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/
Showing posts with label GL. Show all posts
Showing posts with label GL. Show all posts
11/9/10
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/
GL Reconciliation SQL Script – Receivables
This script can be used as a base to reconcile the receivables sub-ledger balances as of a certain date.
DECLARE @ASOFDATE AS DATETIME
SET @ASOFDATE = '2017-04-12'
SELECT Z.*FROM ( SELECT A.[OPENYEAR] TRXYEAR,
A.[ORTRXSRC] AS ARAUDITTRAIL,
A.[ORMSTRID] AS ARCUSTOMERNO,
A.[ORCTRNUM] AS ARDOCUMENTNO,
A.[ORTRXTYP] AS ARDOCUMENTTYPE,
A.[TRXDATE] AS TRANSACTIONDATE,
RTRIM(B.[ACTNUMST]) AS GPACCOUNTNO,
SUM([DEBITAMT] - [CRDTAMNT]) AS GLAMOUNT
FROM [dbo].[GL20000] A
INNER JOIN GL00105 B ON A.[ACTINDX] = B.[ACTINDX]
WHERE A.[ACTINDX] IN ( SELECT [RMARACC]
FROM [dbo].[RM00101] )
GROUP BY A.[OPENYEAR],
A.[ORTRXSRC],
A.[ORMSTRID],
A.[ORCTRNUM],
A.[ORTRXTYP],
A.[TRXDATE],
B.[ACTNUMST]
UNION
SELECT A.[HSTYEAR] TRXYEAR,
A.[ORTRXSRC] AS ARAUDITTRAIL,
A.[ORMSTRID] AS ARCUSTOMERNO,
A.[ORCTRNUM] AS ARDOCUMENTNO,
A.[ORTRXTYP] AS ARDOCUMENTTYPE,
A.[TRXDATE] AS TRANSACTIONDATE,
RTRIM(B.[ACTNUMST]) AS GPACCOUNTNO,
SUM([DEBITAMT] - [CRDTAMNT]) AS GLAMOUNT
FROM [dbo].[GL30000] A
INNER JOIN GL00105 B ON A.[ACTINDX] = B.[ACTINDX]
WHERE A.[ACTINDX] IN ( SELECT [RMARACC]
FROM [dbo].[RM00101] )
GROUP BY A.[HSTYEAR],
A.[ORTRXSRC],
A.[ORMSTRID],
A.[ORCTRNUM],
A.[ORTRXTYP],
A.[TRXDATE],
B.[ACTNUMST]
) ZWHERE Z.[TRANSACTIONDATE] <= @ASOFDATE
http://cvakumar.com/msdynamics/2010/11/07/gl-reconciliation-sql-script-receivables/
Subscribe to:
Comments (Atom)