Find and fix allocation issues (both AR and AP)

The sql below does the following:

Checks and fixes for issues on allocations in AR and AP (Incorrect balances, and missing transactions)

Note that it will undo the allocation in Sage (removes the broken allocation string from the transactions, meaning you/the client will have to allocate again)

To check which lines will be unallocated:

---------------
---------------
-----AP--------
---------------
---------------


--add all the Vendors
 TRUNCATE TABLE _etblAllocsDCLinkRangeTemp
  insert into _etblAllocsDCLinkRangeTemp (DCLink)
   (Select DClink from Vendor where bforcurAcc =0)			-

--Fill the Temp table sage uses
exec _espAllocsAllToTableARAP 'AP'							




Select 
 NoMirror.iFromRecID  as AutoIdx,
 NoMirror.iToRecID as [Alocated To AutoIdx]

  from
   (Select A1.* from _etblAllocsTemp A1 left join _etblAllocsTemp A2 on A2.iToRecID = A1.iFromRecID and A1.iToRecID = A2.iFromRecID where A2.idAllocsTemp Is Null) NoMirror
    left outer join PostAP PAP on NoMirror.iFromRecID = PAP.AutoIdx left outer join Vendor V on NoMirror.iAccountID = V.DCLink order by NoMirror.iFromRecID

 -------------------------------------
-------------------------------------
-------------------------------------
Select 
A1.iFromRecID  as AutoIdx,
 A1.iToRecID as [Alocated To AutoIdx]
  from _etblAllocsTemp A1 left outer join _etblAllocsTemp A2 on A2.iToRecID=A1.iFromRecID and A1.iToRecID=A2.iFromRecID 
  left outer join PostAP PAP on A1.iFromRecID = PAP.AutoIdx 
  left outer join Vendor V on A1.iAccountID = V.DCLink
   where ((select sum(A3.fAmount) from _etblAllocsTemp A3 
			where A3.iFromRecID=A1.iFromRecID and A3.iToRecID=A1.iToRecID group by A3.iFromRecID, A3.iToRecID) <> 
			(select sum(A4.fAmount) from _etblAllocsTemp A4 where A4.iFromRecID=A2.iFromRecID and A4.iToRecID=A2.iToRecID group by A4.iFromRecID, A4.iToRecID))
		 order by A1.iFromRecID



---------------
---------------
-----AR--------
---------------
---------------

--add all the clients
 TRUNCATE TABLE _etblAllocsDCLinkRangeTemp
  insert into _etblAllocsDCLinkRangeTemp (DCLink)
   (Select DClink from Client where bforcurAcc =0)

--Fill the Temp table sage uses
exec _espAllocsAllToTableARAP 'AR'


EXEC _AS_SP_AllocRecalculation @ARAP = 'AR',@CorrectErrors =  1


--Run it again as the fix above changed allocations:
exec _espAllocsAllToTableARAP 'AR'

-------------------------------------
-------------------------------------
-------------------------------------
--No mirror 1
Select
 NoMirror.iFromRecID  as AutoIdx
,NoMirror.iToRecID as [Alocated To AutoIdx]

 from 
 (Select A1.* from  _etblAllocsTemp A1 
 left join _etblAllocsTemp A2 on A2.iToRecID = A1.iFromRecID and A1.iToRecID = A2.iFromRecID 
 where A2.idAllocsTemp Is Null) NoMirror 
 left outer join PostAR PAR on NoMirror.iFromRecID = PAR.AutoIdx 
 left outer join Client C on NoMirror.iAccountID = C.DCLink 
 order by NoMirror.iFromRecID

-------------------------------------
-------------------------------------
 --Alloc diff
 Select	
	  A1.iFromRecID  as AutoIdx
	, A1.iToRecID as [Alocated To AutoIdx]

	 from _etblAllocsTemp A1 
	 left outer join _etblAllocsTemp A2 on A2.iToRecID=A1.iFromRecID and A1.iToRecID=A2.iFromRecID 
	 left outer join PostAR PAR on A1.iFromRecID = PAR.AutoIdx
	  left outer join Client C on A1.iAccountID = C.DCLink 
	  where ((select sum(A3.fAmount)
				 from _etblAllocsTemp A3
					where A3.iFromRecID=A1.iFromRecID 
					and A3.iToRecID=A1.iToRecID 
				group by A3.iFromRecID, A3.iToRecID) 
			<> (select sum(A4.fAmount) 
					from _etblAllocsTemp A4 
					where A4.iFromRecID=A2.iFromRecID and A4.iToRecID=A2.iToRecID 
					group by A4.iFromRecID, A4.iToRecID)) 
	order by A1.iFromRecID

Checks and fixes incorrect calculated outstanding balances (home currency only)

To see what lines will be updated (when running step 2 and 3, it will update all the lines in the sql below)

--AR
EXEC _AS_SP_AllocRecalculation  @ARAP = 'AR', @CorrectErrors = 0			--@CorrectErrors is set to 1 in step 2 and 3


--AP
EXEC _AS_SP_AllocRecalculation  @ARAP = 'AP', @CorrectErrors = 0			--@CorrectErrors is set to 1 in step 2 and 3

1) Run the stored procs on the database

Allocation issue solver

-- =============================================
-- Author:  Jan van der Hilst
-- Company:  Asamco B.V
-- Create date:  09/05/2017
-- Update date : 16/08/2018
-- =============================================
--EXEC _AS_SP_AllocRecalculation  @ARAP = 'AR', @CorrectErrors = 0

IF EXISTS ( SELECT  * FROM  sys.objects WHERE   object_id = OBJECT_ID(N'_AS_SP_AllocRecalculation') AND type IN ( N'P', N'PC' ) ) 
DROP PROCEDURE _AS_SP_AllocRecalculation



CREATE PROCEDURE [dbo]._AS_SP_AllocRecalculation
 @ARAP varchar(2),
 @CorrectErrors bit

AS
BEGIN

If NOT Exists(Select [Name] from sysobjects where xType = 'U' and [Name] = '#AllocData') 
 BEGIN
  CREATE Table #AllocData (
    DCLink int
   ,iToRecID Int
   ,SumAllocs float
  )
 END

If NOT Exists(Select [Name] from sysobjects where xType = 'U' and [Name] = '#ActionDone') 
BEGIN
 CREATE Table #ActionDone (
   AutoIDx int
  ,Debit float
  ,Credit float
  ,DMC float
  ,Outstanding float
  ,SumAllocs float
  ,CorrectCalculatedOutstanding float
  ,Action varchar(30)
 )
END

 TRUNCATE TABLE #AllocData
 TRUNCATE TABLE #ActionDone
 TRUNCATE TABLE _etblAllocsDCLinkRangeTemp


IF @ARAP = 'AR' BEGIN
 

 --Get The max Date
 DECLARE @MaxTxDateAR Date
 SELECT @MaxTxDateAR =  MAX(TxDate) from postar 

 --SELECT * from _efnAgedPostAR(@MaxTxDateAR,NULL) 

 --Select 
 -- PostAROutstanding = AR.outstanding,
 -- CalculatedOutstanding = AgedAR.CalculatedOutStanding
 
 --from _efnAgedPostAR(@MaxTxDateAR,NULL) AgedAR
 --LEFT JOIN PostAR AR on AR.AutoIdx = AgedAR.AutoIdx
 -- WHERE AR.outstanding <> AgedAR.CalculatedOutStanding
  

 insert into _etblAllocsDCLinkRangeTemp (DCLink)
   (Select DClink from Client where bforcurAcc =0)

 
 INSERT INTO #AllocData (DCLink,iToRecID,SumAllocs)
 (Select 
  Allocations.iAccountID
 ,Allocations.iToRecID
 ,SUM(Allocations.fAmount) SumAllocs 
  from  [dbo].[_efnAllocsARAP] ('AR',@MaxTxDateAR,1,0,NULL,NULL) Allocations
  GROUP BY Allocations.iAccountID,Allocations.iToRecID)


   --Information table:
  INSERT INTO #ActionDone 
  (
   AutoIDx 
  ,Debit 
  ,Credit 
  ,DMC
  ,Outstanding
  ,SumAllocs
  ,CorrectCalculatedOutstanding
  ,Action
 )
 SELECT
   ARJ.AutoIDx
  ,ARJ.Debit
  ,ARJ.Credit
  ,ARJ.DMC
  ,ARJ.Outstanding
  ,ARJ.SumAllocs
  ,CASE WHEN ARJ.DMC > 0 
   THEN
    (ROUND(ARJ.DMC,2) - ROUND(COALESCE(ARJ.SumAllocs,0),2)) 
   ELSE    
    (ROUND(ARJ.DMC,2) + ROUND(COALESCE(ARJ.SumAllocs,0),2)) 
   END
  as CorrectCalculatedOutstanding
  ,CASE WHEN SumAllocs > DMC THEN
   'Please fix manually'
   ELSE
   'Updated by this query'
  End as ActionDone
 FROM
  (SELECT
    alloc.DCLink
    ,AR.AutoIdx
    ,AR.Debit
    ,AR.Credit
    ,AR.Debit-AR.Credit AS DMC
    ,AR.Outstanding
    ,AD.SumAllocs 
    --,Case when AR.Debit > 0 THEN AD.SumAllocs ELSE -AD.SumAllocs END AS SumAllocs2  
   from _etblAllocsDCLinkRangeTemp alloc
   LEFT JOIN PostAR AR on AR.Accountlink =  alloc.DcLink 
   LEFT JOIN #AllocData AD on AD.iToRecID = AR.AutoIdx
    WHERE AR.cAllocs IS NOT NULL) ARJ
   where 
    ROUND(
       (ROUND(ARJ.Outstanding,2)
        -
       (CASE WHEN ARJ.DMC > 0 
        THEN
         (ROUND(ARJ.DMC,2) - ROUND(COALESCE(ARJ.SumAllocs,0),2)) 
        ELSE    
         (ROUND(ARJ.DMC,2) + ROUND(COALESCE(ARJ.SumAllocs,0),2)) 
        END
       )),2)
     <> 0

 
 IF @CorrectErrors =1 
  BEGIN
   UPDATE AR
    set Outstanding = #ActionDone.CorrectCalculatedOutstanding
   FROM postAR AR
   INNER JOIN #ActionDone on AR.AutoIdx = #ActionDone.AutoIDx
   --WHERE Action = 'Updated by this query'

  END 

 Select   
   #ActionDone.AutoIDx 
  ,Client.Account
  ,#ActionDone.Debit 
  ,#ActionDone.Credit 
  ,#ActionDone.DMC
  ,#ActionDone.Outstanding
  ,#ActionDone.SumAllocs
  ,#ActionDone.CorrectCalculatedOutstanding
  ,#ActionDone.Action
  FROM #ActionDone
  LEFT JOIN postAR on #ActionDone.AutoIDx = postAR.AutoIdx
  LEFT JOIN Client on Client.DCLink = postAR.AccountLink

END --IF @ARAP = 'AR' BEGIN
IF @ARAP = 'AP' BEGIN
  
 --Get The max Date
 DECLARE @MaxTxDateAP Date
 SELECT @MaxTxDateAP =  MAX(TxDate) from postap 


 insert into _etblAllocsDCLinkRangeTemp (DCLink)
   (Select DClink from Vendor  where bforcurAcc =0)

 
 INSERT INTO #AllocData (DCLink,iToRecID,SumAllocs)
 (Select 
  Allocations.iAccountID
 ,Allocations.iToRecID
 ,SUM(Allocations.fAmount) SumAllocs 
  from  [dbo].[_efnAllocsARAP] ('AP',@MaxTxDateAP,1,0,NULL,NULL) Allocations
  GROUP BY Allocations.iAccountID,Allocations.iToRecID)

  --Information table:
  INSERT INTO #ActionDone 
  (
   AutoIDx 
  ,Debit 
  ,Credit 
  ,DMC
  ,Outstanding
  ,SumAllocs
  ,CorrectCalculatedOutstanding
  ,Action
 )
 SELECT
   ARJ.AutoIDx
  ,ARJ.Debit
  ,ARJ.Credit
  ,ARJ.DMC
  ,ARJ.Outstanding
  ,ARJ.SumAllocs
  ,CASE WHEN ARJ.DMC > 0 
   THEN
    -(ROUND(ARJ.DMC,2) - ROUND(COALESCE(ARJ.SumAllocs,0),2)) 
   ELSE
    (-ROUND(ARJ.DMC,2) - ROUND(COALESCE(ARJ.SumAllocs,0),2)) 
   END
  as CorrectCalculatedOutstanding
  ,CASE WHEN SumAllocs > DMC THEN
   'Please fix manually'
   ELSE
   'Updated by this query'
  End as ActionDone
 FROM
  (SELECT
    alloc.DCLink
    ,AP.AutoIdx
    ,AP.Debit
    ,AP.Credit
    ,AP.Debit-AP.Credit AS DMC
    ,AP.Outstanding
    ,AD.SumAllocs 
    --,Case when AR.Debit > 0 THEN AD.SumAllocs ELSE -AD.SumAllocs END AS SumAllocs2  
   from _etblAllocsDCLinkRangeTemp alloc
   LEFT JOIN PostAP AP on AP.Accountlink =  alloc.DcLink 
   LEFT JOIN #AllocData AD on AD.iToRecID = AP.AutoIdx
    WHERE AP.cAllocs IS NOT NULL) ARJ
   where 
      ROUND(
       (ROUND(ARJ.Outstanding,2)
        -
       (CASE WHEN ARJ.DMC > 0 
        THEN
         -(ROUND(ARJ.DMC,2) - ROUND(COALESCE(ARJ.SumAllocs,0),2)) 
        ELSE
         (-ROUND(ARJ.DMC,2) - ROUND(COALESCE(ARJ.SumAllocs,0),2)) 
        END 
       )),2)
     
     <> 0

 IF @CorrectErrors =1 
  BEGIN
   UPDATE AP
    set Outstanding = #ActionDone.CorrectCalculatedOutstanding
   FROM postAP AP
   INNER JOIN #ActionDone on AP.AutoIdx = #ActionDone.AutoIDx
   --WHERE Action = 'Updated by this query'
  END

 Select   
   #ActionDone.AutoIDx 
  ,Vendor.Account
  ,#ActionDone.Debit 
  ,#ActionDone.Credit 
  ,#ActionDone.DMC
  ,#ActionDone.Outstanding
  ,#ActionDone.SumAllocs
  ,#ActionDone.CorrectCalculatedOutstanding
  ,#ActionDone.Action
  FROM #ActionDone
  LEFT JOIN postAP on #ActionDone.AutoIDx = postAP.AutoIdx
  LEFT JOIN Vendor on Vendor.DCLink = postAP.AccountLink
END --IF @ARAP = 'AP' BEGIN

--DROPPING TABLES:
DROP TABLE #ActionDone
DROP TABLE #AllocData
END

AP allocation fixer

IF EXISTS ( SELECT  * FROM  sys.objects WHERE   object_id = OBJECT_ID(N'_AS_SP_RemoveBrokenAllocationAP') AND type IN ( N'P', N'PC' ) ) 
DROP PROCEDURE _AS_SP_RemoveBrokenAllocationAP

IF EXISTS ( SELECT  * FROM  sys.objects WHERE   object_id = OBJECT_ID(N'_AS_SP_FixAllocationIssuesAP') AND type IN ( N'P', N'PC' ) ) 
DROP PROCEDURE _AS_SP_FixAllocationIssuesAP


IF EXISTS ( SELECT  * FROM  sys.objects WHERE   object_id = OBJECT_ID(N'fnSplitString') AND type IN ( N'TF' ) ) 
DROP Function  fnSplitString

GO

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END 
    RETURN 

END


GO
-- =============================================
-- Author:  Jan van der Hilst
-- Company:  Asamco B.V - Camelsa CCG
-- Create date: 09/05/2017
-- =============================================
CREATE PROCEDURE [dbo]._AS_SP_RemoveBrokenAllocationAP
 @FromAutoidx int,
 @ToAutoIdx  int

AS
BEGIN

	--Remove the From side
	DECLARE @AllocFrom varchar(max)
	DECLARE @AllocTo varchar(max)

	SELECT @AllocFrom = (SELECT COALESCE(cAllocs,'') from postAp where AutoIdx = @FromAutoidx)
	SELECT @AllocTo = (SELECT COALESCE(cAllocs,'') from postAp where AutoIdx = @ToAutoIdx)

	IF @AllocFrom <> ''
		BEGIN
			DECLARE  @NewAllocString Varchar(Max)			

				--START FV CURSOR:
				DECLARE FVCursor CURSOR FOR
					SELECT SPLIT.splitdata from [fnSplitString](@AllocFrom,'|') Split
						WHERE SPLIT.splitdata NOT LIKE '%'+CONVERT(varchar(30),@ToAutoIdx)+'%'
				--Cursor declares:
				DECLARE @Alloc Varchar(max)

				--Open btblLinesCursor
				OPEN FVCursor
				
				-- Here we loop through all lines:
				FETCH NEXT FROM FVCursor INTO @Alloc
				WHILE @@FETCH_STATUS = 0
					BEGIN	

						IF COALESCE(@NewAllocString,'') = ''
							BEGIN
								Select @NewAllocString = @Alloc
							END 
						ELSE 
							BEGIN
								Select @NewAllocString = COALESCE(@NewAllocString,'') + '|' +@Alloc
							END
	

					--CURSOR NEXT: FVCursor
					FETCH NEXT FROM FVCursor INTO  @Alloc
				END --WHILE @@FETCH_STATUS... FVCursor
				--Cleaning up cursor:
				CLOSE FVCursor
				DEALLOCATE FVCursor
			
			UPDATE POSTAP SET cAllocs = @NewAllocString where AutoIdx = @FromAutoidx
		END
	

	IF @AllocTo <> ''
		BEGIN
				DECLARE  @NewAllocStringTo Varchar(Max)			

				--START FV CURSOR:
				DECLARE FVCursorTo CURSOR FOR
					SELECT SPLIT.splitdata from [fnSplitString](@AllocTo,'|') Split
						WHERE SPLIT.splitdata NOT LIKE '%'+CONVERT(varchar(30),@FromAutoidx)+'%'
				--Cursor declares:
				DECLARE @AllocTValueTo Varchar(max)

				--Open btblLinesCursor
				OPEN FVCursorTo
				
				-- Here we loop through all lines:
				FETCH NEXT FROM FVCursorTo INTO @AllocTValueTo
				WHILE @@FETCH_STATUS = 0
					BEGIN	

						IF COALESCE(@NewAllocStringTo,'') = ''
							BEGIN
								Select @NewAllocStringTo = @AllocTValueTo
							END 
						ELSE 
							BEGIN
								Select @NewAllocStringTo = COALESCE(@NewAllocStringTo,'') + '|' +@AllocTValueTo
							END
	

					--CURSOR NEXT: FVCursorTo
					FETCH NEXT FROM FVCursorTo INTO  @AllocTValueTo
				END --WHILE @@FETCH_STATUS... FVCursorTo
				--Cleaning up cursor:
				CLOSE FVCursorTo
				DEALLOCATE FVCursorTo
			
			UPDATE POSTAP SET cAllocs = @NewAllocStringTo where AutoIdx = @ToAutoidx
		END
END


GO

-- =============================================
-- Author:  Jan van der Hilst
-- Company:  Asamco B.V - Camelsa CCG
-- Create date: 09/05/2017
-- Update date : 17/08/2018
-- =============================================
CREATE PROCEDURE [dbo]._AS_SP_FixAllocationIssuesAP 
AS
BEGIN


If NOT Exists(Select [Name] from sysobjects where xType = 'U' and [Name] = '#AutoIdxIssues') 
BEGIN
 CREATE Table #AutoIdxIssues (
   FromAutoIDx int,
   ToAutoIDx int
 )
END

TRUNCATE table #AutoIdxIssues

--add all the Vendors
 TRUNCATE TABLE _etblAllocsDCLinkRangeTemp
  insert into _etblAllocsDCLinkRangeTemp (DCLink)
   (Select DClink from Vendor where bforcurAcc =0)

--Fill the Temp table sage uses
exec _espAllocsAllToTableARAP 'AP'


EXEC _AS_SP_AllocRecalculation @ARAP = 'AP',@CorrectErrors =  1


--Run it again as the fix above changed allocations:
exec _espAllocsAllToTableARAP 'AP'


-------------------------------------
-------------------------------------
-------------------------------------
INSERT INTO #AutoIdxIssues (fromAutoidx,toAutoIdx)
--No mirror 1
Select 
 NoMirror.iFromRecID  as AutoIdx,
 NoMirror.iToRecID as [Alocated To AutoIdx]

  from
   (Select A1.* from _etblAllocsTemp A1 left join _etblAllocsTemp A2 on A2.iToRecID = A1.iFromRecID and A1.iToRecID = A2.iFromRecID where A2.idAllocsTemp Is Null) NoMirror
    left outer join PostAP PAP on NoMirror.iFromRecID = PAP.AutoIdx left outer join Vendor V on NoMirror.iAccountID = V.DCLink order by NoMirror.iFromRecID

 -------------------------------------
-------------------------------------
-------------------------------------

INSERT INTO #AutoIdxIssues (fromAutoidx,toAutoIdx)
 --Alloc diff
Select 
A1.iFromRecID  as AutoIdx,
 A1.iToRecID as [Alocated To AutoIdx]
  from _etblAllocsTemp A1 left outer join _etblAllocsTemp A2 on A2.iToRecID=A1.iFromRecID and A1.iToRecID=A2.iFromRecID 
  left outer join PostAP PAP on A1.iFromRecID = PAP.AutoIdx 
  left outer join Vendor V on A1.iAccountID = V.DCLink
   where ((select sum(A3.fAmount) from _etblAllocsTemp A3 
			where A3.iFromRecID=A1.iFromRecID and A3.iToRecID=A1.iToRecID group by A3.iFromRecID, A3.iToRecID) <> 
			(select sum(A4.fAmount) from _etblAllocsTemp A4 where A4.iFromRecID=A2.iFromRecID and A4.iToRecID=A2.iToRecID group by A4.iFromRecID, A4.iToRecID))
		 order by A1.iFromRecID

-------------------------------------
-------------------------------------
-------------------------------------


			--START FV CURSOR:
			DECLARE AllocCursor CURSOR FOR
				SELECT fromAutoidx,toAutoIdx from #AutoIdxIssues
			--Cursor declares:
			DECLARE @fromAutoidxS Int
			DECLARE @toAutoIdxS Int

			--Open btblLinesCursor
			OPEN AllocCursor
				
			-- Here we loop through all lines:
			FETCH NEXT FROM AllocCursor INTO @fromAutoidxS,@toAutoIdxS
			WHILE @@FETCH_STATUS = 0
				BEGIN	
					--Remove the alloc
						EXEC _AS_SP_RemoveBrokenAllocationAP  @FromAutoidx = @fromAutoidxS, @ToAutoIdx  = @toAutoIdxS				

				--CURSOR NEXT: AllocCursor
				FETCH NEXT FROM AllocCursor INTO  @fromAutoidxS,@toAutoIdxS
			END --WHILE @@FETCH_STATUS... AllocCursor
			--Cleaning up cursor:
			CLOSE AllocCursor
			DEALLOCATE AllocCursor


 
--Set the outstanding where cAllocs is NULL
UPDATE ap 

	Set Outstanding = Case WHEN DEBIT > 1 Then Debit *-1	
						ELSE Credit END
 from postap ap
WHERE cAllocs is null

--Remove the zero's
UPDATE 
	postap set Debit = COALESCE(debit,0) ,Credit = COALESCE(Credit,0)


Drop table #AutoIdxIssues

END


GO

--SELECT * from (
--	Select ar.Accountlink, ROUND(sum(ar.Debit-ar.Credit),2) DMC from postap ar
--		GRoup by ar.Accountlink) DMC
--	LEFT JOIN (Select Accountlink, ROUND(sum(CASE WHEN Debit > 0 THEN ABS(Outstanding) WHEN Credit >0 Then Outstanding *-1 END),2) as OS from postap GRoup by Accountlink) OS on OS.AccountLink = DMC.Accountlink
--WHERE DMC.DMC <> OS.OS


--SELECT
--	'AP' as TRType,
--	 (Select Sum(debit-Credit) from postap ) 
--	- (Select ROUND(sum(CASE WHEN Debit > 0 THEN ABS(Outstanding) WHEN Credit >0 Then Outstanding *-1 END),2) from postap )as  Balance
--	UNION ALL

--SELECT 'AR' as TRType, (Select Sum(debit-Credit) from postar ) - (Select Sum(outstanding) from postar ) as  Balance

AR Allocation fixer

IF EXISTS ( SELECT  * FROM  sys.objects WHERE   object_id = OBJECT_ID(N'_AS_SP_RemoveBrokenAllocationAR') AND type IN ( N'P', N'PC' ) ) 
DROP PROCEDURE _AS_SP_RemoveBrokenAllocationAR

GO

IF EXISTS ( SELECT  * FROM  sys.objects WHERE   object_id = OBJECT_ID(N'_AS_SP_FixAllocationIssuesAR') AND type IN ( N'P', N'PC' ) ) 
DROP PROCEDURE _AS_SP_FixAllocationIssuesAR

GO

IF EXISTS ( SELECT  * FROM  sys.objects WHERE   object_id = OBJECT_ID(N'fnSplitString') AND type IN ( N'TF' ) ) 
DROP Function  fnSplitString

GO

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END 
    RETURN 

END


GO
-- =============================================
-- Author:  Jan van der Hilst
-- Company:  Asamco B.V - Camelsa CCG
-- Create date: 09/05/2017
-- =============================================
CREATE PROCEDURE [dbo]._AS_SP_RemoveBrokenAllocationAR
 @FromAutoidx int,
 @ToAutoIdx  int

AS
BEGIN

	--Remove the From side
	DECLARE @AllocFrom varchar(max)
	DECLARE @AllocTo varchar(max)

	SELECT @AllocFrom = (SELECT COALESCE(cAllocs,'') from postAR where AutoIdx = @FromAutoidx)
	SELECT @AllocTo = (SELECT COALESCE(cAllocs,'') from postAR where AutoIdx = @ToAutoIdx)

	IF @AllocFrom <> ''
		BEGIN
			DECLARE  @NewAllocString Varchar(Max)			

				--START FV CURSOR:
				DECLARE FVCursor CURSOR FOR
					SELECT SPLIT.splitdata from [fnSplitString](@AllocFrom,'|') Split
						WHERE SPLIT.splitdata NOT LIKE '%'+CONVERT(varchar(30),@ToAutoIdx)+'%'
				--Cursor declares:
				DECLARE @Alloc Varchar(max)

				--Open btblLinesCursor
				OPEN FVCursor
				
				-- Here we loop through all lines:
				FETCH NEXT FROM FVCursor INTO @Alloc
				WHILE @@FETCH_STATUS = 0
					BEGIN	

						IF COALESCE(@NewAllocString,'') = ''
							BEGIN
								Select @NewAllocString = @Alloc
							END 
						ELSE 
							BEGIN
								Select @NewAllocString = COALESCE(@NewAllocString,'') + '|' +@Alloc
							END
	

					--CURSOR NEXT: FVCursor
					FETCH NEXT FROM FVCursor INTO  @Alloc
				END --WHILE @@FETCH_STATUS... FVCursor
				--Cleaning up cursor:
				CLOSE FVCursor
				DEALLOCATE FVCursor
			
			UPDATE POSTAR SET cAllocs = @NewAllocString where AutoIdx = @FromAutoidx
		END
	

	IF @AllocTo <> ''
		BEGIN
				DECLARE  @NewAllocStringTo Varchar(Max)			

				--START FV CURSOR:
				DECLARE FVCursorTo CURSOR FOR
					SELECT SPLIT.splitdata from [fnSplitString](@AllocTo,'|') Split
						WHERE SPLIT.splitdata NOT LIKE '%'+CONVERT(varchar(30),@FromAutoidx)+'%'
				--Cursor declares:
				DECLARE @AllocTValueTo Varchar(max)

				--Open btblLinesCursor
				OPEN FVCursorTo
				
				-- Here we loop through all lines:
				FETCH NEXT FROM FVCursorTo INTO @AllocTValueTo
				WHILE @@FETCH_STATUS = 0
					BEGIN	

						IF COALESCE(@NewAllocStringTo,'') = ''
							BEGIN
								Select @NewAllocStringTo = @AllocTValueTo
							END 
						ELSE 
							BEGIN
								Select @NewAllocStringTo = COALESCE(@NewAllocStringTo,'') + '|' +@AllocTValueTo
							END
	

					--CURSOR NEXT: FVCursorTo
					FETCH NEXT FROM FVCursorTo INTO  @AllocTValueTo
				END --WHILE @@FETCH_STATUS... FVCursorTo
				--Cleaning up cursor:
				CLOSE FVCursorTo
				DEALLOCATE FVCursorTo
			
			UPDATE POSTAR SET cAllocs = @NewAllocStringTo where AutoIdx = @ToAutoidx
		END
END


GO

-- =============================================
-- Author:  Jan van der Hilst
-- Company:  Asamco B.V - Camelsa CCG
-- Create date: 09/05/2017
-- Update date : 17/08/2018
-- =============================================
CREATE PROCEDURE [dbo]._AS_SP_FixAllocationIssuesAR
AS
BEGIN


If NOT Exists(Select [Name] from sysobjects where xType = 'U' and [Name] = '#AutoIdxIssues') 
BEGIN
 CREATE Table #AutoIdxIssues (
   FromAutoIDx int,
   ToAutoIDx int
 )
END

TRUNCATE table #AutoIdxIssues

--add all the clients
 TRUNCATE TABLE _etblAllocsDCLinkRangeTemp
  insert into _etblAllocsDCLinkRangeTemp (DCLink)
   (Select DClink from Client where bforcurAcc =0)

--Fill the Temp table sage uses
exec _espAllocsAllToTableARAP 'AR'


EXEC _AS_SP_AllocRecalculation @ARAP = 'AR',@CorrectErrors =  1


--Run it again as the fix above changed allocations:
exec _espAllocsAllToTableARAP 'AR'

-------------------------------------
-------------------------------------
-------------------------------------
INSERT INTO #AutoIdxIssues (fromAutoidx,toAutoIdx)
--No mirror 1
Select
 NoMirror.iFromRecID  as AutoIdx
,NoMirror.iToRecID as [Alocated To AutoIdx]

 from 
 (Select A1.* from  _etblAllocsTemp A1 
 left join _etblAllocsTemp A2 on A2.iToRecID = A1.iFromRecID and A1.iToRecID = A2.iFromRecID 
 where A2.idAllocsTemp Is Null) NoMirror 
 left outer join PostAR PAR on NoMirror.iFromRecID = PAR.AutoIdx 
 left outer join Client C on NoMirror.iAccountID = C.DCLink 
 order by NoMirror.iFromRecID

-------------------------------------
-------------------------------------
INSERT INTO #AutoIdxIssues (fromAutoidx,toAutoIdx)
 --Alloc diff
 Select	
	  A1.iFromRecID  as AutoIdx
	, A1.iToRecID as [Alocated To AutoIdx]

	 from _etblAllocsTemp A1 
	 left outer join _etblAllocsTemp A2 on A2.iToRecID=A1.iFromRecID and A1.iToRecID=A2.iFromRecID 
	 left outer join PostAR PAR on A1.iFromRecID = PAR.AutoIdx
	  left outer join Client C on A1.iAccountID = C.DCLink 
	  where ((select sum(A3.fAmount)
				 from _etblAllocsTemp A3
					where A3.iFromRecID=A1.iFromRecID 
					and A3.iToRecID=A1.iToRecID 
				group by A3.iFromRecID, A3.iToRecID) 
			<> (select sum(A4.fAmount) 
					from _etblAllocsTemp A4 
					where A4.iFromRecID=A2.iFromRecID and A4.iToRecID=A2.iToRecID 
					group by A4.iFromRecID, A4.iToRecID)) 
	order by A1.iFromRecID

-------------------------------------
-------------------------------------
-------------------------------------


			--START FV CURSOR:
			DECLARE AllocCursor CURSOR FOR
				SELECT fromAutoidx,toAutoIdx from #AutoIdxIssues
			--Cursor declares:
			DECLARE @fromAutoidxS Int
			DECLARE @toAutoIdxS Int

			--Open btblLinesCursor
			OPEN AllocCursor
				
			-- Here we loop through all lines:
			FETCH NEXT FROM AllocCursor INTO @fromAutoidxS,@toAutoIdxS
			WHILE @@FETCH_STATUS = 0
				BEGIN	
					--Remove the alloc
						EXEC _AS_SP_RemoveBrokenAllocationAR  @FromAutoidx = @fromAutoidxS, @ToAutoIdx  = @toAutoIdxS				

				--CURSOR NEXT: AllocCursor
				FETCH NEXT FROM AllocCursor INTO  @fromAutoidxS,@toAutoIdxS
			END --WHILE @@FETCH_STATUS... AllocCursor
			--Cleaning up cursor:
			CLOSE AllocCursor
			DEALLOCATE AllocCursor





 --Get The max Date
 DECLARE @MaxTxDateAR Date
 SELECT @MaxTxDateAR =  MAX(TxDate) from postar 
 
--Set the outstanding where cAllocs is NULL
UPDATE ar 

	Set Outstanding = Case WHEN DEBIT > 0 Then Debit	
						ELSE Credit *-1 END
 from postar ar
WHERE cAllocs is null

--Remove the zero's
UPDATE 
	postar set Debit = COALESCE(debit,0) ,Credit = COALESCE(Credit,0)


TRUNCATE table #AutoIdxIssues

Drop table #AutoIdxIssues


END

GO

--SELECT * from (
--	Select ar.Accountlink, ROUND(sum(ar.Debit-ar.Credit),2) DMC from postar ar
--		GRoup by ar.Accountlink) DMC
--	LEFT JOIN (Select Accountlink, ROUND(sum(Outstanding),2) as OS from postar GRoup by Accountlink) OS on OS.AccountLink = DMC.Accountlink
--WHERE DMC.DMC <> OS.OS



--SELECT 'AR' as TRType, (Select Sum(debit-Credit) from postar ) - (Select Sum(outstanding) from postar ) as  Balance

2) Execute the AR :

AR Execute

exec _AS_SP_FixAllocationIssuesAR

3) execute the AP:

Execute AP Fix

Exec _AS_SP_FixAllocationIssuesAP

Space shortcuts

Page tree