Excellent SQL Server Backup and Maintenance Solution

One of the first things I have to do after I’ve installed Dynamics GP and created the DYNAMICS, TWO and company databases, is setup jobs to backup and optimize those databases. The best method I’ve found to do that is a free solution developed by Ola Hallengren that can be found here. This solution received the 2010 Editor’s Choice Bronze Award for best Free SQL Tool, while the SQL Server Community gave it the Gold. After a previous write-up of this solution Ola contacted me directly asking for my feedback, and he has been in touch several times since – he clearly cares about his solution and makes regular improvements as SQL Server features are added or changed.

The solution can be installed by downloading and running a single sql script – direct link here. Upon opening the script in SQL Management Studio, I typically make two changes. First I change the value of the @BackupDirectory parameter, highlighted below, to point to the location I want the backups stored. The backup job will create a folder with the server name in this folder, with folders for each database below that, followed by folders for each of the backup types – full, differential, and log.

The second change I make is that I like to keep 3 days worth of backups on disk if possible, and the scripts defaults to cleaning up backups older than 24 hours, so I like to change that to 72 hours. This can certainly be done afterwards by editing the job step but I like to do it up front. I usually do a quick find and replace operation on “24, ” replacing with 72.

After the replace is completed, I run the script and the highlighted jobs below are created. From there I open each job, set the desired schedule for each one, and I’m done.

I use this script because I want consistently configured backup and database maintenance jobs on all of the SQL Servers I manage for my customers. It takes 10-15 minutes to configure and I love that it’s smart enough to not throw an error on the whole transaction log job if someone creates a new company and the recovery is still set to Simple – it skips that database and moves on to the next one. It is also much smarter then the SQL Server Maintenance Plans about whether it rebuilds or reorganizes the databases indexes. Visit Ola’s site, http://ola.hallengren.com/ to read more about the solution or view the documentation.

http://www.andynifong.com/blog/2011/1/24/excellent-sql-server-backup-and-maintenance-solution.html

Amazon's "Customers who bought this item also bought..."

This set of views is designed to show related items purchased with an inventory item in Dynamics GP.
This is similar in concept to Amazon's "Customers who bought this item also bought..."
The final view should ideally be restricted to a single item and will return items that have been bought (invoiced) with the
restricted item and the % of times they were bought together.

Using Fabrikam as a example, if you run this for item '5-Fee', you'll see that item '5-TVLLABOR' was on the same invoice as '5-Fee' 56%
of the time. The goal is to find related products for suggestive selling or product positioning.

These were built as views to allow them to be built into a Smartlist Builder based Smartlist. The use of views means that performance
suffers because the view has to hold all of the combinations and it may slow down with a large number of items and transactions.

I also have a Stored Procedure at http://www.dynamicaccounting.net that does the same thing and is more efficient.
That is a better option for custom programming, Crystal Reports or SSRS.

*/


--Create view to build denominator for % calc. This is the number of invoices per item.
Create View RelatedItems_InvoiceCount as
Select ItemNmbr as ItemNumber, COUNT(SOPNumbe) as InvoiceCount
FROM sop30300
WHERE sopnumbe IN (SELECT sopnumbe FROM SOP30300) and SOPTYPE=3
Group by ITEMNMBR

--Create view to hold the the relationships. This holds other items on the same invoice as an item.
Create View RelatedItems_Relationship as
SELECT SOP30300.SOPTYPE, SOP30300.SOPNUMBE, SOP30300.ITEMNMBR, SOP30300_2.ITEMNMBR AS RelatedItem
FROM SOP30300 INNER JOIN
SOP30300 AS SOP30300_2 ON SOP30300.SOPTYPE = SOP30300_2.SOPTYPE AND SOP30300.SOPNUMBE = SOP30300_2.SOPNUMBE
WHERE (SOP30300.SOPNUMBE IN
(SELECT SOPNUMBE
FROM SOP30300 AS SOP30300_1)) AND (SOP30300.SOPTYPE = 3) and SOP30300.ITEMNMBR <>SOP30300_2.ITEMNMBR

GROUP BY SOP30300.SOPTYPE, SOP30300.SOPNUMBE, SOP30300.ITEMNMBR, SOP30300_2.SOPNUMBE, SOP30300_2.SOPTYPE, SOP30300_2.ITEMNMBR
order by itemnmbr


--Create view to hold both related items and the % of common invoices they appeared on
Create View RelatedItems_PcntAlsoBought as
SELECT RelatedItems_Relationship.itemnmbr as ItemNumber, RelatedItems_Relationship.RelatedItem AS RelatedItem, IV00101.ITEMDESC AS Description,
Cast(cast(COUNT(SOPNUMBE) as decimal(8,2))/ CAST( RelatedItems_InvoiceCount.InvoiceCount as decimal(8,2)) *100 as decimal(8,2)) AS PcntAlsoBought
FROM RelatedItems_Relationship INNER JOIN
IV00101 ON RelatedItems_Relationship.RelatedItem = IV00101.ITEMNMBR
INNER JOIN
RelatedItems_InvoiceCount ON RelatedItems_Relationship.ItemNmbr = RelatedItems_InvoiceCount.ItemNumber
Where RelatedItems_Relationship.ITEMNMBR in (Select ITEMNMBR from IV00101)
GROUP BY RelatedItems_Relationship.itemnmbr,RelatedItems_Relationship.Relateditem, IV00101.ITEMDESC, RelatedItems_InvoiceCount.InvoiceCount

--Execute the view limiting it to one item and sorting by highest %
Select * from RelatedItems_PcntAlsoBought where ItemNumber='5-Fee'
Order by 4 desc

Wrong PO Number

Welcome to another edition of my blog! This time around, I want to talk -- not literally -- about a common occurrence experimented in many Dynamics GP environments. When the pressure amounts, some company buyers may find themselves accidentally overriding the PO number field, a common misshap that may cause wasted time or the need to void and re-enter the document. Now, think for an instance, if you are using the Manufacturing module or any third-party product how cumbersome the task can become.

Fortunately, there is help on the way! I have developed a script based on a previous post, that will scan for the PONUMBER field in all tables in the company database. The script will automatically produce another script in the Results pane that can be copied and pasted into a new Query window and be executed against the company database.

The following example shows the script with the new PO number (@newponumber) and the old PO number (@oldponumber) variables being used to facilitate the interfacing with the person executing the change.

DECLARE @newponumber char(25), @oldponumber char(25)
SET @newponumber = 'PO1023'
SET @oldponumber = 'PO1001'

SELECT DISTINCT 'UPDATE ' + RTRIM(objs.name) + ' SET PONUMBER = ''' + RTRIM(@newponumber) + ''' WHERE PONUMBER = ''' + RTRIM(@oldponumber) + ''''
FROM syscolumns cols
INNER JOIN sysobjects objs ON (cols.id = objs.id)
INNER JOIN sysindexes indx on (cols.id = indx.id)
WHERE (cols.name = 'PONUMBER') and (objs.xtype = 'U') and (indx.rowcnt <> 0)


When this script is executed against plain vanilla GP v10, it produces the following results:
UPDATE POP10100 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP10110 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP10310 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP10500 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP30100 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP30110 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP30310 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP40100 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE SOP60100 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
(9 row(s) affected)

http://dynamicsgpblogster.blogspot.com/2008/04/wrong-po-number.html

SQL Code for GL Trial Balance

Today, I just decided to post a simple code snippet to generate a detailed GL Trial Balance with reference to the various sub ledgers, including the multi-dimensional information.

IF EXISTS ( SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[vw_GLTrialBalance]') AND OBJECTPROPERTY(id, N'IsView') = 1 )
DROP VIEW [dbo].[vw_GLTrialBalance]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vw_GLTrialBalance
AS SELECT dbo.GL00105.ACTNUMST AS [ACCOUNTNUMBER],
dbo.GL00100.ACTDESCR AS ACCOUNTDESCRIPTION,
dbo.GL00102.ACCATDSC AS CATEGORY,
dbo.GL00100.MNACSGMT AS [MAINACCOUNT],
CASE dbo.GL00100.ACCTTYPE
WHEN 1 THEN 'POSTING ACCOUNT'
WHEN 2 THEN 'UNIT ACCOUNT'
WHEN 3 THEN 'POSTING ALLOCATION ACCOUNT'
WHEN 4 THEN 'UNIT ALLOCATION ACCOUNT'
END AS [ACCOUNTTYPE],
CASE dbo.GL00100.ACTIVE
WHEN 1 THEN 'ACTIVE'
WHEN 0 THEN 'INACTIVE'
END AS [STATUS],
CASE dbo.GL00100.PSTNGTYP
WHEN 0 THEN 'BALANCE SHEET'
WHEN 1 THEN 'PROFT AND LOSS'
END AS [POSTINGTYPE],
CASE dbo.GL00100.TPCLBLNC
WHEN 0 THEN 'DEBIT'
WHEN 1 THEN 'CREDIT'
END AS [TYPICALBALANCE],
dbo.GL00100.ACTNUMBR_1 AS SEGMENT1,
dbo.GL00100.ACTNUMBR_2 AS SEGMENT2,
dbo.GL00100.ACTNUMBR_3 AS SEGMENT3,
dbo.GL00100.ACTNUMBR_4 AS SEGMENT4,
dbo.GL00100.ACTNUMBR_5 AS SEGMENT5,
dbo.GL00100.ACTNUMBR_6 AS SEGMENT6,
dbo.GL00100.ACTNUMBR_7 AS SEGMENT7,
dbo.GL00100.ACTNUMBR_8 AS SEGMENT8,
dbo.GL00100.ACTNUMBR_9 AS SEGMENT9,
dbo.GL00100.ACTNUMBR_10 AS SEGMENT10,
dbo.GL20000.OPENYEAR AS [FISCALYEAR],
dbo.GL20000.JRNENTRY AS [JVNUMBER],
dbo.GL20000.REFRENCE AS REFERENCE,
dbo.GL20000.DSCRIPTN AS [ADDLDESCRIPTION],
dbo.GL20000.TRXDATE AS [JVDATE],
dbo.GL20000.TRXSORCE AS [AUDITTRAIL],
dbo.GL20000.LASTUSER AS [USERID],
CASE dbo.GL20000.SERIES
WHEN 1 THEN 'ALL'
WHEN 2 THEN 'FINANCIAL'
WHEN 3 THEN 'SALES'
WHEN 4 THEN 'PURCHASING'
WHEN 5 THEN 'INVENTORY'
WHEN 6 THEN 'PAYROLL'
WHEN 7 THEN 'PROJECT'
END AS SERIES,
dbo.GL20000.ORDOCNUM AS [ORIGDOCNUMBER],
dbo.GL20000.ORMSTRNM AS [ORIGMASTERNAME],
dbo.GL20000.ORTRXSRC AS [ORIGAUDITTRAIL],
dbo.GL20000.CURNCYID AS [CURRENCYID],
dbo.GL20000.CRDTAMNT AS [CREDITAMOUNT],
dbo.GL20000.DEBITAMT AS [DEBITAMOUNT],
( dbo.GL20000.DEBITAMT - dbo.GL20000.CRDTAMNT ) NETAMOUNT,
dbo.GL20000.VOIDED AS [VOIDSTATUS],
dbo.GL20000.Back_Out_JE AS [BACKOUTJV],
dbo.GL20000.Back_Out_JE_Year AS [BACKOUTJVYEAR],
dbo.GL20000.Correcting_JE AS [CORRECTINGJV],
dbo.GL20000.Correcting_JE_Year AS [CORRECTINGJVYEAR],
dbo.GL20000.Original_JE AS [ORIGINALJV],
dbo.GL20000.Original_JE_Year AS [ORIGINALJVYEAR],
CASE MONTH(dbo.GL20000.TRXDATE)
WHEN 1 THEN 'JAN - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 2 THEN 'FEB - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 3 THEN 'MAR - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 4 THEN 'APR - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 5 THEN 'MAY - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 6 THEN 'JUN - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 7 THEN 'JUL - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 8 THEN 'AUG - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 9 THEN 'SEP - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 10 THEN 'OCT - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 11 THEN 'NOV - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 12 THEN 'DEC - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
END AS [JVMONTH],
dbo.GL20000.PERIODID,
dbo.GL20000.SOURCDOC,
dbo.DTA10100.GROUPID ANALYSISGROUPID,
dbo.DTA10100.GROUPAMT ANALYSISGROUPAMOUNT,
dbo.DTA10200.CODEID ANALYSISCODEID,
dbo.DTA10200.POSTDESC ANALYSISPOSTINGDESC,
dbo.DTA10200.CODEAMT ANALYSISCODEAMOUNT
FROM dbo.GL20000
INNER JOIN dbo.GL00100 ON dbo.GL20000.ACTINDX = dbo.GL00100.ACTINDX
INNER JOIN dbo.GL00102 ON dbo.GL00100.ACCATNUM = dbo.GL00102.ACCATNUM
INNER JOIN dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX
LEFT OUTER JOIN dbo.DTA10100 ON dbo.DTA10100.JRNENTRY = dbo.GL20000.JRNENTRY
AND dbo.DTA10100.ACTINDX = dbo.GL20000.ACTINDX
LEFT OUTER JOIN dbo.DTA10200 ON dbo.DTA10200.DTAREF = dbo.DTA10100.DTAREF
UNION
SELECT dbo.GL00105.ACTNUMST AS [ACCOUNTNUMBER],
dbo.GL00100.ACTDESCR AS ACCOUNTDESCRIPTION,
dbo.GL00102.ACCATDSC AS CATEGORY,
dbo.GL00100.MNACSGMT AS [MAINACCOUNT],
CASE dbo.GL00100.ACCTTYPE
WHEN 1 THEN 'POSTING ACCOUNT'
WHEN 2 THEN 'UNIT ACCOUNT'
WHEN 3 THEN 'POSTING ALLOCATION ACCOUNT'
WHEN 4 THEN 'UNIT ALLOCATION ACCOUNT'
END AS [ACCOUNTTYPE],
CASE dbo.GL00100.ACTIVE
WHEN 1 THEN 'ACTIVE'
WHEN 0 THEN 'INACTIVE'
END AS [STATUS],
CASE dbo.GL00100.PSTNGTYP
WHEN 0 THEN 'BALANCE SHEET'
WHEN 1 THEN 'PROFT AND LOSS'
END AS [POSTINGTYPE],
CASE dbo.GL00100.TPCLBLNC
WHEN 0 THEN 'DEBIT'
WHEN 1 THEN 'CREDIT'
END AS [TYPICALBALANCE],
dbo.GL00100.ACTNUMBR_1 AS SEGMENT1,
dbo.GL00100.ACTNUMBR_2 AS SEGMENT2,
dbo.GL00100.ACTNUMBR_3 AS SEGMENT3,
dbo.GL00100.ACTNUMBR_4 AS SEGMENT4,
dbo.GL00100.ACTNUMBR_5 AS SEGMENT5,
dbo.GL00100.ACTNUMBR_6 AS SEGMENT6,
dbo.GL00100.ACTNUMBR_7 AS SEGMENT7,
dbo.GL00100.ACTNUMBR_8 AS SEGMENT8,
dbo.GL00100.ACTNUMBR_9 AS SEGMENT9,
dbo.GL00100.ACTNUMBR_10 AS SEGMENT10,
dbo.GL30000.HSTYEAR AS [FISCALYEAR],
dbo.GL30000.JRNENTRY AS [JVNUMBER],
dbo.GL30000.REFRENCE AS REFERENCE,
dbo.GL30000.DSCRIPTN AS [ADDLDESCRIPTION],
dbo.GL30000.TRXDATE AS [JVDATE],
dbo.GL30000.TRXSORCE AS [AUDITTRAIL],
dbo.GL30000.LASTUSER AS [USERID],
CASE dbo.GL30000.SERIES
WHEN 1 THEN 'ALL'
WHEN 2 THEN 'FINANCIAL'
WHEN 3 THEN 'SALES'
WHEN 4 THEN 'PURCHASING'
WHEN 5 THEN 'INVENTORY'
WHEN 6 THEN 'PAYROLL'
WHEN 7 THEN 'PROJECT'
END AS SERIES,
dbo.GL30000.ORDOCNUM AS [ORIGDOCNUMBER],
dbo.GL30000.ORMSTRNM AS [ORIGMASTERNAME],
dbo.GL30000.ORTRXSRC AS [ORIGAUDITTRAIL],
dbo.GL30000.CURNCYID AS [CURRENCYID],
dbo.GL30000.CRDTAMNT AS [CREDITAMOUNT],
dbo.GL30000.DEBITAMT AS [DEBITAMOUNT],
( dbo.GL30000.DEBITAMT - dbo.GL30000.CRDTAMNT ) NETAMOUNT,
dbo.GL30000.VOIDED AS [VOIDSTATUS],
dbo.GL30000.Back_Out_JE AS [BACKOUTJV],
dbo.GL30000.Back_Out_JE_Year AS [BACKOUTJVYEAR],
dbo.GL30000.Correcting_JE AS [CORRECTINGJV],
dbo.GL30000.Correcting_JE_Year AS [CORRECTINGJVYEAR],
dbo.GL30000.Original_JE AS [ORIGINALJV],
dbo.GL30000.Original_JE_Year AS [ORIGINALJVYEAR],
CASE MONTH(dbo.GL30000.TRXDATE)
WHEN 1 THEN 'JAN - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 2 THEN 'FEB - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 3 THEN 'MAR - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 4 THEN 'APR - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 5 THEN 'MAY - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 6 THEN 'JUN - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 7 THEN 'JUL - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 8 THEN 'AUG - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 9 THEN 'SEP - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 10 THEN 'OCT - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 11 THEN 'NOV - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 12 THEN 'DEC - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
END AS [JVMONTH],
dbo.GL30000.PERIODID,
dbo.GL30000.SOURCDOC,
dbo.DTA10100.GROUPID ANALYSISGROUPID,
dbo.DTA10100.GROUPAMT ANALYSISGROUPAMOUNT,
dbo.DTA10200.CODEID ANALYSISCODEID,
dbo.DTA10200.POSTDESC ANALYSISPOSTINGDESC,
dbo.DTA10200.CODEAMT ANALYSISCODEAMOUNT
FROM dbo.GL30000
INNER JOIN dbo.GL00100 ON dbo.GL30000.ACTINDX = dbo.GL00100.ACTINDX
INNER JOIN dbo.GL00102 ON dbo.GL00100.ACCATNUM = dbo.GL00102.ACCATNUM
INNER JOIN dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX
LEFT OUTER JOIN dbo.DTA10100 ON dbo.DTA10100.JRNENTRY = dbo.GL30000.JRNENTRY
AND dbo.DTA10100.ACTINDX = dbo.GL30000.ACTINDX
LEFT OUTER JOIN dbo.DTA10200 ON dbo.DTA10200.DTAREF = dbo.DTA10100.DTAREF

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT SELECT ON vw_GLTrialBalance TO DYNGRP

http://cvakumar.com/msdynamics/2009/04/26/sql-code-for-gl-trial-balance/

Receivables HATB Report (using Document Date)

Today, I have decided to post a script for the Receivables Historical Aged Trial Balance (HATB), which is generated by picking transactions based on the GL posting date for the receivables transactions.
This script can also be used as a base to reconcile the sub-ledger balances as of a certain date with the corresponding GL transactions. The script for generating general ledger balances is available here.

DECLARE @ASOFDATE DATETIME
 SET @ASOFDATE = '2017-04-12'
 SELECT  X.CUSTOMERNUMBER,
        X.CORPORATECUSTNUMBER,
        X.DOCUMENTNO,
        X.DOCUMENTTYPE,
        X.DOCUMENTDATE,
        X.GLPOSTINGDATE,
        X.DOCUMENTAMT,
        X.APPLIEDAMT,
        X.WRITEOFFAMT,
        X.DISCTAKENAMT,
        X.REALGAINLOSSAMT,
        ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT
          + X.REALGAINLOSSAMT ) AS CURRENTAMTFROM    ( SELECT    Z.CUSTNMBR AS CUSTOMERNUMBER,
                    Z.CPRCSTNM AS CORPORATECUSTNUMBER,
                    Z.DOCNUMBR AS DOCUMENTNO,
                    Z.DOCTYPE AS DOCUMENTTYPE,
                    Z.DOCDATE AS DOCUMENTDATE,
                    Z.GLPOSTINGDATE AS GLPOSTINGDATE,
                    Z.ORTRXAMT AS DOCUMENTAMT,
                    CASE WHEN Z.[RMDTYPAL] <= 6
                         THEN ISNULL(( SELECT   SUM(Y.[ActualApplyToAmount])
                                       FROM     ( SELECT    [CUSTNMBR],
                                                            [DATE1],
                                                            [APFRDCDT],
                                                            [APTODCNM],
                                                            [APTODCTY],
                                                            [ActualApplyToAmount]
                                                  FROM      dbo.[RM20201]
                                                  UNION
                                                  SELECT    [CUSTNMBR],
                                                            [DATE1],
                                                            [APFRDCDT],
                                                            [APTODCNM],
                                                            [APTODCTY],
                                                            [ActualApplyToAmount]
                                                  FROM      dbo.[RM30201]
                                                ) Y
                                       WHERE    Y.[DATE1] <= @ASOFDATE
                                                AND Y.[APFRDCDT] <= @ASOFDATE
                                                AND Y.[CUSTNMBR] = Z.CUSTNMBR
                                                AND Y.[APTODCNM] = Z.DOCNUMBR
                                                AND Y.[APTODCTY] = Z.RMDTYPAL
                                     ), 0)
                         WHEN Z.[RMDTYPAL] > 7
                              AND Z.[RMDTYPAL] <= 9
                         THEN ISNULL(( SELECT   SUM(Y.[ActualApplyToAmount])
                                       FROM     ( SELECT    [CUSTNMBR],
                                                            [DATE1],
                                                            [APTODCDT],
                                                            [APFRDCNM],
                                                            [APFRDCTY],
                                                            [ActualApplyToAmount]
                                                  FROM      dbo.[RM20201]
                                                  UNION
                                                  SELECT    [CUSTNMBR],
                                                            [DATE1],
                                                            [APTODCDT],
                                                            [APFRDCNM],
                                                            [APFRDCTY],
                                                            [ActualApplyToAmount]
                                                  FROM      dbo.[RM30201]
                                                ) Y
                                       WHERE    Y.[DATE1] <= @ASOFDATE
                                                AND Y.[APTODCDT] <= @ASOFDATE
                                                AND Y.[CUSTNMBR] = Z.CUSTNMBR
                                                AND Y.[APFRDCNM] = Z.DOCNUMBR
                                                AND Y.[APFRDCTY] = Z.RMDTYPAL
                                     ), 0)
                         ELSE 0
                    END AS APPLIEDAMT,
                    CASE WHEN Z.[RMDTYPAL] <= 6
                         THEN ISNULL(( SELECT   SUM(Y.[WROFAMNT])
                                       FROM     ( SELECT    [CUSTNMBR],
                                                            [DATE1],
                                                            [APFRDCDT],
                                                            [APTODCNM],
                                                            [APTODCTY],
                                                            [WROFAMNT]
                                                  FROM      dbo.[RM20201]
                                                  UNION
                                                  SELECT    [CUSTNMBR],
                                                            [DATE1],
                                                            [APFRDCDT],
                                                            [APTODCNM],
                                                            [APTODCTY],
                                                            [WROFAMNT]
                                                  FROM      dbo.[RM30201]
                                                ) Y
                                       WHERE    Y.[DATE1] <= @ASOFDATE
                                                AND Y.[APFRDCDT] <= @ASOFDATE
                                                AND Y.[CUSTNMBR] = Z.CUSTNMBR
                                                AND Y.[APTODCNM] = Z.DOCNUMBR
                                                AND Y.[APTODCTY] = Z.RMDTYPAL
                                     ), 0)
                         ELSE 0
                    END AS WRITEOFFAMT,
                    CASE WHEN Z.[RMDTYPAL] <= 6
                         THEN ISNULL(( SELECT   SUM(Y.[DISTKNAM])
                                       FROM     ( SELECT    [CUSTNMBR],
                                                            [DATE1],
                                                            [APFRDCDT],
                                                            [APTODCNM],
                                                            [APTODCTY],
                                                            [DISTKNAM]
                                                  FROM      dbo.[RM20201]
                                                  UNION
                                                  SELECT    [CUSTNMBR],
                                                            [DATE1],
                                                            [APFRDCDT],
                                                            [APTODCNM],
                                                            [APTODCTY],
                                                            [DISTKNAM]
                                                  FROM      dbo.[RM30201]
                                                ) Y
                                       WHERE    Y.[DATE1] <= @ASOFDATE
                                                AND Y.[APFRDCDT] <= @ASOFDATE
                                                AND Y.[CUSTNMBR] = Z.CUSTNMBR
                                                AND Y.[APTODCNM] = Z.DOCNUMBR
                                                AND Y.[APTODCTY] = Z.RMDTYPAL
                                     ), 0)
                         ELSE 0
                    END AS DISCTAKENAMT,
                    CASE WHEN Z.[RMDTYPAL] > 7
                              AND Z.[RMDTYPAL] <= 9
                         THEN ISNULL(( SELECT   SUM(Y.[RLGANLOS])
                                       FROM     ( SELECT    [CUSTNMBR],
                                                            [DATE1],
                                                            [APTODCDT],
                                                            [APFRDCNM],
                                                            [APFRDCTY],
                                                            [RLGANLOS]
                                                  FROM      dbo.[RM20201]
                                                  UNION
                                                  SELECT    [CUSTNMBR],
                                                            [DATE1],
                                                            [APTODCDT],
                                                            [APFRDCNM],
                                                            [APFRDCTY],
                                                            [RLGANLOS]
                                                  FROM      dbo.[RM30201]
                                                ) Y
                                       WHERE    Y.[DATE1] <= @ASOFDATE
                                                AND Y.[APTODCDT] <= @ASOFDATE
                                                AND Y.[CUSTNMBR] = Z.CUSTNMBR
                                                AND Y.[APFRDCNM] = Z.DOCNUMBR
                                                AND Y.[APFRDCTY] = Z.RMDTYPAL
                                     ), 0)
                         ELSE 0
                    END AS REALGAINLOSSAMT
          FROM      ( SELECT    A.[CUSTNMBR],
                                A.[CPRCSTNM],
                                A.[DOCNUMBR],
                                A.[RMDTYPAL],
                                B.[DOCDESCR] AS DOCTYPE,
                                A.[DOCDATE],
                                A.[GLPOSTDT] AS GLPOSTINGDATE,
                                A.[ORTRXAMT]
                      FROM      [dbo].[RM20101] A
                                INNER JOIN RM40401 B ON A.[RMDTYPAL] = B.[RMDTYPAL]
                      WHERE     [VOIDSTTS] = 0
                      UNION
                      SELECT    A.[CUSTNMBR],
                                A.[CPRCSTNM],
                                A.[DOCNUMBR],
                                A.[RMDTYPAL],
                                B.[DOCDESCR] AS DOCTYPE,
                                A.[DOCDATE],
                                A.[VOIDDATE] AS GLPOSTINGDATE,
                                A.[ORTRXAMT] * -1
                      FROM      [dbo].[RM20101] A
                                INNER JOIN [dbo].[RM40401] B ON A.[RMDTYPAL] = B.[RMDTYPAL]
                      WHERE     [VOIDSTTS] = 1
                      UNION
                      SELECT    A.[CUSTNMBR],
                                A.[CPRCSTNM],
                                A.[DOCNUMBR],
                                A.[RMDTYPAL],
                                B.[DOCDESCR] AS DOCTYPE,
                                A.[DOCDATE],
                                A.[GLPOSTDT] AS GLPOSTINGDATE,
                                A.[ORTRXAMT]
                      FROM      [dbo].[RM30101] A
                                INNER JOIN [dbo].[RM40401] B ON A.[RMDTYPAL] = B.[RMDTYPAL]
                      WHERE     [VOIDSTTS] = 0
                      UNION
                      SELECT    A.[CUSTNMBR],
                                A.[CPRCSTNM],
                                A.[DOCNUMBR],
                                A.[RMDTYPAL],
                                B.[DOCDESCR] AS DOCTYPE,
                                A.[DOCDATE],
                                A.[VOIDDATE] AS GLPOSTINGDATE,
                                A.[ORTRXAMT] * -1
                      FROM      [dbo].[RM30101] A
                                INNER JOIN [dbo].[RM40401] B ON A.[RMDTYPAL] = B.[RMDTYPAL]
                      WHERE     [VOIDSTTS] = 1
                    ) Z
          WHERE     Z.DOCDATE <= @ASOFDATE
        ) X

http://cvakumar.com/msdynamics/2010/11/07/receivables-hatb-report-using-gl-posting-date/

Receivables HATB Report (using GL Posting Date)

Today, I have decided to post this script which is generated by picking transactions based on the GL posting date.
This script can also be used as a base to reconcile the sub-ledger balances as of a certain date with the corresponding GL transactions. The script for generating general ledger balances for reconciliation is available here.

DECLARE @ASOFDATE DATETIME
 SET @ASOFDATE = '2017-04-12'
 SELECT  X.CUSTOMERNUMBER,
        X.CORPORATECUSTNUMBER,
        X.DOCUMENTNO,
        X.DOCUMENTTYPE,
        X.DOCUMENTDATE,
        X.GLPOSTINGDATE,
        X.DOCUMENTAMT,
        X.APPLIEDAMT,
        X.WRITEOFFAMT,
        X.DISCTAKENAMT,
        X.REALGAINLOSSAMT,
        ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT
          + X.REALGAINLOSSAMT ) AS CURRENTAMTFROM    ( SELECT    Z.CUSTNMBR AS CUSTOMERNUMBER,
                    Z.CPRCSTNM AS CORPORATECUSTNUMBER,
                    Z.DOCNUMBR AS DOCUMENTNO,
                    Z.DOCTYPE AS DOCUMENTTYPE,
                    Z.DOCDATE AS DOCUMENTDATE,
                    Z.GLPOSTINGDATE AS GLPOSTINGDATE,
                    Z.ORTRXAMT AS DOCUMENTAMT,
                    CASE WHEN Z.[RMDTYPAL] <= 6
                         THEN ISNULL(( SELECT   SUM(Y.[ActualApplyToAmount])
                                       FROM     ( SELECT    [CUSTNMBR],
                                                            [GLPOSTDT],
                                                            [ApplyFromGLPostDate],
                                                            [APTODCNM],
                                                            [APTODCTY],
                                                            [ActualApplyToAmount]
                                                  FROM      dbo.[RM20201]
                                                  UNION
                                                  SELECT    [CUSTNMBR],
                                                            [GLPOSTDT],
                                                            [ApplyFromGLPostDate],
                                                            [APTODCNM],
                                                            [APTODCTY],
                                                            [ActualApplyToAmount]
                                                  FROM      dbo.[RM30201]
                                                ) Y
                                       WHERE    Y.[GLPOSTDT] <= @ASOFDATE
                                                AND Y.[ApplyFromGLPostDate] <= @ASOFDATE
                                                AND Y.[CUSTNMBR] = Z.CUSTNMBR
                                                AND Y.[APTODCNM] = Z.DOCNUMBR
                                                AND Y.[APTODCTY] = Z.RMDTYPAL
                                     ), 0)
                         WHEN Z.[RMDTYPAL] > 7
                              AND Z.[RMDTYPAL] <= 9
                         THEN ISNULL(( SELECT   SUM(Y.[ActualApplyToAmount])
                                       FROM     ( SELECT    [CUSTNMBR],
                                                            [GLPOSTDT],
                                                            [ApplyToGLPostDate],
                                                            [APFRDCNM],
                                                            [APFRDCTY],
                                                            [ActualApplyToAmount]
                                                  FROM      dbo.[RM20201]
                                                  UNION
                                                  SELECT    [CUSTNMBR],
                                                            [GLPOSTDT],
                                                            [ApplyToGLPostDate],
                                                            [APFRDCNM],
                                                            [APFRDCTY],
                                                            [ActualApplyToAmount]
                                                  FROM      dbo.[RM30201]
                                                ) Y
                                       WHERE    Y.[GLPOSTDT] <= @ASOFDATE
                                                AND Y.[ApplyToGLPostDate] <= @ASOFDATE
                                                AND Y.[CUSTNMBR] = Z.CUSTNMBR
                                                AND Y.[APFRDCNM] = Z.DOCNUMBR
                                                AND Y.[APFRDCTY] = Z.RMDTYPAL
                                     ), 0)
                         ELSE 0
                    END AS APPLIEDAMT,
                    CASE WHEN Z.[RMDTYPAL] <= 6
                         THEN ISNULL(( SELECT   SUM(Y.[WROFAMNT])
                                       FROM     ( SELECT    [CUSTNMBR],
                                                            [GLPOSTDT],
                                                            [ApplyFromGLPostDate],
                                                            [APTODCNM],
                                                            [APTODCTY],
                                                            [WROFAMNT]
                                                  FROM      dbo.[RM20201]
                                                  UNION
                                                  SELECT    [CUSTNMBR],
                                                            [GLPOSTDT],
                                                            [ApplyFromGLPostDate],
                                                            [APTODCNM],
                                                            [APTODCTY],
                                                            [WROFAMNT]
                                                  FROM      dbo.[RM30201]
                                                ) Y
                                       WHERE    Y.[GLPOSTDT] <= @ASOFDATE
                                                AND Y.[ApplyFromGLPostDate] <= @ASOFDATE
                                                AND Y.[CUSTNMBR] = Z.CUSTNMBR
                                                AND Y.[APTODCNM] = Z.DOCNUMBR
                                                AND Y.[APTODCTY] = Z.RMDTYPAL
                                     ), 0)
                         ELSE 0
                    END AS WRITEOFFAMT,
                    CASE WHEN Z.[RMDTYPAL] <= 6
                         THEN ISNULL(( SELECT   SUM(Y.[DISTKNAM])
                                       FROM     ( SELECT    [CUSTNMBR],
                                                            [GLPOSTDT],
                                                            [ApplyFromGLPostDate],
                                                            [APTODCNM],
                                                            [APTODCTY],
                                                            [DISTKNAM]
                                                  FROM      dbo.[RM20201]
                                                  UNION
                                                  SELECT    [CUSTNMBR],
                                                            [GLPOSTDT],
                                                            [ApplyFromGLPostDate],
                                                            [APTODCNM],
                                                            [APTODCTY],
                                                            [DISTKNAM]
                                                  FROM      dbo.[RM30201]
                                                ) Y
                                       WHERE    Y.[GLPOSTDT] <= @ASOFDATE
                                                AND Y.[ApplyFromGLPostDate] <= @ASOFDATE
                                                AND Y.[CUSTNMBR] = Z.CUSTNMBR
                                                AND Y.[APTODCNM] = Z.DOCNUMBR
                                                AND Y.[APTODCTY] = Z.RMDTYPAL
                                     ), 0)
                         ELSE 0
                    END AS DISCTAKENAMT,
                    CASE WHEN Z.[RMDTYPAL] > 7
                              AND Z.[RMDTYPAL] <= 9
                         THEN ISNULL(( SELECT   SUM(Y.[RLGANLOS])
                                       FROM     ( SELECT    [CUSTNMBR],
                                                            [GLPOSTDT],
                                                            [ApplyToGLPostDate],
                                                            [APFRDCNM],
                                                            [APFRDCTY],
                                                            [RLGANLOS]
                                                  FROM      dbo.[RM20201]
                                                  UNION
                                                  SELECT    [CUSTNMBR],
                                                            [GLPOSTDT],
                                                            [ApplyToGLPostDate],
                                                            [APFRDCNM],
                                                            [APFRDCTY],
                                                            [RLGANLOS]
                                                  FROM      dbo.[RM30201]
                                                ) Y
                                       WHERE    Y.[GLPOSTDT] <= @ASOFDATE
                                                AND Y.[ApplyToGLPostDate] <= @ASOFDATE
                                                AND Y.[CUSTNMBR] = Z.CUSTNMBR
                                                AND Y.[APFRDCNM] = Z.DOCNUMBR
                                                AND Y.[APFRDCTY] = Z.RMDTYPAL
                                     ), 0)
                         ELSE 0
                    END AS REALGAINLOSSAMT
          FROM      ( SELECT    A.[CUSTNMBR],
                                A.[CPRCSTNM],
                                A.[DOCNUMBR],
                                A.[RMDTYPAL],
                                B.[DOCDESCR] AS DOCTYPE,
                                A.[DOCDATE],
                                A.[GLPOSTDT] AS GLPOSTINGDATE,
                                A.[ORTRXAMT]
                      FROM      [dbo].[RM20101] A
                                INNER JOIN RM40401 B ON A.[RMDTYPAL] = B.[RMDTYPAL]
                      WHERE     [VOIDSTTS] = 0
                      UNION
                      SELECT    A.[CUSTNMBR],
                                A.[CPRCSTNM],
                                A.[DOCNUMBR],
                                A.[RMDTYPAL],
                                B.[DOCDESCR] AS DOCTYPE,
                                A.[DOCDATE],
                                A.[VOIDDATE] AS GLPOSTINGDATE,
                                A.[ORTRXAMT] * -1
                      FROM      [dbo].[RM20101] A
                                INNER JOIN [dbo].[RM40401] B ON A.[RMDTYPAL] = B.[RMDTYPAL]
                      WHERE     [VOIDSTTS] = 1
                      UNION
                      SELECT    A.[CUSTNMBR],
                                A.[CPRCSTNM],
                                A.[DOCNUMBR],
                                A.[RMDTYPAL],
                                B.[DOCDESCR] AS DOCTYPE,
                                A.[DOCDATE],
                                A.[GLPOSTDT] AS GLPOSTINGDATE,
                                A.[ORTRXAMT]
                      FROM      [dbo].[RM30101] A
                                INNER JOIN [dbo].[RM40401] B ON A.[RMDTYPAL] = B.[RMDTYPAL]
                      WHERE     [VOIDSTTS] = 0
                      UNION
                      SELECT    A.[CUSTNMBR],
                                A.[CPRCSTNM],
                                A.[DOCNUMBR],
                                A.[RMDTYPAL],
                                B.[DOCDESCR] AS DOCTYPE,
                                A.[DOCDATE],
                                A.[VOIDDATE] AS GLPOSTINGDATE,
                                A.[ORTRXAMT] * -1
                      FROM      [dbo].[RM30101] A
                                INNER JOIN [dbo].[RM40401] B ON A.[RMDTYPAL] = B.[RMDTYPAL]
                      WHERE     [VOIDSTTS] = 1
                    ) Z
          WHERE     Z.GLPOSTINGDATE <= @ASOFDATE
        ) X

http://cvakumar.com/msdynamics/2010/11/07/receivables-hatb-report-using-gl-posting-date/