Month: May 2023

SQL Scripts for Microsoft Dynamics GP: SQL Script to Return PO Receipts

Ian Grieve5 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 was created for a client to return a list of purchase receipts for use in their document management system.

CREATE VIEW [dbo].[uv_AZRCRV_GetPurchaseReceipts] AS
/*
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).
*/
SELECT
	['Purchase Receipt Work'].POPRCTNM
	,['Purchase Receipt Line'].RCPTLNNM AS RCPTLNNM
	--,['Purchase Receipt Line'].RCPTLNNM/16384 AS RCPTLNNM
	,['Purchase Receipt Line'].PONUMBER
	,['Purchase Receipt Line Quanities'].POLNENUM
	,['Purchase Receipt Work'].VENDORID
	,['Purchase Receipt Line'].ITEMNMBR
	,['Purchase Receipt Line Quanities'].QTYSHPPD
	,['Purchase Receipt Line Quanities'].QTYSHPPD - ['Purchase Receipt Line Quanities'].QTYMATCH AS QTYRMAIN
	,['Purchase Receipt Line'].UNITCOST
	,['Purchase Receipt Line'].EXTDCOST
	,ROUND(['Purchase Receipt Line'].EXTDCOST - (['Purchase Receipt Line'].EXTDCOST * 20 / (100 + 20)),2) AS NET
	,ROUND(['Purchase Receipt Line'].EXTDCOST * 20 / (100 + 20),2) AS VAT
	,['Purchase Receipt Work'].receiptdate
	,['Account Index Master'].ACTINDX
	,['Account Index Master'].ACTNUMST
	,['Purchasing Distributions WORK'].ACTINDX AS ACCINDX
	,(SELECT
		ACTINDX
	FROM
		SY01100 --Posting Account Master (SY01100)
	WHERE
		SERIES = 4
	AND
		PTGACDSC = 'Accounts Payable') AS PAYINDX
	,'PS' AS TAXSCHID
	,'PS' AS TAXDTLID
	,(SELECT TOP 1
		ISNULL(['Workflow Users'].ADDisplayName,['Workflow History'].Workflow_History_User)
	FROM
		WFI10002 ['Workflow Instance Master'] --Workflow Instance Master (WFI10002)
	INNER JOIN 
		WF30100 ['Workflow History'] --Workflow History (WF30100)
			ON
				['Workflow History'].WorkflowInstanceID = ['Workflow Instance Master'].WorkflowInstanceID
			AND
				['Workflow History'].Workflow_Action = 10
	LEFT JOIN
		WF40200 ['Workflow Users'] --Workflow Users (WF40200)
			ON
				['Workflow Users'].ADLogin = ['Workflow History'].Workflow_History_User
	WHERE
		WfBusObjKey = ['SOP_POP Link'].SOPNUMBE) AS Approver
FROM
	POP30300 AS ['Purchase Receipt Work'] --Purchasing Receipt History (POP30300)
INNER JOIN
	POP30310 AS ['Purchase Receipt Line'] --Purchasing Receipt Line History (POP30310)
		ON ['Purchase Receipt Line'].POPRCTNM = ['Purchase Receipt Work'].POPRCTNM
INNER JOIN
	POP30390 AS ['Purchasing Distributions WORK'] --Purchasing Distribution History (POP30390)
		ON
			['Purchasing Distributions WORK'].POPRCTNM = ['Purchase Receipt Work'].POPRCTNM
		AND
			['Purchasing Distributions WORK'].DISTTYPE = 9
LEFT JOIN
	POP10500 AS ['Purchase Receipt Line Quanities'] --Purchasing Receipt Line Quantities (POP10500)
		ON
			['Purchase Receipt Line Quanities'].POPRCTNM = ['Purchase Receipt Line'].POPRCTNM
		AND
			['Purchase Receipt Line Quanities'].RCPTLNNM = ['Purchase Receipt Line'].RCPTLNNM
		AND
			['Purchase Receipt Line Quanities'].PONUMBER = ['Purchase Receipt Line'].PONUMBER
LEFT JOIN
	GL00105 AS ['Account Index Master'] --Account Index Master (GL00105)
		ON
			['Account Index Master'].ACTINDX = ['Purchase Receipt Line'].INVINDX
LEFT JOIN
	POP10360 AS ['Purchasing Tax'] --Purchasing Tax (POP10360)
		ON
			['Purchasing Tax'].POPRCTNM = ['Purchase Receipt Line'].POPRCTNM
		AND
			['Purchasing Tax'].RCPTLNNM = ['Purchase Receipt Line'].RCPTLNNM
LEFT JOIN
	SOP60100 AS ['SOP_POP Link'] --SOP_POPLink (SOP60100)
		ON
			['SOP_POP Link'].PONUMBER = ['Purchase Receipt Line'].PONUMBER
		AND
			['SOP_POP Link'].ORD = 16384
WHERE
	['Purchase Receipt Work'].POPTYPE = 1
AND 
	['Purchase Receipt Line Quanities'].QTYSHPPD - ['Purchase Receipt Line Quanities'].QTYMATCH > 0
GO

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

Delete Specific Cookie in the Vivaldi Browser

Ian Grieve1 minute read0 comment

Vivaldi BrowserI recently wanted to delete a specific cookie from the Vivaldi browser and it took me a few minutes to find the place to do it, so I thought I’d document in case I need to do it again and don’t remember where.

Open the Settings page from the menu and select the Privacy and Security tab; click the (highlighted) Show Saved Cookies button:

Privacy and Security page

Continue reading “Delete Specific Cookie in the Vivaldi Browser”

SQL Scripts for Microsoft Dynamics GP: SQL View to Return Purchase Orders

Ian Grieve5 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 was created for a client to use for lookups on Dynamics GP purchase orders from their document management system.

CREATE VIEW [dbo].[uv_AZRCRV_GetPurchaseOrders] AS
/*
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).
*/
SELECT
	['Purchase Order Work'].PONUMBER
	,['Purchase Order Line'].ORD/16384 AS ORDLNNM
	,['Purchase Order Work'].POSTATUS
	,CASE WHEN ['Purchase Order Work'].POSTATUS = 1 THEN
			'NEW'
		WHEN ['Purchase Order Work'].POSTATUS = 2 THEN
			'Released'
		WHEN ['Purchase Order Work'].POSTATUS = 3 THEN
			'Change Order'
		WHEN ['Purchase Order Work'].POSTATUS = 4 THEN
			'Received'
		WHEN ['Purchase Order Work'].POSTATUS = 5 THEN
			'Closed'
		WHEN ['Purchase Order Work'].POSTATUS = 6 THEN
			'Cancelled'
	END AS POSTATUSDESC
	,['Purchase Order Work'].DOCDATE
	,['Purchase Order Work'].VENDORID
	,['Purchase Order Work'].SUBTOTAL
	,['Purchase Order Work'].TAXAMNT
	,['Purchase Order Work'].SUBTOTAL + ['Purchase Order Work'].TAXAMNT AS TOTAL
	,['Purchase Order Line'].QTYORDER
	,['Purchase Order Line'].UNITCOST
	,['Purchase Order Line'].EXTDCOST
	,['Purchase Order Work'].USER2ENT
	,REPLACE(RTRIM(['User Master'].USERNAME), ' ' , '.') AS Originator
	,['Account Index Master'].ACTNUMST
	,CASE WHEN LEN(['Purchase Order Line'].Purchase_Item_Tax_Schedu) = 0 THEN
		['Purchase Order Line'].Purchase_Site_Tax_Schedu
	ELSE
		['Purchase Order Line'].Purchase_Item_Tax_Schedu
	END AS TAXSCHID
	,['Purchasing Tax'].TAXDTLID
	,(SELECT TOP 1
		ISNULL(['Workflow Users'].ADDisplayName,['Workflow History'].Workflow_History_User)
	FROM
		WFI10002 ['Workflow Instance Master'] --Workflow Instance Master (WFI10002)
	INNER JOIN 
		WF30100 ['Workflow History'] --Workflow History (WF30100)
			ON
				['Workflow History'].WorkflowInstanceID = ['Workflow Instance Master'].WorkflowInstanceID
			AND
				['Workflow History'].Workflow_Action = 10
	LEFT JOIN
		WF40200 ['Workflow Users'] --Workflow Users (WF40200)
			ON
				['Workflow Users'].ADLogin = ['Workflow History'].Workflow_History_User
	WHERE
		WfBusObjKey = ['SOP_POP Link'].SOPNUMBE ORDER BY ['Workflow History'].DEX_ROW_ID DESC) AS Approver
FROM
	POP10100 AS ['Purchase Order Work'] --Purchase Order Work (POP10100)
INNER JOIN
	POP10110 AS ['Purchase Order Line'] --Purchase Order Line (POP10110)
		ON ['Purchase Order Line'].PONUMBER = ['Purchase Order Work'].PONUMBER
LEFT JOIN
	GL00105 AS ['Account Index Master'] --Account Index Master (GL00105)
		ON
			['Account Index Master'].ACTINDX = ['Purchase Order Line'].INVINDX
LEFT JOIN
	POP10160 AS ['Purchasing Tax'] --Purchase Order Tax (POP10160)
		ON
			['Purchasing Tax'].PONUMBER = ['Purchase Order Line'].PONUMBER
		AND
			['Purchasing Tax'].ORD = ['Purchase Order Line'].ORD
LEFT JOIN
	SOP60100 AS ['SOP_POP Link'] --SOP_POPLink (SOP60100)
		ON
			['SOP_POP Link'].PONUMBER = ['Purchase Order Line'].PONUMBER
		AND
			['SOP_POP Link'].ORD = ['Purchase Order Line'].ORD
INNER JOIN
	DYNAMICS..SY01400 AS ['User Master'] --Users Master (SY01400)
		ON
			['User Master'].USERID = ['Purchase Order Work'].USER2ENT
GO

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

In Microsoft Dynamics 365 Business Central (Financial), how do I… Configure Dimension Combination Blocks

Ian Grieve2 minute read0 comment

xMicrosoft Dynamics 365 Business CentralThis post is part of the In Microsoft Dynamics 365 Business Central (Financial), 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 the last article, I discussed what dimension combinations are and in this one I am going to cover how you can configure dimension combination blocks.

A dimension combination block can be configured to stop users using a combination of dimensions, regardless of the dimension values. An example of when this would be useful, is when I have dimensions for purchaser and customer group and I wanted to make sure that users could not add both of these dimensions to the same transaction; a dimension block will allow me to stop users doing this.

To add a dimension combination block, search for dim comb and select Dimension Combinations:

BC search showing results for dim comb

Continue reading “In Microsoft Dynamics 365 Business Central (Financial), how do I… Configure Dimension Combination Blocks”

SQL Scripts for Microsoft Dynamics GP: SQL View to Return Category Linked to Segment 3 in COA

Ian Grieve2 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 was created for a client to review the chart of accounts to make sure that all accounts using segment three had the same category assigned.

CREATE VIEW [dbo].[uv_AZRCRV_AccountCategoryReview] AS
/*
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).
*/
SELECT DISTINCT
	['Account Index Master'].ACTNUMBR_3 AS 'Cost Account'
	,['Segment Master'].DSCRIPTN AS 'Cost Account Description'
	,['Account Category Master'].ACCATDSC AS 'Account Category Description'
	,['Account Index Master'].USERDEF2
FROM
	GL00100 AS ['Account Index Master'] --Breakdown Account Master (GL00100)
INNER JOIN
	GL00102 AS ['Account Category Master'] --Account Category Master (GL00102)
		ON
			['Account Category Master'].ACCATNUM = ['Account Index Master'].ACCATNUM
INNER JOIN
	GL40200 AS ['Segment Master'] --Segment Description Master (GL40200)
		ON
			['Segment Master'].SGMNTID = ['Account Index Master'].ACTNUMBR_3
		AND
			['Segment Master'].SGMTNUMB = 3
GO

GRANT SELECT ON uv_AZRCRV_AccountCategoryReview TO DYNGRP
GO

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

Use Sizer to Set Window Size

Ian Grieve3 minute read0 comment

Useful ApplicationsI take a lot of screenshots and often want to get them the same size; if dealing with a browser this can be difficult over a period of time, so I did some investigation and found an application called Sizer which can be used to accurately resize and reposition windows.

The application is free and, based on my experience so far, very easy to both configure and use.

Sizer download page
  • Hot keys. Sizer actions, including bringing up the main resizing menu, can be assigned to a hot key that can be used at any time. e.g. by default pressing Ctrl+Win+Z brings up the Sizer context menu. This saves you hunting out the window border to right click it.
  • Macros and expressions. Specify sizes and positions as formula. e.g. 0.7 * w:mon results in a value that is 70% of the width of the current monitor. With macros you can position several windows at the same time allowing you to layout multiple applications the way you want them. Further documentation will follow but in the meantime you can consult the railroad diagrams for details of the syntax.
  • Menu groups. Simplify and tidy up the Sizer menu by creating cascading menus with groups.
  • Portable. Run Sizer from a USB pen drive or keep your configurations in one place.
  • Configuration file management. Export and import settings via the user interface.

SQL Scripts for Microsoft Dynamics GP: SQL View to Return Quantity Available

Ian Grieve2 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 was created for a client to use in SmartList Builder, and other, reports; it returns the quantity available for items on a location basis.

/*
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 VIEW uv_AZRCRV_GetAvailableQuantity AS
/*
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).
*/
SELECT
	[Item Quantity Master'].ITEMNMBR
	,[Item Quantity Master'].LOCNCODE
	,SUM([Item Quantity Master'].QTYONHND - [Item Quantity Master'].ATYALLOC - ISNULL(['Sales Transaction Amounts Work'].QTYALLOC,0)) AS 'QTYAVAIL'
FROM
	IV00102 AS [Item Quantity Master'] --Item Quantity Master (IV00102)
LEFT JOIN
	(
		SELECT
			ITEMNMBR
			,LOCNCODE
			,SUM((QTYREMAI - ATYALLOC) * QTYBSUOM) AS QTYALLOC
		FROM
			SOP10200 AS ['Sales Transaction Amounts Work'] --Sales Transaction Amounts Work (SOP10200)
		GROUP BY
			ITEMNMBR
			,LOCNCODE
	) AS ['Sales Transaction Amounts Work']
		ON
			['Sales Transaction Amounts Work'].ITEMNMBR = [Item Quantity Master'].ITEMNMBR
		AND
			['Sales Transaction Amounts Work'].LOCNCODE = [Item Quantity Master'].LOCNCODE
WHERE
	[Item Quantity Master'].RCRDTYPE = 2
GROUP BY
	[Item Quantity Master'].ITEMNMBR
	,[Item Quantity Master'].LOCNCODE
GO

GRANT SELECT ON uv_AZRCRV_GetAvailableQuantity TO DYNGRP
GO

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

In Microsoft Dynamics 365 Business Central (Financial), how do I… Understand Dimension Combinations

Ian Grieve4 minute read3 Comments

Microsoft Dynamics 365 Business CentralThis post is part of the In Microsoft Dynamics 365 Business Central (Financial), 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.

To avoid posting entries with contradictory or irrelevant dimensions, you can block or limit specific combinations of two dimensions. A blocked dimension combination means you cannot post both dimensions on the same entry regardless of what the dimension values are. In contrast, a limited dimension combination means you can post both dimensions to the same entry, but only for certain combinations of dimension values.

The purpose of dimension combinations is to prevent contradictory or irrelevant combinations of dimensions being posted on the same transaction.

Dimension combinations allow a restriction on the combinations of two dimensions; there are two types of restriction available:

  1. limiting which allows only certain combinations of values from the limited dimensions to be used together.
  2. blocking which prevents two dimensions being posted on a transaction, regardless of the dimension values.

As an example, you could use a dimension combination to limit which region and cost centre dimensions can be used together or you could block the purchaser and salesperson dimensions being used on the same transaction.

Once you have set up dimension combination restrictions, users will only be able to select combinations of dimensions which are allowed by the restriction. If they try to select an invalid combination, an error message will be produced and they will not be able to save the transaction until they correct the error.

I’ll show how to set up a dimension combination limit, block and then how to remove a combination over the next few articles in this series.

In Microsoft Dynamics 365 Business Central, how do I…

In Microsoft Dynamics 365 Business Central (Financial), how do I…

ClassicPress Plugins Available From azurecurve | Development in 2023: Images

Ian Grieve1 minute read0 comment

ClassicPressIn this series of articles, I am going to introduce each of the plugins I have developed for ClassicPress, a hard-fork of WordPress, which was originally created to provide an alternative, yet compatible, CMS without the Gutenberg block editor.

The 22nd plugin is Images.

Images
Shortcode to display image in post; add width, alt, caption or error attributes.

Example shortcode:

[image image="http://www.example.com/hello.png" width="550px" alt="Hello World!"]

This plugin is multisite compatible.

Continue reading “ClassicPress Plugins Available From azurecurve | Development in 2023: Images”

ClassicPress Plugins Available From azurecurve | Development in 2023: Icons

Ian Grieve2 minute read0 comment

ClassicPressIn this series of articles, I am going to introduce each of the plugins I have developed for ClassicPress, a hard-fork of WordPress, which was originally created to provide an alternative, yet compatible, CMS without the Gutenberg block editor.

The 21st plugin is Icons.

Icons

Easily add an icon to a post or page using the [icon] shortcode.

For example, to display the note icon, shortcode usage is [icon=note]; 1,000 icons from the famfamfam Silk collection are included.

Definition of icons can be found at famfamfam.com; a settings page also shows all available icons.

Custom cons can be added; if a custom icon with the same name as a standard icon exists, the custom icon will be used.

Shortcodes In Comments can be used to allow flags in comments and Shortcodes In Widgets can allow them in widgets.

This plugin is multisite compatible.

Continue reading “ClassicPress Plugins Available From azurecurve | Development in 2023: Icons”