Dynamics GP Reporting Series: Sales Invoice (Receivables Management)

For Sales invoices that does not contains items, you might need to print invoice using Transactions>> Sales>> Transaction Entry, the good thing is, MS guys already created view for the Receivables Transactions, below the SQL command needed:

SQL Command:

SELECT    
dbo.RM10301.DOCTYPE, dbo.RM10301.DOCDATE,
dbo.RM00102.ADDRESS1, dbo.RM00102.ADDRESS2,
dbo.RM00102.ADDRESS3, dbo.RM00102.CITY,
dbo.RM10301.DOCDESCR, dbo.RM10301.CUSTNMBR,
dbo.RM10301.CUSTNAME, dbo.RM10301.SLPRSNID,
dbo.RM10301.SHIPMTHD, dbo.RM10301.PYMTRMID,
dbo.RM10301.DOCAMNT, dbo.RM10301.SLSAMNT,
dbo.RM10301.MISCAMNT, dbo.RM10301.TAXAMNT,
dbo.RM10301.FRTAMNT, dbo.RM10301.TRDISAMT,
dbo.RM10301.CASHAMNT, dbo.RM10301.CHEKAMNT,
dbo.RM10301.CRCRDAMT, dbo.RM10301.CURNCYID,
dbo.RM10301.DOCNUMBR, dbo.ReceivablesTransactions.[Originating Cash Amount],
dbo.ReceivablesTransactions.[Originating Check Amount],
dbo.ReceivablesTransactions.[Originating Credit Card Amount],
dbo.ReceivablesTransactions.[Originating Current Trx Amount],
dbo.ReceivablesTransactions.[Originating Discount Taken Amount],
dbo.ReceivablesTransactions.[Originating Freight Amount],
dbo.ReceivablesTransactions.[Originating Misc Amount],
dbo.ReceivablesTransactions.[Originating Sales Amount],
dbo.ReceivablesTransactions.[Originating Tax Amount],
dbo.ReceivablesTransactions.[Originating Trade Discount Amount],
dbo.ReceivablesTransactions.[Originating Write Off Amount],
dbo.ReceivablesTransactions.[Currency ID]
FROM         dbo.RM10301
LEFT OUTER JOIN dbo.ReceivablesTransactions
ON dbo.RM10301.DOCNUMBR = dbo.ReceivablesTransactions.[Document Number]
LEFT OUTER JOIN dbo.RM00102 ON dbo.RM10301.CUSTNMBR = dbo.RM00102.CUSTNMBR
AND dbo.RM10301.ADRSCODE = dbo.RM00102.ADRSCODE
WHERE dbo.RM10301.DOCNUMBR = {?DOCNUMBER}

As all other reports, to avoid any issues when creating our crystal report, we’ll need to add the above statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the original Sales Invoice:

Crystal Report Design:

image

Few optimizations are still required to include calculations and formulas to get the report in the needed format.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Database Monitoring (Transactions Operations Log)

Have you ever wanted to monitor one of your tables transaction for non GP tables that couldn’t be monitored by Activity Tracking? I been in a situation where the customer requested to log ALL transactions performed on a third party software, to achieve this you will have two options, one to use SQL Server Profiler to trace your data based on predefined filters and direct the results to an SQL table, which will be stopped if the profiler closed, or your other option is to use SQL triggers to perform insert operation on a monitoring table I called “TransactionsLog”:

image

Now the tough part is to create triggers on your tables, each table will need to have 3 triggers, one to monitor “Insert”, and one to monitor “Update” and the last one to monitor “Delete”.

Having a big number of tables will make such task a nightmare! Where I had to write the script generator below that generates those triggers for you! It writes you the needed triggers including the primary key field –if it was single – along with the timestamp of the transaction, below the complete script:

/*Transactions Log Table Creation:

Created By: Mohammad R. Daoud

http://mohdaoud.blogspot.com

April 26, 2010

Make sure to have a complete backup on the databases you are running this script on, in addition, running those scripts is your own risk.

*/

 

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TransactionsLog]’) AND type in (N’U’))
DROP TABLE [dbo].[TransactionsLog]
GO

CREATE TABLE [dbo].[TransactionsLog](
    [TransactionID] [int] NOT NULL,
    [Type] [varchar](500) NOT NULL CONSTRAINT [DF_LogTable_Type]  DEFAULT (”),
    [TrxDateTime] [datetime] NOT NULL CONSTRAINT [DF_LogTable_TrxDateTime]  DEFAULT (”),
    [TrxDescription] [varchar](5000) NOT NULL CONSTRAINT [DF_LogTable_TrxDescription]  DEFAULT (”),
    [Notes] [varchar](500) NOT NULL CONSTRAINT [DF_LogTable_Notes]  DEFAULT (”),
    [TrxNumber] [int] NOT NULL,
    [RowID] [int] IDENTITY(1,1) NOT NULL,
    [RowUser] [varchar](500) NOT NULL CONSTRAINT [DF_LogTable_RowUser]  DEFAULT (suser_sname()),
    [RowDate] [datetime] NOT NULL CONSTRAINT [DF_LogTable_RowDate]  DEFAULT (getdate()),
CONSTRAINT [PK_LogTable] PRIMARY KEY CLUSTERED
(
    [TransactionID] ASC
))
GO

 

/* Starting Script */

DECLARE @Operation varchar(500)
DECLARE    @TableName varchar(128)
DECLARE @PrimaryKey varchar(500)

DECLARE TriggersCur CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME = ‘GL00100’ OR TABLE_NAME = ‘GL00105’

 

/*Remove Above Filter To Monitor the complete Database, note that this will create triggers on all your database tables, please make sure to have a FULL DATABASE Backup before adding the triggers */

OPEN TriggersCur
FETCH NEXT FROM TriggersCur INTO @TableName

WHILE @@FETCH_Status = 0
BEGIN
SELECT    @PrimaryKey = c.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,        INFORMATION_SCHEMA.KEY_COLUMN_USAGE c WHERE pk.TABLE_NAME = @TableName AND CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND c.TABLE_NAME = pk.TABLE_NAME AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
SET @Operation = ‘INSERT Operation’
PRINT ‘ IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N”[dbo].[‘ + @TableName + ‘INSERT]”))’
PRINT ‘ DROP TRIGGER [dbo].[‘ + @TableName + ‘INSERT]’
PRINT ‘ GO ‘

PRINT ‘ CREATE TRIGGER ‘ + @TableName + ‘INSERT ON ‘ + @TableName + ‘ FOR INSERT AS’
PRINT ‘ DECLARE @LOGID INT’
PRINT ‘ DECLARE @TransactionID INT’
PRINT ‘ SELECT @TransactionID = ‘ + @PrimaryKey + ‘ FROM INSERTED’
PRINT ‘ SELECT @LOGID = ISNULL(MAX(TransactionID),0) + 1 FROM TransactionsLog’
PRINT ‘ INSERT INTO [TransactionsLog]’
PRINT ‘            ([TransactionID]’
PRINT ‘            ,[Type]’
PRINT ‘            ,[TrxDateTime]’
PRINT ‘            ,[TrxDescription]’
PRINT ‘            ,[Notes]’
PRINT ‘            ,[TrxNumber])’
PRINT ‘      VALUES’
PRINT ‘            (@LogID ‘
PRINT ‘            ,”’ + @Operation + ””
PRINT ‘            ,”’ + CONVERT(VARCHAR(500), GETDATE()) + ””
PRINT ‘            ,”’+ @TableName + ””
PRINT ‘            ,”’+ ””
PRINT ‘            , CONVERT(VARCHAR(500), @TransactionID) )’
PRINT ‘ GO ‘

SET @Operation = ‘UPDATE Operation’

PRINT ‘ IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N”[dbo].[‘ + @TableName + ‘UPDATE]”))’
PRINT ‘ DROP TRIGGER [dbo].[‘ + @TableName + ‘UPDATE]’
PRINT ‘ GO ‘

PRINT ‘ CREATE TRIGGER ‘ + @TableName + ‘UPDATE ON ‘ + @TableName + ‘ FOR UPDATE AS’
PRINT ‘ DECLARE @LOGID INT’
PRINT ‘ DECLARE @TransactionID INT’
PRINT ‘ SELECT @TransactionID = ‘ + @PrimaryKey + ‘ FROM INSERTED’
PRINT ‘ SELECT @LOGID = ISNULL(MAX(TransactionID),0) + 1 FROM TransactionsLog’
PRINT ‘ INSERT INTO [TransactionsLog]’
PRINT ‘            ([TransactionID]’
PRINT ‘            ,[Type]’
PRINT ‘            ,[TrxDateTime]’
PRINT ‘            ,[TrxDescription]’
PRINT ‘            ,[Notes]’
PRINT ‘            ,[TrxNumber])’
PRINT ‘      VALUES’
PRINT ‘            (@LogID ‘
PRINT ‘            ,”’ + @Operation + ””
PRINT ‘            ,”’ + CONVERT(VARCHAR(500), GETDATE()) + ””
PRINT ‘            ,”’+ @TableName + ””
PRINT ‘            ,”’+ ””
PRINT ‘            , CONVERT(VARCHAR(500), @TransactionID) )’
PRINT ‘ GO ‘

SET @Operation = ‘DELETE Operation’

PRINT ‘ IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N”[dbo].[‘ + @TableName + ‘DELETE]”))’
PRINT ‘ DROP TRIGGER [dbo].[‘ + @TableName + ‘DELETE]’
PRINT ‘ GO ‘

PRINT ‘ CREATE TRIGGER ‘ + @TableName + ‘DELETE ON ‘ + @TableName + ‘ FOR DELETE AS’
PRINT ‘ DECLARE @LOGID INT’
PRINT ‘ DECLARE @TransactionID INT’
PRINT ‘ SELECT @TransactionID = ‘ + @PrimaryKey + ‘ FROM DELETED’
PRINT ‘ SELECT @LOGID = ISNULL(MAX(TransactionID),0) + 1 FROM TransactionsLog’
PRINT ‘ INSERT INTO [TransactionsLog]’
PRINT ‘            ([TransactionID]’
PRINT ‘            ,[Type]’
PRINT ‘            ,[TrxDateTime]’
PRINT ‘            ,[TrxDescription]’
PRINT ‘            ,[Notes]’
PRINT ‘            ,[TrxNumber])’
PRINT ‘      VALUES’
PRINT ‘            (@LogID ‘
PRINT ‘            ,”’ + @Operation + ””
PRINT ‘            ,”’ + CONVERT(VARCHAR(500), GETDATE()) + ””
PRINT ‘            ,”’+ @TableName + ””
PRINT ‘            ,”’+ ””
PRINT ‘            , CONVERT(VARCHAR(500), @TransactionID) )’
PRINT ‘ GO ‘

FETCH NEXT FROM TriggersCur INTO @TableName
END
CLOSE TriggersCur
DEALLOCATE TriggersCur

Hope that this helps!

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Dynamics GP Reporting Series: SOP Sales Invoice

Moving from the financial series into the Sales, sales invoice will need to be sent to the customer, where it has to be designed to represent company image, I have used design similar to the one come with GP, below the SQL command needed:

SQL Command:

SELECT    
dbo.SOP10100.SOPNUMBE, dbo.SOP10100.DOCDATE,
dbo.SOP10100.PYMTRMID, dbo.SOP10100.CUSTNMBR,
dbo.SOP10100.CUSTNAME, dbo.SOP10100.CSTPONBR,
dbo.SOP10100.SHIPMTHD, dbo.SOP10100.SLPRSNID,
dbo.SOP10200.ITEMNMBR, dbo.SOP10200.ITEMDESC,
dbo.SOP10200.QTYORDER, dbo.SOP10200.QTYTBAOR,
dbo.SOP10200.QTYTOINV, dbo.SOP10100.CURNCYID,
dbo.SOP10100.CURRNIDX, dbo.SOP10200.UOFM,
dbo.SOP10100.MSTRNUMB, dbo.SOP10106.USRTAB01,
dbo.SOP10106.USERDEF2, dbo.SOP10106.USRDEF03,
RM00102_1.ADDRESS1, RM00102_1.ADDRESS2,
RM00102_1.ADDRESS3, dbo.SOP10100.ORTDISAM,
dbo.SOP10100.ORSUBTOT, dbo.SOP10100.ORFRTAMT,
dbo.SOP10100.ORMISCAMT, dbo.SOP10100.ORTAXAMT,
dbo.SOP10100.ORDOCAMT, dbo.SOP10200.ORUNTPRC,
dbo.SOP10200.OXTNDPRC, dbo.SOP10200.ORMRKDAM,
dbo.SOP10200.CMPNTSEQ
FROM         dbo.SOP10100
INNER JOIN dbo.SOP10200 ON dbo.SOP10100.SOPTYPE = dbo.SOP10200.SOPTYPE
AND dbo.SOP10100.SOPNUMBE = dbo.SOP10200.SOPNUMBE
AND dbo.SOP10100.TRXSORCE = dbo.SOP10200.TRXSORCE
LEFT OUTER JOIN dbo.SOP10106 ON dbo.SOP10100.SOPTYPE = dbo.SOP10106.SOPTYPE
AND dbo.SOP10100.SOPNUMBE = dbo.SOP10106.SOPNUMBE
LEFT OUTER JOIN dbo.RM00102 RM00102_1 ON dbo.SOP10100.PRSTADCD = RM00102_1.ADRSCODE
AND dbo.SOP10100.CUSTNMBR = RM00102_1.CUSTNMBR
WHERE dbo.SOP10100.SOPNUMBE = {?SOPNumber}

As all other reports, to avoid any issues when creating our crystal report, we’ll need to add the above statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the original SOP Sales Invoice:

Crystal Report Design:

image

Few optimizations are still required to include calculations and formulas to get the report in the needed format.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Dynamics GP Reporting Series: Bank Transfers

As well as the bank transactions, bank transfers does not have “Save” operation as well, it directly posts the transaction to your checkbook, where we’ll need to get our report printed after the post operation, below the SQL command needed:

SQL Command:

SELECT    
dbo.CM20600.CMXFRNUM AS CMTrxNum, dbo.CM20600.CMCHKBKID,
dbo.CM20600.CMFRMCHKBKID, dbo.CM20600.CMXFTDATE,
dbo.CM20100.AUDITTRAIL, dbo.CM20200.DSCRIPTN,
dbo.CM20200.POSTEDDT, dbo.CM20200.Xfr_Record_Number,
dbo.CM20400.DistRef, dbo.GL00105.ACTNUMST,
dbo.GL00100.ACTDESCR, dbo.CM20200.ORIGAMT,
dbo.CM20400.ORCRDAMT, dbo.CM20400.ORDBTAMT,
dbo.CM20200.CURNCYID
FROM         dbo.CM20400
INNER JOIN dbo.CM20200 ON dbo.CM20400.CMDNUMWK = dbo.CM20200.CMRECNUM
INNER JOIN dbo.GL00100 ON dbo.CM20400.ACTINDX = dbo.GL00100.ACTINDX
INNER JOIN dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX
INNER JOIN dbo.CM20600
INNER JOIN dbo.CM20100 ON dbo.CM20600.CMXFRNUM = dbo.CM20100.CMTrxNum
ON dbo.CM20200.CMRECNUM = dbo.CM20100.CMDNUMWK
WHERE     (dbo.CM20200.CMTrxType = 7)
AND dbo.CM20100.CMTrxNum = {?CMTRXNUM}

To avoid any issues when creating our crystal report, we’ll need to add the above statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the original Bank Transfer Posting Journal:

Crystal Report Design:

image

Few optimizations are still required to include calculations and formulas to get the report in the needed format.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Microsoft Dynamics GP 2010 will be ready for customers starting May 1st!

As taken from Inside Microsoft Dynamics GP blog, get the post here, or visit the press release here.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Dynamics GP Reporting Series: Bank Transactions

As you know, bank transactions does not have “Save” operation, it directly posts the transaction to your checkbook, where we’ll need to get our report printed after the post operation, below the SQL command needed:

SQL Command: 

SELECT

dbo.CM20200.TRXDATE, dbo.CM20200.GLPOSTDT,

dbo.CM20200.paidtorcvdfrom, dbo.CM20200.DSCRIPTN,

dbo.CM20100.AUDITTRAIL, dbo.CM20400.ACTINDX,

dbo.GL00100.ACTDESCR, dbo.GL00105.ACTNUMST,

dbo.CM40101.DOCTYNAM, dbo.CM20100.CMTrxNum,

dbo.CM20100.CMTrxType, dbo.CM20100.CHEKBKID,

dbo.CM20200.CURNCYID, dbo.CM20400.ORCRDAMT,

dbo.CM20400.ORDBTAMT, dbo.CM20200.ORIGAMT

FROM

dbo.CM20100 INNER JOIN dbo.CM20400

ON dbo.CM20100.CMDNUMWK = dbo.CM20400.CMDNUMWK

INNER JOIN dbo.GL00100 ON dbo.CM20400.ACTINDX = dbo.GL00100.ACTINDX

INNER JOIN dbo.GL00105 ON dbo.CM20400.ACTINDX = dbo.GL00105.ACTINDX

INNER JOIN dbo.CM20200 ON dbo.CM20100.CMTrxNum = dbo.CM20200.CMTrxNum

AND dbo.CM20100.CHEKBKID = dbo.CM20200.CHEKBKID

AND dbo.CM20100.CMTrxType = dbo.CM20200.CMTrxType

INNER JOIN dbo.CM40101 ON dbo.CM20100.CMTrxType = dbo.CM40101.CMTrxType

Where (dbo.CM20100.VOIDED 1) AND (dbo.CM20400.VOIDED 1) AND dbo.CM20100.CMTrxNum = {?CMTRXNUM}

To avoid any issues when creating our crystal report, we’ll need to add the above statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the original Bank Transaction Posting Journal:

Crystal Report Design:

image

Few optimizations are still required to include calculations and formulas to get the report in the needed format.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Integration Manager Scripts without hard coding the Database Name

Good method by Touchstone blog, to write SQL Scripts within your Microsoft Dynamics GP Integration and perform some database operations without having to hard code the database name.

Checkout the article here.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com