Day: April 7, 2023

SQL Scripts for Microsoft Dynamics GP: Simple RMA Audit

Ian Grieve7 minute read0 comment

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script is another variation of the simple audit I created, but this time it audits for changes to the RMA tables.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
CREATE TABLE ut_AZRCRV_Audit
	(
		RecordType VARCHAR(100)
		,RecordID VARCHAR(100)
		,UpdateType VARCHAR(10)
		,Username varchar(150)
		,ChangeDateTime DateTime
		,OldData NVARCHAR(MAX)
		,NewData NVARCHAR(MAX)
	)
GO

CREATE TRIGGER utr_AZRCRV_SVC05000_AuditUpdate ON SVC05000 AFTER UPDATE AS
INSERT INTO
	ut_AZRCRV_Audit
--VALUES
	SELECT
		'SVC05000'
		,d.RETDOCID
		,'Update'
		,SYSTEM_USER
		,GETDATE()
		,'RATETPID = ' + CAST(RTRIM(d.RATETPID) AS VARCHAR(100)) + ' | ' + 'EXGTBLID = ' + CAST(RTRIM(d.EXGTBLID) AS VARCHAR(100)) + ' | ' + 'XCHGRATE = ' + CAST(RTRIM(d.XCHGRATE) AS VARCHAR(100)) + ' | ' + 'EXCHDATE = ' + CAST(RTRIM(d.EXCHDATE) AS VARCHAR(100))
		,'RATETPID = ' + CAST(RTRIM(i.RATETPID) AS VARCHAR(100)) + ' | ' + 'EXGTBLID = ' + CAST(RTRIM(i.EXGTBLID) AS VARCHAR(100)) + ' | ' + 'XCHGRATE = ' + CAST(RTRIM(i.XCHGRATE) AS VARCHAR(100)) + ' | ' + 'EXCHDATE = ' + CAST(RTRIM(i.EXCHDATE) AS VARCHAR(100))
	FROM
		deleted d
	LEFT JOIN
		inserted i
			ON
				i.RETDOCID = d.RETDOCID
GO

CREATE TRIGGER utr_AZRCRV_SVC05000_AuditInsert on SVC05000 AFTER INSERT AS
INSERT INTO
	ut_AZRCRV_Audit
--VALUES
	SELECT
		'SVC05000'
		,i.RETDOCID
		,'Insert'
		,SYSTEM_USER
		,GETDATE()
		,''
		,'RATETPID = ' + CAST(RTRIM(i.RATETPID) AS VARCHAR(100)) + ' | ' + 'EXGTBLID = ' + CAST(RTRIM(i.EXGTBLID) AS VARCHAR(100)) + ' | ' + 'XCHGRATE = ' + CAST(RTRIM(i.XCHGRATE) AS VARCHAR(100)) + ' | ' + 'EXCHDATE = ' + CAST(RTRIM(i.EXCHDATE) AS VARCHAR(100))
	FROM
		inserted i
GO

CREATE TRIGGER utr_AZRCRV_SVC05200_AuditUpdate ON SVC05200 AFTER UPDATE AS
INSERT INTO
	ut_AZRCRV_Audit
--VALUES
	SELECT
		'SVC05200'
		,CAST(d.RETDOCID AS VARCHAR(15)) + ' | ' + CAST(d.LNSEQNBR AS VARCHAR(20))
		,'Update'
		,SYSTEM_USER
		,GETDATE()
		,'UNITPRCE = ' + CAST(RTRIM(d.UNITPRCE) AS VARCHAR(100)) + ' | ' + 'ORUNTPRC = ' + CAST(RTRIM(d.ORUNTPRC) AS VARCHAR(100)) + ' | ' + 'XTNDPRCE = ' + CAST(RTRIM(d.XTNDPRCE) AS VARCHAR(100)) + ' | ' + 'OXTNDPRC = ' + CAST(RTRIM(d.OXTNDPRC) AS VARCHAR(100)) + ' | ' + 'Originating_Return_Price = ' + CAST(RTRIM(d.Originating_Return_Price) AS VARCHAR(100)) + ' | ' + 'SVC_Extended_Return_Pric = ' + CAST(RTRIM(d.SVC_Extended_Return_Pric) AS VARCHAR(100)) + ' | ' + 'SVC_Orig_Ext_Return_Pric = ' + CAST(RTRIM(d.SVC_Orig_Ext_Return_Pric) AS VARCHAR(100))
		,'UNITPRCE = ' + CAST(RTRIM(i.UNITPRCE) AS VARCHAR(100)) + ' | ' + 'ORUNTPRC = ' + CAST(RTRIM(i.ORUNTPRC) AS VARCHAR(100)) + ' | ' + 'XTNDPRCE = ' + CAST(RTRIM(i.XTNDPRCE) AS VARCHAR(100)) + ' | ' + 'OXTNDPRC = ' + CAST(RTRIM(i.OXTNDPRC) AS VARCHAR(100)) + ' | ' + 'Originating_Return_Price = ' + CAST(RTRIM(i.Originating_Return_Price) AS VARCHAR(100)) + ' | ' + 'SVC_Extended_Return_Pric = ' + CAST(RTRIM(i.SVC_Extended_Return_Pric) AS VARCHAR(100)) + ' | ' + 'SVC_Orig_Ext_Return_Pric = ' + CAST(RTRIM(i.SVC_Orig_Ext_Return_Pric) AS VARCHAR(100))
	FROM
		deleted d
	LEFT JOIN
		inserted i
			ON
				i.RETDOCID = d.RETDOCID
			AND
				i.LNSEQNBR = d.LNSEQNBR
GO

CREATE TRIGGER utr_AZRCRV_SVC05200_AuditInsert on SVC05200 AFTER INSERT AS
INSERT INTO
	ut_AZRCRV_Audit
--VALUES
	SELECT
		'SVC05200'
		,CAST(RETDOCID AS VARCHAR(15)) + ' | ' + CAST(LNSEQNBR AS VARCHAR(20))
		,'Insert'
		,SYSTEM_USER
		,GETDATE()
		,''
		,'UNITPRCE = ' + CAST(RTRIM(i.UNITPRCE) AS VARCHAR(100)) + ' | ' + 'ORUNTPRC = ' + CAST(RTRIM(i.ORUNTPRC) AS VARCHAR(100)) + ' | ' + 'XTNDPRCE = ' + CAST(RTRIM(i.XTNDPRCE) AS VARCHAR(100)) + ' | ' + 'OXTNDPRC = ' + CAST(RTRIM(i.OXTNDPRC) AS VARCHAR(100)) + ' | ' + 'Originating_Return_Price = ' + CAST(RTRIM(i.Originating_Return_Price) AS VARCHAR(100)) + ' | ' + 'SVC_Extended_Return_Pric = ' + CAST(RTRIM(i.SVC_Extended_Return_Pric) AS VARCHAR(100)) + ' | ' + 'SVC_Orig_Ext_Return_Pric = ' + CAST(RTRIM(i.SVC_Orig_Ext_Return_Pric) AS VARCHAR(100))
	FROM
		inserted i
GO

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

In Microsoft Dynamics 365 Business Central (Administration), how do I… Use Worksheet Pages

Ian Grieve3 minute read0 comment

Microsoft Dynamics 365 Business CentralThis post is part of the In Microsoft Dynamics 365 Business Central (Administration), how do I… series and of the wider In Microsoft Dynamics 365 Business Central, how do I… series which I am posting as I familiarise myself with Microsoft Dynamics 365 Business Central.

In an earlier article in this series, I introduced the different types of pages used within Dynamics BC. I thought it might be useful to give a run through of how to use each of the pages; in this post, I’m going to take a look at worksheet pages.

Like document pages, worksheet pages are transaction pages. However, they differ from document pages in that they can hold multiple transactions rather than just a single one.

Due to this difference, a worksheet page is structured differently. They consist of a small number of header fields, such as for a batch number, along with a list page style section for entering multiple data rows. This is sometimes followed by a section containing either additional data fields or totals.

There are many worksheet pages in Dynamics BC, including, but not limited to, general journals, price, pick and put-away worksheets.

An example of a worksheet page is the one for General Journals:

General Journal worksheet page

Continue reading “In Microsoft Dynamics 365 Business Central (Administration), how do I… Use Worksheet Pages”