Scenario :
Solution :
1. Suggest to Update latest Version, if version not fixed
2. Due to incorrect ways import of Credit Limit, caused duplicate Credit limit in Back End Table.
To Check Duplicate Info:
Run below Script :
Select C.id, D.CompanyCode, C.CreditLimit from CreditControl C
JOIN Debtors D
ON D.id = C.GLAccountId
Where (C.Glaccountid) in (
Select Glaccountid
from CreditControl
Group by GLAccountId
Having Count(*) >1
)
Another Script to check from related Table
Run below Script :
Select * from CreditControlDetail where CreditControlId in
(select id from CreditControl Where (Glaccountid) in (
Select Glaccountid
from CreditControl
Group by GLAccountId
Having Count(*) >1
))order by CreditControlId asc
To Remove Duplicate Info:
Run Below Script :
-- temp table to hold duplicated records
declare @tableThatContainsDuplicatedRecords table (id uniqueidentifier, code varchar(100), rowNo int)
-- insert the duplicated items
insert into @tableThatContainsDuplicatedRecords
Select C.id, D.CompanyCode, ROW_NUMBER() OVER (PARTITION BY D.CompanyCode ORDER BY C.id ASC) AS rowNo
from CreditControl C
JOIN Debtors D
ON D.id = C.GLAccountId
Where (C.Glaccountid) in (
Select Glaccountid
from CreditControl
Group by GLAccountId
Having Count(*) >1
)
-- display the duplicated records
select * from @tableThatContainsDuplicatedRecords
-- table to hold records to be deleted
declare @tableThatContainsRecordsToBeDeleted table (id uniqueidentifier)
insert into @tableThatContainsRecordsToBeDeleted
select
id
from @tableThatContainsDuplicatedRecords where rowNo <> 1
-- display the record's ids to be deleted
select * from @tableThatContainsRecordsToBeDeleted
-- delete duplicated items
delete from CreditControlDetail
where CreditControlId in (select id from @tableThatContainsRecordsToBeDeleted)
delete from CreditControl
where Id in (select id from @tableThatContainsRecordsToBeDeleted)
-- display table after deletion
Select C.id, D.CompanyCode, C.CreditLimit from CreditControl C
JOIN Debtors D
ON D.id = C.GLAccountId
Where (C.Glaccountid) in (
Select Glaccountid
from CreditControl
Group by GLAccountId
Having Count(*) >1
)
** Learn more about Azure Cloud
** Learn more about QNE Hybrid Cloud Software
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article