Поиск элементов инвентаризации (10.210.00) не заполняется правильно

Эта статья содержит решение проблемы, из-за которой поиск элементов инвентаризации (10.210.00) не заполняется правильно.

Область применения: Microsoft Dynamics SL 2011 с пакетом обновления 2
Исходный номер базы знаний: 2953185

Симптомы

Поиск элементов инвентаризации (10.210.00) не включает все элементы инвентаризации, которые он должен.

Причина

Таблица InvtDescrXref не заполнена.

Решение

Примените следующие скрипты SQL с SQL Server Management Studio:

Сценарий 1

/****** Object: Trigger [dbo].[ADG_TR_InvtDescrXref_Delete] Script Date: 08/30/2012 17:03:58 ******/
SET
   ANSI_NULLS
   ON
   GO
   SET
      QUOTED_IDENTIFIER OFF
      GO ALTER TRIGGER [dbo].[ADG_TR_InvtDescrXref_Delete]
      ON [dbo].[Inventory] FOR
      DELETE
         AS
      set
         nocount
         on
         DECLARE @InvtID varchar(30)
         DECLARE @Descr varchar(60)
         DECLARE @RowsAffected int
         SELECT
            @RowsAffected = @@ROWCOUNT IF @RowsAffected = 1
            BEGIN
               SELECT
                  @InvtID = InvtID,
                  @Descr = Descr
               from
                  DELETED EXECUTE ADG_InvtDescrXref_Delete @InvtID,
                  @Descr
            END
            IF @RowsAffected > 1
            BEGIN
               DECLARE DelCursor SCROLL CURSOR FOR
               SELECT
                  InvtID,
                  Descr
               FROM
                  DELETED OPEN DelCursor FETCH FIRST
               FROM
                  DelCursor INTO @InvtID,
                  @Descr WHILE (@@FETCH_STATUS = 0)
                  BEGIN
                     EXECUTE ADG_InvtDescrXref_Delete @InvtID,
                     @Descr FETCH NEXT
               FROM
                  DelCursor INTO @InvtID,
                  @Descr
                  END
                  CLOSE DelCursor DEALLOCATE DelCursor
            END
            -- Copyright 1998 by Advanced Distribution Group, Ltd. All rights reserved.
            GO

Сценарий 2

/****** Object: StoredProcedure [dbo].[ADG_InvtDescrXref_Populate] Script Date: 08/30/2012 17:22:44 ******/
SET
   ANSI_NULLS
   ON
   GO
   SET
      QUOTED_IDENTIFIER OFF
      GO ALTER PROCEDURE [dbo].[ADG_InvtDescrXref_Populate] AS
      set
         nocount
         on
         DECLARE @InvtID varchar(30)
         DECLARE @Descr varchar(60) TRUNCATE TABLE InvtDescrXref
         DECLARE InvCursor INSENSITIVE CURSOR FOR
         SELECT
            InvtID,
            Descr
         FROM
            Inventory OPEN InvCursor FETCH NEXT
         FROM
            InvCursor INTO @InvtID,
            @Descr WHILE (@@FETCH_STATUS = 0)
            BEGIN
               EXECUTE ADG_InvtDescrXref_Add @InvtID,
               @Descr FETCH NEXT
         FROM
            InvCursor INTO @InvtID,
            @Descr
            END
            CLOSE InvCursor DEALLOCATE InvCursor
            -- Copyright 1998 by Advanced Distribution Group, Ltd. All rights reserved.
            GO

Скрипт 3

/****** Object: Trigger [dbo].[ADG_TR_InvtDescrXref_Add] Script Date: 08/30/2012 17:03:37 ******/
SET
   ANSI_NULLS
   ON
   GO
   SET
      QUOTED_IDENTIFIER OFF
      GO ALTER TRIGGER [dbo].[ADG_TR_InvtDescrXref_Add]
      ON [dbo].[Inventory] FOR INSERT,
      UPDATE
         AS
         DECLARE @InvtID varchar(30)
         DECLARE @Descr varchar(60)
         DECLARE @TranStatusCode varchar(2)
         DECLARE @RowsAffected int
         DECLARE @OMInstalled smallint
         SELECT
            @RowsAffected = @@ROWCOUNT
         SET
            NOCOUNT
            ON IF @RowsAffected = 1
            BEGIN
               IF
               UPDATE
(Descr)
                  BEGIN
                     SELECT
                        @InvtID = InvtID,
                        @Descr = Descr
                     from
                        DELETED EXECUTE ADG_InvtDescrXref_Delete @InvtID,
                        @Descr
                        SELECT
                           @InvtID = InvtID,
                           @Descr = Descr
                        from
                           INSERTED EXECUTE ADG_InvtDescrXref_Add @InvtID,
                           @Descr
                  END
                  IF
                  UPDATE
(TranStatusCode)
                     BEGIN
                        Select
                           @OMInstalled = count(*)
                        from
                           SOSetup (NOLOCK) IF @OMInstalled > 0
                           BEGIN
                              SELECT
                                 @InvtID = InvtID,
                                 @TranStatusCode = TranStatusCode
                              from
                                 INSERTED EXECUTE ADG_ProcessMGr_PlnIn_CrtSh @InvtID,
                                 @TranStatusCode
                           END
                     END
            END
            IF @RowsAffected > 1
            BEGIN
               IF
               UPDATE
(Descr)
                  BEGIN
                     DECLARE DelCursor SCROLL CURSOR FOR
                     SELECT
                        InvtID,
                        Descr
                     FROM
                        DELETED OPEN DelCursor FETCH FIRST
                     FROM
                        DelCursor INTO @InvtID,
                        @Descr WHILE (@@FETCH_STATUS = 0)
                        BEGIN
                           EXECUTE ADG_InvtDescrXref_Delete @InvtID,
                           @Descr FETCH NEXT
                     FROM
                        DelCursor INTO @InvtID,
                        @Descr
                        END
                        CLOSE DelCursor DEALLOCATE DelCursor
                        DECLARE InsCursor SCROLL CURSOR FOR
                        SELECT
                           InvtID,
                           Descr
                        FROM
                           INSERTED OPEN InsCursor FETCH FIRST
                        FROM
                           InsCursor INTO @InvtID,
                           @Descr WHILE (@@FETCH_STATUS = 0)
                           BEGIN
                              EXECUTE ADG_InvtDescrXref_Add @InvtID,
                              @Descr FETCH NEXT
                        FROM
                           InsCursor INTO @InvtID,
                           @Descr
                           END
                           CLOSE InsCursor DEALLOCATE InsCursor
                  END
                  IF
                  UPDATE
(TranStatusCode)
                     BEGIN
                        Select
                           @OMInstalled = count(*)
                        from
                           SOSetup (NOLOCK) IF @OMInstalled > 0
                           BEGIN
                              DECLARE InsCursor SCROLL CURSOR FOR
                              SELECT
                                 InvtID,
                                 TranStatusCode
                              FROM
                                 INSERTED OPEN InsCursor FETCH FIRST
                              FROM
                                 InsCursor INTO @InvtID,
                                 @TranStatusCode WHILE (@@FETCH_STATUS = 0)
                                 BEGIN
                                    EXECUTE ADG_ProcessMgr_PlnIn_CrtSh @InvtID,
                                    @TranStatusCode FETCH NEXT
                              FROM
                                 InsCursor INTO @InvtID,
                                 @TranStatusCode
                                 END
                                 CLOSE InsCursor DEALLOCATE InsCursor
                           END
                     END
            END
            -- Copyright 1998 by Advanced Distribution Group, Ltd. All rights reserved.
            GO