Removing Extender Data when deleting Transactions or Lines Revisited

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

Last 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:

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]
GOSET
QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOCREATE
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]
GOSET
QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOCREATE
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]
GOSET
QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOCREATE
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

Updated Item Transfer Extender Data Example.zip

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

One thought on “Removing Extender Data when deleting Transactions or Lines Revisited

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.