Issue: Duplicate documents created by Integra removal
Date Documented: 2 Oct 2018

Symptoms / Error

  • In certain situations Integra can create a large number of duplicate documents.


Cause

  • This is caused by the update process within Integra pushing new documents for each line item. Sometime causing 1000's of new documents.


Fix

  • Get a list of documents with duplicate PointerToSource fields.

  • SELECT
  •     PointerToSource, COUNT(*) as duplicates
  • FROM [DocuPhase].[dbo].[_obj_2]
  • WHERE status != '7'
  • GROUP BY
  •     PointerToSource
  • HAVING
  •     COUNT(*) > 10
  • ORDER BY duplicates DESC

  • Update the status on all but the first copy of that PTS to an unused status.

  • begin tran
  • update [DocuPhase].[dbo].[_obj_2]
  • set status = '7'
  • AND Status = 'E' AND ObjectID > (select MIN(ObjectID) from _obj_2 where PointerToSource = '\\RD54\Repository\Accounts Payable\_obj_2_D1\_obj_2_D1_233.pdf')
  • rollback tran

  • Here is Dian's similar solution. 

*****build a table with duplicate counts and document name.  This table will be joined by document name to the query that will change the status on all records with the same document name except the first one.


SELECT

    PointerToSource as sourcepath, COUNT(*) as duplicates

    into duprecs

    FROM [DocuPhase].[dbo].[_obj_2]

    WHERE status != 'X'

GROUP BY

    PointerToSource

HAVING

    COUNT(*) > 1

ORDER BY duplicates DESC


****browse to show records that will be updated


select pointertosource,objectid, status, duplicates, createdate,IDX_Vendor_ID,IDX_Invoice from [DocuPhase].[dbo].[_obj_2] join dbo.duprecs on Pointertosource=sourcepath

where PointerToSource =sourcepath

AND Status = 'E'

AND ObjectID > (select MIN(ObjectID) from _obj_2 where PointerToSource = dbo.duprecs.sourcepath)

order by duplicates desc, objectid


****Updates all the duplicate records excep the first one with a status of X so they wont show up

begin tran


update ap

set  status = 'X'

from [docuphase].[dbo].[_obj_2] ap

inner join dbo.duprecs  on Pointertosource=sourcepath

where PointerToSource =[dbo].[duprecs].sourcepath

AND Status = 'E'

AND ObjectID > (select MIN(ObjectID) from _obj_2 where PointerToSource = [dbo].[duprecs].sourcepath)

rollback tran


Summary

  • All duplicate documents are now status 7 and can be dealt with as needed