Page History

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