Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP (Archived)

GP AR Aging report

Posted on by 110

Data Doesn't match in Dynamics GP and SSRS report. Total Due is showing different value in SSRS.

I can't find where's the error here

USE [QNAQ]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
 
ALTER FUNCTION [dbo].[HistoricalAging2](@x AS DATETIME)
RETURNS TABLE
AS
RETURN
 
WITH CTE1 AS
(
SELECT CM.CUSTNAME AS CustName,
SUM(CASE WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM
                 WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1
                        ELSE 0 END) AS 'TotalDue',
SUM(CASE WHEN DATEDIFF(DAY,RM.GLPOSTDT,@x)<31 THEN
                                      CASE WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM
                           WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1
                                                  ELSE 0 END ELSE 0 END) AS 'CurrentDays',
SUM(CASE WHEN DATEDIFF(DAY,RM.GLPOSTDT,@x)>30 AND DATEDIFF(DAY,RM.GLPOSTDT,@x)<61  THEN
                                      CASE WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM
                           WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1
                                                   ELSE 0 END ELSE 0 END) AS Group1,
SUM(CASE WHEN DATEDIFF(DAY,RM.GLPOSTDT,@x)>60 AND DATEDIFF(DAY,RM.GLPOSTDT,@x)<91  THEN
                                      CASE WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM
                      WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1
                                      ELSE 0 END ELSE 0 END) AS Group2,
SUM(CASE WHEN DATEDIFF(DAY,RM.GLPOSTDT,@x)>90 THEN
                                      CASE WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM
                      WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1
                                      ELSE 0 END ELSE 0 END) AS Group3
FROM RM20101 AS RM
JOIN RM00101 AS CM ON RM.CUSTNMBR = CM.CUSTNMBR
GROUP BY CM.CUSTNAME
HAVING SUM(CASE WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM
                        WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1
                                                ELSE 0 END)<>0
UNION
SELECT CM1.CUSTNAME AS CustName,
SUM(CASE WHEN @x>= RM1.APPLYTOGLPOSTDATE AND @x < RM1.APFRDCDT THEN RM1.APPTOAMT ELSE 0 END) AS TotalDue,
SUM(CASE WHEN DATEDIFF(DAY,RM1.APPLYTOGLPOSTDATE,@x)<31 THEN
         CASE WHEN @x>= RM1.APPLYTOGLPOSTDATE AND @x < RM1.APFRDCDT THEN RM1.APPTOAMT ELSE 0 END ELSE 0 END) AS 'CurrentDays',
SUM(CASE WHEN DATEDIFF(DAY,RM1.APPLYTOGLPOSTDATE,@x)>30 AND DATEDIFF(DAY,RM1.APPLYTOGLPOSTDATE,@x)<61 THEN
         CASE WHEN @x>= RM1.APPLYTOGLPOSTDATE AND @x < RM1.APFRDCDT THEN RM1.APPTOAMT ELSE 0 END ELSE 0 END) AS Group1,
SUM(CASE WHEN DATEDIFF(DAY,RM1.APPLYTOGLPOSTDATE,@x)>60 AND DATEDIFF(DAY,RM1.APPLYTOGLPOSTDATE,@x)<91 THEN
         CASE WHEN @x>= RM1.APPLYTOGLPOSTDATE AND @x < RM1.APFRDCDT THEN RM1.APPTOAMT ELSE 0 END ELSE 0 END) AS Group2,
SUM(CASE WHEN DATEDIFF(DAY,RM1.APPLYTOGLPOSTDATE,@x)>90 THEN
         CASE WHEN @x>= RM1.APPLYTOGLPOSTDATE AND @x < RM1.APFRDCDT THEN RM1.APPTOAMT ELSE 0 END ELSE 0 END) AS Group3
FROM RM20201 AS RM1
JOIN RM00101 AS CM1
ON RM1.CUSTNMBR = CM1.CUSTNMBR
GROUP BY CM1.CUSTNAME
HAVING SUM(CASE WHEN @x>= RM1.APPLYTOGLPOSTDATE AND @x < RM1.APFRDCDT THEN RM1.APPTOAMT ELSE 0 END) <> 0
UNION
SELECT CM2.CUSTNAME AS CustName,
SUM(CASE WHEN @x>= RM2.APPLYTOGLPOSTDATE AND @x < RM2.APFRDCDT THEN RM2.APPTOAMT ELSE 0 END) AS TotalDue,
SUM(CASE WHEN DATEDIFF(DAY,RM2.APPLYTOGLPOSTDATE,@x)<31 THEN
         CASE WHEN @x>= RM2.APPLYTOGLPOSTDATE AND @x < RM2.APFRDCDT THEN RM2.APPTOAMT ELSE 0 END ELSE 0 END) AS 'CurrentDays',
SUM(CASE WHEN DATEDIFF(DAY,RM2.APPLYTOGLPOSTDATE,@x)>30 AND DATEDIFF(DAY,RM2.APPLYTOGLPOSTDATE,@x)<61 THEN
         CASE WHEN @x>= RM2.APPLYTOGLPOSTDATE AND @x < RM2.APFRDCDT THEN RM2.APPTOAMT ELSE 0 END ELSE 0 END) AS Group1,
SUM(CASE WHEN DATEDIFF(DAY,RM2.APPLYTOGLPOSTDATE,@x)>60 AND DATEDIFF(DAY,RM2.APPLYTOGLPOSTDATE,@x)<91 THEN
         CASE WHEN @x>= RM2.APPLYTOGLPOSTDATE AND @x < RM2.APFRDCDT THEN RM2.APPTOAMT ELSE 0 END ELSE 0 END) AS Group2,
SUM(CASE WHEN DATEDIFF(DAY,RM2.APPLYTOGLPOSTDATE,@x)>90 THEN
         CASE WHEN @x>= RM2.APPLYTOGLPOSTDATE AND @x < RM2.APFRDCDT THEN RM2.APPTOAMT ELSE 0 END ELSE 0 END) AS Group3
FROM RM30201 AS RM2
JOIN RM00101 AS CM2
ON RM2.CUSTNMBR = CM2.CUSTNMBR
GROUP BY CM2.CUSTNAME
HAVING SUM(CASE WHEN @x>= RM2.APPLYTOGLPOSTDATE AND @x < RM2.APFRDCDT THEN RM2.APPTOAMT ELSE 0 END) <> 0
)
 
SELECT CustName, SUM(TotalDue) AS 'TotalDue', SUM(CurrentDays) AS 'Current',
SUM(Group1) AS '31-60 Days', SUM(Group2) AS '61-90 Days', SUM(Group3) AS '91+ Days'
FROM CTE1
GROUP BY CustName;

*This post is locked for comments

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans