1) Run the stored procs on the database
-- =============================================
-- 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
|
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'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
|
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
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
|
2) Execute the AR :
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 TRUNCATE TABLE _etblAllocsDCLinkRangeTemp --Fill the Temp table sage uses 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 EXEC _AS_SP_AllocRecalculation @ARAP = 'AR',@CorrectErrors = 1 --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 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 |
3) execute the AP:
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
TRUNCATE TABLE _etblAllocsDCLinkRangeTemp
--Fill the Temp table sage uses
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
EXEC _AS_SP_AllocRecalculation @ARAP = 'AP',@CorrectErrors = 1
--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
--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 |