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
11/11/10
11/9/10
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/
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/
Subscribe to:
Posts (Atom)