Developing for Dynamics GP

by Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

Removing Extender Data when deleting Transactions or Lines Revisited

Removing Extender Data when deleting Transactions or Lines Revisited

  • Comments 1

David Meego - Click for blog homepageLast week, I tackled the subject of orphaned records in the Extender tables when the parent records were deleted and the Extender data was left behind.  If you have not read this post the link is below:

Removing Extender Data when deleting Transactions or Lines

If you read through the comments, you will see that Konrad Berger (an Australian Partner Consultant) mentions the idea of checking the Open (or History) table to see if the transaction has been moved to the next stage (ie. posted) rather than deleted. 

The concept works because most of the posting code in Microsoft Dynamics GP adds the records to the Open (or History) tables before it removed the old record from the Work table.  This gives a SQL table trigger the ability to tell the difference between a record being deleted by the user and a record being posted.

So this post will take the triggers attached to the previous post and modify them to use this concept. The updated triggers are shown below and also are attached to this post.  These updated triggers can be used in a live system and do away with the need for the Visual Basic for Applications (VBA) code in the previous post.

Extender Window Example - using IVTRF_1 & IVTRF_2

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IV10000_DELETE_IVTRF_1]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[IV10000_DELETE_IVTRF_1]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS OFF
GO

CREATE TRIGGER [IV10000_DELETE_IVTRF_1]
ON [dbo].[IV10000]
FOR DELETE
NOT FOR REPLICATION
AS

DECLARE @ExtenderID char(15)

select @ExtenderID = 'IVTRF_1'

SET NOCOUNT ON

BEGIN

DELETE A FROM EXT00101 A -- Strings
JOIN EXT00100 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30200 D 
      where D.IVDOCTYP = 3 and D.DOCNUMBR = C.IVDOCNBR)

DELETE A FROM EXT00102 A -- Dates
JOIN EXT00100 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30200 D 
      where D.IVDOCTYP = 3 and D.DOCNUMBR = C.IVDOCNBR)

DELETE A FROM EXT00103 A -- Numbers
JOIN EXT00100 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30200 D
      where D.IVDOCTYP = 3 and D.DOCNUMBR = C.IVDOCNBR)

DELETE A FROM EXT00104 A -- Times
JOIN EXT00100 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30200 D
      where D.IVDOCTYP = 3 and D.DOCNUMBR = C.IVDOCNBR)

DELETE B FROM EXT00100 B -- Mapping
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30200 D
      where D.IVDOCTYP = 3 and D.DOCNUMBR = C.IVDOCNBR)

END

GO
SET
QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IV10001_DELETE_IVTRF_2]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[IV10001_DELETE_IVTRF_2]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS OFF
GO

CREATE TRIGGER [IV10001_DELETE_IVTRF_2]
ON [dbo].[IV10001]
FOR DELETE
NOT FOR REPLICATION
AS

DECLARE @ExtenderID char(15)

select @ExtenderID = 'IVTRF_2'

SET NOCOUNT ON

BEGIN

DELETE A FROM EXT00101 A -- Strings
JOIN EXT00100 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
      where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)

DELETE A FROM EXT00102 A -- Dates
JOIN EXT00100 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
      where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)

DELETE A FROM EXT00103 A -- Numbers
JOIN EXT00100 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
      where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)

DELETE A FROM EXT00104 A -- Times
JOIN EXT00100 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
     
where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)

DELETE B FROM EXT00100 B -- Mapping
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
     
where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)

END

GO
SET
QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS ON
GO

 

 

Extender Detail Window Example - using IV TRANSFER FORM

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IV10001_DELETE_IV_TRANS_FORM]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[IV10001_DELETE_IV_TRANS_FORM]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS OFF
GO

CREATE TRIGGER [IV10001_DELETE_IV_TRANS_FORM]
ON [dbo].[IV10001]
FOR DELETE
NOT FOR REPLICATION
AS

DECLARE @ExtenderID char(15)

select @ExtenderID = 'IV TRANS FORM'

SET NOCOUNT ON

BEGIN

DELETE A FROM EXT00181 A -- Strings
JOIN EXT00185 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
     
where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)

DELETE A FROM EXT00182 A -- Dates
JOIN EXT00185 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
     
where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)

DELETE A FROM EXT00183 A -- Numbers
JOIN EXT00185 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
     
where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)

DELETE A FROM EXT00184 A -- Times
JOIN EXT00185 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
     
where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)

DELETE A FROM EXT00180 A -- Lines
JOIN EXT00185 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
     
where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)

DELETE B FROM EXT00185 B -- Line Mapping
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
     
where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)

END

GO
SET
QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS ON
GO

 

 

You will need to test the triggers for your window to be sure that the concept of checking the Open (or History) table does work for the particular posting code. You should also have the Table Link or SQL Trigger to cleanup when the records are removed from History.

Hope you find this useful. Thanks Konrad for the idea.

David

Attachment: Updated Item Transfer Extender Data Example.zip
Page 1 of 1 (1 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 6 and 4 and type the answer here:
  • Post