select * from postgl --TO CHANGE TO RIGHT QUERY! - jan schiet eens op.
--exec _AS_SP_AllocRecalculation 'AP', 0
-- =============================================
-- Author: Jan van der Hilst
-- Company: Asamco B.V - Camelsa CCG
-- Create date: 09/05/2017
-- =============================================
CREATE PROCEDURE [dbo]._AS_SP_AllocRecalculation
@ARAP varchar(2),
@CorrectErrors bit
AS
BEGIN
--USAGE:
--exec _AS_SP_AllocRecalculation 'AP', 0
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
--if correct errors, ONLY home currency accounts!
IF @CorrectErrors = 1
BEGIN
insert into _etblAllocsDCLinkRangeTemp (DCLink)
(Select DClink from Client where bforcurAcc =0)
END
ELSE
BEGIN
insert into _etblAllocsDCLinkRangeTemp (DCLink)
(Select DClink from Client ) --where bforcurAcc =0)
END
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
--if correct errors, ONLY home currency accounts!
IF @CorrectErrors = 1
BEGIN
insert into _etblAllocsDCLinkRangeTemp (DCLink)
(Select DClink from Vendor where bforcurAcc =0)
END
ELSE
BEGIN
insert into _etblAllocsDCLinkRangeTemp (DCLink)
(Select DClink from Vendor ) --where bforcurAcc =0)
END
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
|