Dynamics GP - Reconcile to GL - Inventory - Violation of Primary Key contstraint... Cannot insert duplicate key in object

Related Issues

• The excel sheet will still be generated after clearing the error
• The reconciled excel sheet shows no matched transactions
• All transactions appear as unmatched

--The problem occurs when this script is trying to run, but returns duplicates

BEGIN DECLARE @stored_proc_name char(28) DECLARE @retstat int set nocount on SELECT @stored_proc_name = 'ATLT.dbo.glGetIVTransactions'
EXEC @retstat = @stored_proc_name '2013.11.01', '2013.11.30', '##1765136' SELECT @retstat set nocount on END


--The constraint in question is this one
ALTER TABLE ##1765136 ADD CONSTRAINT PK##1765136 PRIMARY KEY NONCLUSTERED (TRXSORCE, DOCNUMBR, DOCTYPE, JRNENTRY, ACTINDX, ITEMNMBR)


--Send the output to a clean temp table

select * into temp_GLIVREC from dbo.##1765136
BEGIN DECLARE @stored_proc_name char(28) DECLARE @retstat int set nocount on SELECT @stored_proc_name = 'ATLT.dbo.glGetIVTransactions'
EXEC @retstat = @stored_proc_name '2013.11.01', '2013.11.30', 'temp_GLIVREC' SELECT @retstat set nocount on END


--Review the output by running this query to identify any duplicates

SELECT     TRXSORCE, DOCNUMBR, DOCTYPE, JRNENTRY, ACTINDX, ITEMNMBR
FROM         temp_GLIVREC
GROUP BY TRXSORCE, DOCNUMBR, DOCTYPE, JRNENTRY, ACTINDX, ITEMNMBR
HAVING      (COUNT(TRXSORCE) > 1)

--I found that i was getting duplicates due to transctions in the SEE30303 table having blank TRXSORCE values. To resolve the issue, i populated the trxsorce with the dexrowid because i was unsure if the records should be deleted or not. I kept a backup of the SEE30303 table in case we needed to reset the data

select * into see30303_bak from SEE30303
update see30303 set TRXSORCE = DEX_ROW_ID where TRXSORCE = ''