SQL View to Return Sales Invoice Lines

Microsoft Dynamics GPI've been organising a demo of software which can be used to produce documentation using data from Microsoft Dynamics GP and integrate it with data from other systems or network shares. I thought about a few ways of getting the Dynamics GP data and decided that a custom SQL view returning only the required information was the best way to go.

This view returns sales invoice lines and related fields such as the sales user defined.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_SalesInvoiceLines', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesInvoiceLines
GO
-- create view
CREATE VIEW [dbo].[uv_AZRCRV_SalesInvoiceLines] AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
SELECT RTRIM(['Sales Transaction Work'].SOPNUMBE) AS 'Invoice Number' ,['Sales Transaction Amounts Work'].LNITMSEQ AS 'Line Number' ,RTRIM(ISNULL(['Sales Customer Item Cross Reference'].CUSTITEMNMBR, ['Sales Transaction Amounts Work'].ITEMNMBR)) AS 'Item Number' ,RTRIM(ISNULL(['Sales Customer Item Cross Reference'].CUSTITEMDESC, ['Sales Transaction Amounts Work'].ITEMDESC)) AS 'Item Description' ,RTRIM(['Item Master'].TCC) AS 'Tax Commodity Code' ,'UK' AS 'Country of Origin' ,RTRIM(['Sales Transaction Amounts Work'].UOFM) AS 'Unit of Measure' ,['Sales Transaction Amounts Work'].QUANTITY AS 'Quantity' ,CONVERT(DECIMAL(10,2),['Sales Transaction Amounts Work'].ORUNTPRC) AS 'Originating Unit Price' ,CONVERT(DECIMAL(10,2),['Sales Transaction Amounts Work'].OREXTCST) AS 'Originating Extended Price' ,RTRIM(['Sales Transaction Amounts Work'].LOCNCODE) AS 'Site' FROM SOP10100 AS ['Sales Transaction Work'] -- Sales Transaction Work (SOP10100) INNER JOIN SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200) ON ['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE AND ['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE INNER JOIN IV00101 AS ['Item Master'] -- Item Master (IV00101) ON ['Item Master'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR LEFT JOIN SOP60300 AS ['Sales Customer Item Cross Reference'] -- Sales Customer Item Cross Reference (SOP60300) ON ['Sales Customer Item Cross Reference'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR AND ['Sales Customer Item Cross Reference'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR WHERE ['Sales Transaction Amounts Work'].SOPTYPE = 3 GO GRANT SELECT ON uv_AZRCRV_SalesInvoiceLines TO DYNGRP GO

[/postcode]

SQL View to Return Sales Invoice Headers

Microsoft Dynamics GPI've been organising a demo of software which can be used to produce documentation using data from Microsoft Dynamics GP and integrate it with data from other systems or network shares. I thought about a few ways of getting the Dynamics GP data and decided that a custom SQL view returning only the required information was the best way to go.

This view returns sales invoice headers and related fields such as the sales user defined.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_SalesInvoiceHeaders', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesInvoiceHeaders
GO
-- create view
CREATE VIEW [dbo].[uv_AZRCRV_SalesInvoiceHeaders] AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
SELECT RTRIM(['Sales Transaction Work'].SOPNUMBE) AS 'Invoice Number' ,['Sales Transaction Work'].DOCID AS 'Invoice Type' ,FORMAT(['Sales Transaction Work'].DOCDATE, 'dd-MM-yyyy') AS 'Document Date' ,FORMAT(['Sales Transaction Work'].INVODATE, 'dd-MM-yyyy') AS 'Invoice Date' ,FORMAT(['Sales Transaction Work'].INVODATE, 'yyyy-MM-dd') AS 'Invoice Date Sortable' ,FORMAT(['Sales Transaction Work'].DUEDATE, 'dd-MM-yyyy') AS 'Due Date' ,FORMAT(['Sales Transaction Work'].ReqShipDate, 'dd-MM-yyyy') AS 'Requested Ship Date' ,RTRIM(['Company Master'].ADRCNTCT) AS 'Shipper Contact Person' ,RTRIM(['Company Master'].ADDRESS1) AS 'Shipper Address 1' ,RTRIM(['Company Master'].ADDRESS2) AS 'Shipper Address 2' ,RTRIM(['Company Master'].ADDRESS3) AS 'Shipper Address 3' ,RTRIM(['Company Master'].CITY) AS 'Shipper City' ,RTRIM(['Company Master'].STATE) AS 'Shipper State' ,RTRIM(['Company Master'].ZIPCODE) AS 'Shipper Zip Code' ,RTRIM(['Company Master'].COUNTY) AS 'Shipper Country' ,RTRIM(['Company Master'].PHONE1) AS 'Shipper Phone 1' ,RTRIM(['Sales Transaction Work'].CUSTNMBR) AS 'Customer Number' ,RTRIM(['Sales Transaction Work'].CUSTNAME) AS 'Customer Name' ,RTRIM(['RM Customer Master'].TXRGNNUM) AS 'Tax Registration Number' ,RTRIM(['Sales Transaction Work'].PRSTADCD) AS 'Ship To Address Code' ,RTRIM(['Sales Transaction Work'].CNTCPRSN) AS 'Contact Person' ,RTRIM(['Sales Transaction Work'].ShipToName) AS 'Ship To Name' ,RTRIM(['Sales Transaction Work'].ADDRESS1) AS 'Address 1' ,RTRIM(['Sales Transaction Work'].ADDRESS2) AS 'Address 2' ,RTRIM(['Sales Transaction Work'].ADDRESS3) AS 'Address 3' ,RTRIM(['Sales Transaction Work'].CITY) AS 'City' ,RTRIM(['Sales Transaction Work'].STATE) AS 'State' ,RTRIM(['Sales Transaction Work'].ZIPCODE) AS 'Zip Code' ,RTRIM(['Sales Transaction Work'].COUNTRY) AS 'Country' ,RTRIM(['Sales Transaction Work'].PHNUMBR1) AS 'Phone 1' ,RTRIM(['Sales Transaction Work'].CURNCYID) AS 'Currency ID' ,( SELECT CONVERT(DECIMAL(10,0), SUM(QUANTITY)) FROM SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200) WHERE ['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE AND ['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE ) AS 'Number Of Pieces' ,( SELECT CONVERT(DECIMAL(10,2), SUM(QUANTITY) * SUM(['Item Master'].ITEMSHWT)) FROM SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200) INNER JOIN IV00101 AS ['Item Master'] -- Item Master (IV00101) ON ['Item Master'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR WHERE ['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE AND ['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE ) AS 'Total Weight' ,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].SUBTOTAL) AS 'Sub Total' ,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].TAXAMNT) AS 'Tax Amount' ,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].FRTAMNT) AS 'Freight Amount' ,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].DOCAMNT) AS 'Document Amount' FROM SOP10100 AS ['Sales Transaction Work'] -- Sales Transaction Work (SOP10100) LEFT JOIN RM00101 AS ['RM Customer Master'] -- RM Customer MSTR (RM00101) ON ['RM Customer Master'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR LEFT JOIN DYNAMICS..SY01500 AS ['Company Master'] -- Company Master (SY01500) ON ['Company Master'].INTERID = DB_NAME() WHERE ['Sales Transaction Work'].SOPTYPE = 3 GO GRANT SELECT ON uv_AZRCRV_SalesInvoiceHeaders TO DYNGRP GO

[/postcode]

SQL View to Return Sales Quote Lines

Microsoft Dynamics GPI've been organising a demo of software which can be used to produce documentation using data from Microsoft Dynamics GP and integrate it with data from other systems or network shares. I thought about a few ways of getting the Dynamics GP data and decided that a custom SQL view returning only the required information was the best way to go.

This view returns sales quote lines and related fields such as the sales user defined.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_SalesQuoteLines', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesQuoteLines
GO
-- create view
CREATE VIEW [dbo].[uv_AZRCRV_SalesQuoteLines] AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
SELECT RTRIM(['Sales Transaction Work'].SOPNUMBE) AS 'Quote Number' ,['Sales Transaction Amounts Work'].LNITMSEQ AS 'Line Number' ,RTRIM(ISNULL(['Sales Customer Item Cross Reference'].CUSTITEMNMBR, ['Sales Transaction Amounts Work'].ITEMNMBR)) AS 'Item Number' ,RTRIM(ISNULL(['Sales Customer Item Cross Reference'].CUSTITEMDESC, ['Sales Transaction Amounts Work'].ITEMDESC)) AS 'Item Description' ,RTRIM(['Sales Transaction Amounts Work'].UOFM) AS 'Unit of Measure' ,['Sales Transaction Amounts Work'].QUANTITY AS 'Quantity' ,CONVERT(DECIMAL(10,2),['Sales Transaction Amounts Work'].ORUNTPRC) AS 'Originating Unit Price' ,CONVERT(DECIMAL(10,2),['Sales Transaction Amounts Work'].OREXTCST) AS 'Originating Extended Price' ,RTRIM(['Sales Transaction Amounts Work'].LOCNCODE) AS 'Site' FROM SOP10100 AS ['Sales Transaction Work'] -- Sales Transaction Work (SOP10100) INNER JOIN SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200) ON ['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE AND ['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE LEFT JOIN SOP60300 AS ['Sales Customer Item Cross Reference'] -- Sales Customer Item Cross Reference (SOP60300) ON ['Sales Customer Item Cross Reference'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR AND ['Sales Customer Item Cross Reference'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR WHERE ['Sales Transaction Amounts Work'].SOPTYPE = 1 GO GRANT SELECT ON uv_AZRCRV_SalesQuoteLines TO DYNGRP GO

SQL View to Return Sales Quote Headers

Microsoft Dynamics GPI've been organising a demo of software which can be used to produce documentation using data from Microsoft Dynamics GP and integrate it with data from other systems or network shares. I thought about a few ways of getting the Dynamics GP data and decided that a custom SQL view returning only the required information was the best way to go.

This view returns sales quote headers and related fields such as the sales user defined.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_SalesQuoteHeaders', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesQuoteHeaders
GO
-- create view
CREATE VIEW [dbo].[uv_AZRCRV_SalesQuoteHeaders] AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
SELECT RTRIM(['Sales Transaction Work'].SOPNUMBE) AS 'Quote Number' ,['Sales Transaction Work'].DOCID AS 'Quote Type' ,['Sales Transaction Work'].DOCDATE AS 'Document Date' ,FORMAT(['Sales Transaction Work'].QUOTEDAT, 'dd-MM-yyyy') AS 'Quote Date' ,FORMAT(['Sales Transaction Work'].QUOEXPDA, 'dd-MM-yyyy') AS 'Quote Expiry Date' ,FORMAT(['Sales Transaction Work'].DUEDATE, 'dd-MM-yyyy') AS 'Due Date' ,FORMAT(['Sales Transaction Work'].ReqShipDate, 'dd-MM-yyyy') AS 'Requested Ship Date' ,RTRIM(['Sales Transaction Work'].CUSTNMBR) AS 'Customer Number' ,RTRIM(['Sales Transaction Work'].CUSTNAME) AS 'Customer Name' ,RTRIM(['Sales Transaction Work'].PRSTADCD) AS 'Ship To Address Code' ,RTRIM(['Sales Transaction Work'].CNTCPRSN) AS 'Contact Person' ,RTRIM(['Sales Transaction Work'].ShipToName) AS 'Ship To Name' ,RTRIM(['Sales Transaction Work'].ADDRESS1) AS 'Address 1' ,RTRIM(['Sales Transaction Work'].ADDRESS2) AS 'Address 2' ,RTRIM(['Sales Transaction Work'].ADDRESS3) AS 'Address 3' ,RTRIM(['Sales Transaction Work'].CITY) AS 'City' ,RTRIM(['Sales Transaction Work'].STATE) AS 'State' ,RTRIM(['Sales Transaction Work'].ZIPCODE) AS 'Zip Code' ,RTRIM(['Sales Transaction Work'].COUNTRY) AS 'Country' ,RTRIM(['Sales Transaction Work'].CURNCYID) AS 'Currency ID' ,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].ORSUBTOT) AS 'Originating Sub Total' ,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].ORTAXAMT) AS 'Originating Tax Amount' ,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].ORDOCAMT) AS 'Originating Document Amount' ,RTRIM(['Sales Transaction Work'].SALSTERR) AS 'Sales Territory ID' ,RTRIM(['Sales Territory Master'].SALSTERR) AS 'Sales Territory' ,RTRIM(['Sales Transaction Work'].SLPRSNID) AS 'Salesperson ID' ,RTRIM(['Sales User-Defined Work History'].USERDEF1) AS 'Priority' ,RTRIM(['Sales User-Defined Work History'].USERDEF2) AS 'Confirmation' ,RTRIM(['Sales User-Defined Work History'].USRDEF03) AS 'Ordered By' ,RTRIM(['Sales User-Defined Work History'].USRDEF04) AS 'Project Number' ,RTRIM(['Sales User-Defined Work History'].USRDEF05) AS 'Project Name' ,RTRIM(['RM Salesperson Master'].SLPRSNFN) + ' ' + RTRIM(['RM Salesperson Master'].SPRSNSLN) AS 'Salesperson' FROM SOP10100 AS ['Sales Transaction Work'] -- Sales Transaction Work (SOP10100) LEFT JOIN SOP10106 AS ['Sales User-Defined Work History'] -- Sales User-Defined Work History (SOP10106) ON ['Sales User-Defined Work History'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE AND ['Sales User-Defined Work History'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE LEFT JOIN RM00301 AS ['RM Salesperson Master'] -- RM Salesperson Master (RM00301) ON ['RM Salesperson Master'].SLPRSNID = ['Sales Transaction Work'].SLPRSNID LEFT JOIN RM00303 AS ['Sales Territory Master'] -- Sales Territory Master File (RM00303) ON ['Sales Territory Master'].SALSTERR = ['Sales Transaction Work'].SALSTERR WHERE ['Sales Transaction Work'].SOPTYPE = 1 GO GRANT SELECT ON uv_AZRCRV_SalesQuoteHeaders TO DYNGRP GO

Simple Audit for Microsoft Dynamics GP: Conclusion

Microsoft Dynamics GPThis post is part of a series on creating a simple audit for Microsoft Dynamics GP.

As I've shown over the last few posts, it is relatively easy to create a simple audit on a table in Dynamics GP, but this approach is not vert scalable and requires someone to manually create the SQL triggers. For a client on a shoestring budget and who wanted to audit one table, this approach sufficed.

However, for a larger client who would want to audit more tables in more than one company and who would want to have non-technical users maintaining the audited information, I would recommend a solution like Assure Suite from Fastpath which I have implemented with a umber of clients previously.

What brought this approach to mind was a client had an issue with some incorrect data and we could not determine who or what was changing some data, so I amended this custom audit for the tables we needed to record for that issue. In this instance, there was no need for a full audit solution as it will only be in place temporarily while investigating a specific issue.

Simple Audit for Microsoft Dynamics GP: SQL View for Reporting

Microsoft Dynamics GPThis post is part of a series on creating a simple audit for Microsoft Dynamics GP.

Once the audit table and triggers have been deployed, any changes made through the audit will be recorded and available for reviewing later to see who has been making changes and, more significantly, what was changed.

The easiest way of making this available to the client was to create a SmartList for them using SmartList Designer to select data from the new custom audit table. SmartList Designer can see either Dexterity tables or SQL views, but not custom SQL tables, I created a SQL view on the custom audit table, joining it to the Users Master (SY01400) table in the DYNAMICS table to get the username:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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 VIEW uv_AZRCRV_Audit AS SELECT ['Audit'].RecordType AS 'Record Type' ,['Audit'].RecordID AS 'Record ID' ,['Audit'].UpdateType AS 'Update Type' ,['Audit'].USERID AS 'User ID' ,['User Master'].Username AS 'Username' ,FORMAT(['Audit'].ChangeDateTime, 'yyyy-MM-dd') AS 'Change Date' ,FORMAT(['Audit'].ChangeDateTime, 'HH:ss') AS 'Change Time' ,['Audit'].OldData AS 'Old Data' ,['Audit'].NewData AS 'New Data' FROM ut_AZRCRV_Audit ['Audit'] LEFT JOIN DYNAMICS..SY01400 AS ['User Master'] ON ['User Master'].USERID = ['Audit'].USERID GO GRANT SELECT ON uv_AZRCRV_Audit TO DYNGRP GO

SQL View Showing Serial Number Allocated to SOP from POP in Microsoft Dynamics GP

Microsoft Dynamics GPA recent project required a report of serial numbers received into Microsoft Dynamics GP on purchase orders and to which sales transaction they'd been allocated. I had a hunt around in my scripts folder and found an old script I'd written which only required some small changes to add the required fields.

The script uses INNER JOIN clauses as only assigned serial numbers were wanted, but this could easily be changed to LEFT JOIN to return serial numbered items which had been received but not yet allocated.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_POPSOPSerialNumbers', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_POPSOPSerialNumbers
GO
-- create view
CREATE VIEW uv_AZRCRV_POPSOPSerialNumbers AS
SELECT
	['Purchasing Receipt Line Quantities'].PONUMBER
	,['Purchasing Receipt Line Quantities'].POPRCTNM
	,['Purchasing Receipt Line Quantities'].VENDORID
	,['Purchasing Receipt Line Quantities'].TRXLOCTN
	,['Sales Serial/Lot Work AND History'].ITEMNMBR
	,[Purchasing Serial Lot History'].SERLTNUM
	,['Sales Serial/Lot Work AND History'].SOPTYPE
	,CASE ['Sales Serial/Lot Work AND History'].SOPTYPE
		WHEN 1 THEN 'Quote'
		WHEN 2 THEN 'Order'
		WHEN 3 THEN 'Invoice'
		WHEN 5 THEN 'Back Order'
		ELSE ''
	END AS 'Type'
	,['Sales Serial/Lot Work AND History'].SOPNUMBE
	,['Sales Transactions'].DOCDATE
	,['Sales Transactions'].CUSTNMBR
FROM
	POP30330 AS [Purchasing Serial Lot History'] -- Purchasing Serial Lot History (POP30330)
INNER JOIN
	POP10500 AS ['Purchasing Receipt Line Quantities'] -- Purchasing Receipt Line Quantities (POP10500)
		ON
			['Purchasing Receipt Line Quantities'].POPRCTNM = [Purchasing Serial Lot History'].POPRCTNM
		AND
			['Purchasing Receipt Line Quantities'].RCPTLNNM = [Purchasing Serial Lot History'].RCPTLNNM
INNER JOIN
	SOP10201 AS ['Sales Serial/Lot Work AND History'] -- Sales Serial/Lot Work and History (SOP10201)
		ON
			['Sales Serial/Lot Work AND History'].SERLTNUM = [Purchasing Serial Lot History'].SERLTNUM
INNER JOIN
	(
		SELECT
			SOPNUMBE
			,SOPTYPE
			,DOCID
			,DOCDATE
			,CUSTNMBR
			,CUSTNAME
		FROM
			SOP10100 AS ['Sales Transaction Work'] -- Sales Transaction Work (SOP10100)
		UNION ALL
			SELECT
				SOPNUMBE
				,SOPTYPE
				,DOCID
				,DOCDATE
				,CUSTNMBR
				,CUSTNAME
			FROM
				SOP30200 AS ['Sales Transaction History'] -- Sales Transaction History (SOP30200)
	) AS ['Sales Transactions']
		ON
			['Sales Transactions'].SOPNUMBE = ['Sales Serial/Lot Work AND History'].SOPNUMBE
		AND
			['Sales Transactions'].SOPTYPE = ['Sales Serial/Lot Work AND History'].SOPTYPE
GO

GRANT SELECT ON uv_AZRCRV_POPSOPSerialNumbers TO DYNGRP
GO

SQL View to Return the Last Restore Date of Microsoft SQL Server Databases

Microsoft SQL ServerThis script will create a SQL view to select the last restore date for every database on a Microsoft SQL Server along with the user who performed the restore.

It has included a couple of other use columns in the returned data such as the collation_name and compatibility_level:

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRVGetLastDatabaseRestoreDate', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRVGetLastDatabaseRestoreDate
GO
-- create view
CREATE VIEW uv_AZRCRVGetLastDatabaseRestoreDate AS
WITH LastRestores AS
	(
		SELECT
			d.name AS DatabaseName
			,d.create_date
			,d.compatibility_level
			,d.collation_name
			,r.restore_date
			,r.user_name
			,ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC) AS RowNumber
		FROM
			master.sys.databases AS d
		LEFT JOIN
			msdb.dbo.[restorehistory] AS r
				ON
					r.[destination_database_name] = d.Name
	)

SELECT
	DatabaseName
	,create_date
	,compatibility_level
	,collation_name
	,restore_date
	,user_name
FROM
	LastRestores
WHERE
	RowNumber = 1
GO

GRANT SELECT ON uv_AZRCRVGetLastDatabaseRestoreDate TO DYNGRP
GO

I created this as a view so that it could easily be included in a SmartList using either SmartList Designer or SmartList Builder to allow finance users to see how up-to-date their test system is.

SQL View for to Extract Accruals from Microsoft Dynamics GP Payables Management Module

Microsoft Dynamics GPI recently did a webinar for my employer, ISC Software, on Prepayments and Acruals and as part of the accruals section used a SQL View to extract the accruals to be created; as mentioned in thw webinar, the extract cna be done either using a SmartList or a direct query in SmartConnect.

The SQL view has been created using the EOMONTH function which si available only in later versions of SQL Server; you may need to tweak the script a little to handle getting dates in different ways, if you are running an older version of SQL Server.

IF OBJECT_ID (N'uv_ISC_PayablesAccruals', N'V') IS NOT NULL
    DROP VIEW uv_ISC_PayablesAccruals
GO

CREATE VIEW uv_ISC_PayablesAccruals AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
SELECT * FROM (SELECT 'PAC ' + FORMAT(DATEADD(month,-1,GETDATE()), 'yyyyMM') AS 'Accruals Batch Number' ,FORMAT(EOMONTH(DATEADD(month,-1,GETDATE())), 'dd/MM/yyyy') AS 'Transaction Date' ,FORMAT(DATEADD(day, 1, EOMONTH(DATEADD(month,-1,GETDATE()))), 'dd/MM/yyyy') AS 'Reverse Date' ,'Purchase Accruals ' + FORMAT(DATEADD(month,-1,GETDATE()), 'MM/yyyy') AS 'Reference' ,ISNULL(GL00105_DAIM.ACTNUMST,GL00105.ACTNUMST) AS 'Account'[/sqlred] ,CASE WHEN DOCTYPE = 1 THEN 0 ELSE CASE WHEN PP000100.PP_Module IS NOT NULL THEN SUM(PP000101.TRXAMNT) ELSE SUM(PM10100.CRDTAMNT) END END AS 'Debit' ,CASE WHEN DOCTYPE = 1 THEN CASE WHEN PP000100.PP_Module IS NOT NULL THEN SUM(PP000101.TRXAMNT) ELSE SUM(PM10100.DEBITAMT) END ELSE 0 END AS 'Credit' ,LEFT(RTRIM(CAST(PM10100.VCHRNMBR AS VARCHAR(15))) + ' ' + PM10100.DistRef, 30) AS 'Description' ,PM10100.VCHRNMBR AS 'Voucher Number' ,PM10000.BACHNUMB AS 'Batch Number' ,PM10000.TRXDSCRN AS 'Document Description' ,PM10000.DOCNUMBR AS 'Document Number' ,PM10000.PORDNMBR AS 'PO Number' ,PM10100.DistRef AS 'Distribution Reference' FROM PM10000 AS PM10000 --PM Transaction WORK File (PM10000) INNER JOIN PM10100 AS PM10100 --PM Distribution WORK OPEN (PM10100) on PM10100.CNTRLTYP = PM10000.CNTRLTYP AND PM10100.VCHRNMBR = PM10000.VCHRNMBR INNER JOIN GL00105 AS GL00105 --Account Index Master (GL00105) on GL00105.ACTINDX = PM10100.DSTINDX LEFT JOIN -- Join to RED PP000100 AS PP000100 --Deferral Header Work (PP000100) on PP000100.CNTRLTYP = PM10100.CNTRLTYP AND PP000100.PP_Document_Number = PM10100.VCHRNMBR AND PP000100.PP_Sequencer = PM10100.DSTSQNUM LEFT JOIN GL00105 AS GL00105_DAIM --Account Index Master (GL00105) on GL00105_DAIM.ACTINDX = PP000100.ACTINDX LEFT JOIN PP000101 AS PP000101 --Deferral Line Work (PP000101) on PP000101.PP_Module = PP000100.PP_Module AND PP000101.PP_Record_Type = PP000100.PP_Record_Type AND PP000101.PP_Document_Number = PP000100.PP_Document_Number AND PP000101.PP_Sequencer = PP000100.PP_Sequencer AND PP000101.PPOFFSEQ = PP000100.PPOFFSEQ AND PP000101.CNTRLTYP = PP000100.CNTRLTYP AND PP000101.VCHRNMBR = PP000100.VCHRNMBR AND PP000101.DSTSQNUM = PP000100.DSTSQNUM AND PP000101.GLPOSTDT <= EOMONTH(DATEADD(month,-1,GETDATE())) WHERE PM10000.BCHSOURC = 'PM_Trxent' --Include only normal transactions AND PM10000.DOCTYPE = 1 --Include only Invoices AND PM10100.DISTTYPE = 6 --Include only Purchases Distribution GROUP BY GL00105.ACTNUMST ,GL00105_DAIM.ACTNUMST ,PM10000.DOCTYPE ,PP000100.PP_Module ,PM10100.DistRef ,PM10100.VCHRNMBR ,PM10000.BACHNUMB ,PM10000.TRXDSCRN ,PM10000.DOCNUMBR ,PM10000.PORDNMBR HAVING SUM(PP000101.TRXAMNT) > 0 --partial deferral OR PP000100.PP_Module IS NULL --no deferral UNION ALL SELECT 'PAC ' + FORMAT(DATEADD(month,-1,GETDATE()), 'yyyyMM') AS 'Accruals Batch Number' ,FORMAT(EOMONTH(DATEADD(month,-1,GETDATE())), 'dd/MM/yyyy') AS 'Transaction Date' ,FORMAT(DATEADD(day, 1, EOMONTH(DATEADD(month,-1,GETDATE()))), 'dd/MM/yyyy'[/sqlred]) AS 'Reverse Date' ,'Purchase Accruals ' + FORMAT(DATEADD(month,-1,GETDATE()),'MM/yyyy') AS 'Reference' ,GL00105.ACTNUMST AS 'Account' ,CASE WHEN PP000100.PP_Module IS NOT NULL THEN SUM(PP000101.TRXAMNT) ELSE SUM(PM10100.DEBITAMT) END AS 'Debit' ,CASE WHEN PP000100.PP_Module IS NOT NULL THEN SUM(PP000101.TRXAMNT) ELSE SUM(PM10100.CRDTAMNT) END AS 'Credit' ,'Accrued Purchases' AS 'Description' ,'' AS 'Voucher Number' ,'' AS 'Batch Number' ,'' AS 'Document Description' ,'' AS 'Document Number' ,'' AS 'PO Number' ,'' AS 'Distribution Reference' FROM PM10000 AS PM10000 --PM Transaction WORK File (PM10000) INNER JOIN PM10100 AS PM10100 --PM Distribution WORK OPEN (PM10100) on PM10100.CNTRLTYP = PM10000.CNTRLTYP AND PM10100.VCHRNMBR = PM10000.VCHRNMBR INNER JOIN GL00100 AS GL00100 --Breakdown Account Master (GL00100) on GL00100.ACTDESCR = 'Accrued Purchases' INNER JOIN GL00105 AS GL00105 --Account Index Master (GL00105) on GL00105.ACTINDX = GL00100.ACTINDX LEFT JOIN -- Join to RED PP000100 AS PP000100 --Deferral Header Work (PP000100) on PP000100.CNTRLTYP = PM10100.CNTRLTYP AND PP000100.PP_Document_Number = PM10100.VCHRNMBR AND PP000100.PP_Sequencer = PM10100.DSTSQNUM LEFT JOIN GL00105 AS GL00105_DAIM --Account Index Master (GL00105) on GL00105_DAIM.ACTINDX = PP000100.ACTINDX LEFT JOIN PP000101 AS PP000101 --Deferral Line Work (PP000101) on PP000101.PP_Module = PP000100.PP_Module AND PP000101.PP_Record_Type = PP000100.PP_Record_Type AND PP000101.PP_Document_Number = PP000100.PP_Document_Number AND PP000101.PP_Sequencer = PP000100.PP_Sequencer AND PP000101.PPOFFSEQ = PP000100.PPOFFSEQ AND PP000101.CNTRLTYP = PP000100.CNTRLTYP AND PP000101.VCHRNMBR = PP000100.VCHRNMBR AND PP000101.DSTSQNUM = PP000100.DSTSQNUM AND PP000101.GLPOSTDT <= EOMONTH(DATEADD(month,-1,GETDATE())) WHERE PM10000.BCHSOURC = 'PM_Trxent' --Include only normal transactions AND PM10000.DOCTYPE = 1 --Include only Invoices AND PM10100.DISTTYPE = 6 --Include only Purchases Distribution GROUP BY GL00105.ACTNUMST ,GL00105_DAIM.ACTNUMST ,PM10000.DOCTYPE ,PP000100.PP_Module HAVING SUM(PP000101.TRXAMNT) > 0 --partial deferral OR PP000100.PP_Module IS NULL --no deferral ) AS Accruals WHERE Accruals.Debit >[/sqlgrey] 0 OR Accruals.Credit > 0 GO GRANT SELECT ON uv_ISC_PayablesAccruals TO DYNGRP GO

SQL View on Sales Order Processing Backorder Transactions

Microsoft Dynamics GPI've written this view for a number of different clients over the years, so am posting it here for future reference. It returns only open backorders which have yet top be transferred to order or invoice; it includes PO information so users can easily see if a PO has been raised and if any of it has been receipted.

The SQL below includes the

CREATE VIEW uv_AZRCRV_SOPOpenBackorders AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
SELECT ['Sales Transaction Work'].SOPNUMBE AS 'SOP Number' ,['Sales Transaction Work'].SOPTYPE AS 'SOP Type' ,['Sales Transaction Work'].DOCDATE AS 'Document Date' ,['Sales Transaction Work'].CSTPONBR AS 'Customer PO Number' ,['Sales Transaction Work'].CURNCYID AS 'Currency' ,['Sales Transaction Work'].CUSTNMBR AS 'Customer Number' ,['Sales Transaction Work'].CUSTNAME AS 'Customer Name' ,['Sales Transaction Amounts Work'].ITEMNMBR AS 'Item Number' ,['Sales Transaction Amounts Work'].ITEMDESC AS 'Item Description' ,['Sales Transaction Amounts Work'].QUANTITY AS 'Original Backorder Quantity' ,['Sales Transaction Amounts Work'].QTYTORDR + ['Sales Transaction Amounts Work'].QTYTOINV AS 'Current Backorder Quantity' ,['Sales Transaction Amounts Work'].QUANTITY - (['Sales Transaction Amounts Work'].QTYTORDR + ['Sales Transaction Amounts Work'].QTYTOINV) AS 'Quantity Shipped' ,['Sales Transaction Amounts Work'].UNITPRCE AS 'Unit Price' ,['Sales Transaction Amounts Work'].XTNDPRCE AS 'Extended Price' ,(['Sales Transaction Amounts Work'].QTYTORDR + ['Sales Transaction Amounts Work'].QTYTOINV) * ['Sales Transaction Amounts Work'].UNITPRCE AS 'Current Extended Price' ,['Sales Transaction Amounts Work'].ORUNTPRC AS 'Originating Unit Price' ,['Sales Transaction Amounts Work'].OXTNDPRC AS 'Originating Extended Price' ,(['Sales Transaction Amounts Work'].QTYTORDR + ['Sales Transaction Amounts Work'].QTYTOINV) * ['Sales Transaction Amounts Work'].ORUNTPRC AS 'Current Originating Extended Price' ,['Sales Transaction Amounts Work'].LOCNCODE AS 'Site ID' ,['Sales Transaction Amounts Work'].ReqShipDate AS 'Requested Ship Date' ,['SOP_POP Link'].PONUMBER AS 'PO Number' ,['Purchase Order Lines'].VENDORID AS 'Vendor ID' ,['SOP_POP Link'].QTYRECVD AS 'Quantity Received' ,['Purchase Order Lines'].PRMSHPDTE AS 'Promised Ship Date' ,['Sales Line Comment Work and History'].CMMTTEXT AS 'Comment Text' FROM SOP10100 AS ['Sales Transaction Work'] --Sales Transaction Work (SOP10100) INNER JOIN SOP10200 AS ['Sales Transaction Amounts Work'] --Sales Transaction Amounts Work (SOP10200) ON ['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE AND ['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE INNER JOIN RM00101 AS ['RM Customer Master'] --RM Customer MSTR (RM00101) ON ['RM Customer Master'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR LEFT JOIN SOP60100 AS ['SOP_POP Link'] --SOP_POPLink (SOP60100) ON ['SOP_POP Link'].SOPTYPE = ['Sales Transaction Amounts Work'].SOPTYPE AND ['SOP_POP Link'].SOPNUMBE = ['Sales Transaction Amounts Work'].SOPNUMBE AND ['SOP_POP Link'].CMPNTSEQ = ['Sales Transaction Amounts Work'].CMPNTSEQ AND ['SOP_POP Link'].LNITMSEQ = ['Sales Transaction Amounts Work'].LNITMSEQ LEFT JOIN SOP10202 AS ['Sales Line Comment Work and History'] --Sales Distribution Work and History (SOP10102) ON ['Sales Line Comment Work and History'].SOPTYPE = ['Sales Transaction Amounts Work'].SOPTYPE AND ['Sales Line Comment Work and History'].SOPNUMBE = ['Sales Transaction Amounts Work'].SOPNUMBE AND ['Sales Line Comment Work and History'].CMPNTSEQ = ['Sales Transaction Amounts Work'].CMPNTSEQ AND ['Sales Line Comment Work and History'].LNITMSEQ = ['Sales Transaction Amounts Work'].LNITMSEQ LEFT JOIN POP10110 AS ['Purchase Order Lines'] --Purchase Order Line (POP10110) ON ['Purchase Order Lines'].PONUMBER = ['SOP_POP Link'].PONUMBER AND ['Purchase Order Lines'].ORD = ['SOP_POP Link'].ORD WHERE ['Sales Transaction Work'].SOPTYPE = 5 GO GRANT SELECT ON uv_AZRCRV_SOPOpenBackorders TO DYNGRP GO