I have some clients who use the Bill of Materials and assemblies within the Inventory series rather than the Manufacturing series and full MRP; their processes are not so complex that they need this level of MRP functionality. To make it easy to link an assembly to a sales order, the assemblies are created with the same ID as the order (one of the clients has a high level of automation added via customisations to automatically create the assembly from the order).
I’ve created a script to return this information on more than one occasion, so finally decided to post it here so I can easily find it.
CREATE VIEW uv_AZRCRV_SalesOrdersToBeAssembled 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 CASE WHEN LEFT(RTRIM(SOP102.SOPNUMBE),3) = 'ORD' THEN SUBSTRING(RTRIM(SOP102.SOPNUMBE),4,LEN(RTRIM(SOP102.SOPNUMBE))-3) ELSE RTRIM(SOP102.SOPNUMBE) END + CASE WHEN (SELECT COUNT(SOPNUMBE) FROM SOP10200 SOP102I WHERE SOP102I.SOPNUMBE = SOP102.SOPNUMBE) > 1 THEN '_' + CAST(SOP102.LNITMSEQ/13684 AS VARCHAR(2)) ELSE '' END AS 'Assembly' ,FORMAT(GETDATE(), 'yyyyMMdd') AS 'Batch Number' ,RTRIM(SOP102.ITEMNMBR) AS 'Item Number' ,CASE WHEN SOP102.UOFM = 'EACH' THEN CAST(CAST(SOP102.QUANTITY AS DECIMAL(10,0)) AS VARCHAR(10)) ELSE CAST(CAST(SOP102.QUANTITY*10000 AS DECIMAL(10,0)) AS VARCHAR(10)) END AS 'Quantity' ,SOP102.UOFM AS 'UofM' ,SOP101.BACHNUMB AS 'Sales Batch' FROM SOP10200 SOP102 WITH (NOLOCK) INNER JOIN SOP10100 SOP101 WITH (NOLOCK) ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPTYPE = SOP102.SOPTYPE INNER JOIN BM00101 BM101 WITH (NOLOCK) ON BM101.ITEMNMBR = SOP102.ITEMNMBR AND BM101.Bill_Status = 1 LEFT JOIN BM10200 BM102 WITH (NOLOCK) ON BM102.TRX_ID = RTRIM(SUBSTRING(SOP102.SOPNUMBE,4,LEN(SOP102.SOPNUMBE)-3)) LEFT JOIN BM30200 BM302 WITH (NOLOCK) ON BM302.TRX_ID = RTRIM(SUBSTRING(SOP102.SOPNUMBE,4,LEN(SOP102.SOPNUMBE)-3)) WHERE SOP102.SOPTYPE = 2 AND BM102.TRX_ID IS NULL AND BM302.TRX_ID IS NULL GO GRANT SELECT ON uv_AZRCRV_SalesOrdersToBeAssembled TO DYNGRP
Back in July 2013 I did a post where I looked at a problem copying live to test. The basic issue was that the Microsoft Dynamics GP user is also a login (at the SQL Server level) and a user (at the SQL Server database level) and when a database is copied from the live server to the test server (or from the current live top the new live) you can run a script to transfer across the logins, but the users come across with the database and will have different SIDs (Security IDs).
You can use the ALTER USER command in SQL to re-link the login with the user, but this is one statement per user per database. The old post showed how to do this, but this quickly becomes a pain when there are more than a handful of users.
As Perfect Image has grown we have clients with more and more users and/or company databases. Our largest client has over 250 users in their Dynamics GP installation while another has fewer users, but well over 100 companies. Both of these can make copying live to test problematic, especially when only a company database might be copied over rather than the whole system.
I needed to automate the process of altering the login to match the user; the below script is the result of this need. Continue reading → SQL Script To Bulk Alter Users With Logins
With some clients who have objects with this naming convention looking to do upgrades I’ve taken the next step and created some SQL queries to select all of these objects (which was always the next step).
The naming convention I adopted is in the following format:
- organisation who created
- name (which will be omitted if the object is a generic one which might be given to multiple clients)
So, a custom table, created by azurecurve for Fabrikam, Inc. to store a Sales Order/Assembly cross reference would be called ut_AZRCRV_FAB_SalesOrderAssemblyXref.
The type prefix varies by object type, but always starts with a u for user. The types I use are:
- ut for tables
- uv for views
- uf for functions
- usp for stored procedures
- utr for triggers
The following view (following my naming convention above lacks a client as it is generic) selects all custom objects in the database created by AZRCRV:
CREATE VIEW uv_AZRCRV_GetCustomObjects 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 o.name,'' AS 'table name', o.type_desc, o.modify_date FROM sys.objects AS o WHERE o.name LIKE 'u__AZRCRV_%' UNION ALL SELECT i.name, o.name, o.type_desc, o.modify_date FROM sys.indexes AS i INNER JOIN sys.objects AS o ON o.object_id = i.object_id WHERE I.name LIKE 'u%_AZRCRV_%' UNION ALL SELECT t.name, o.name, t.type_desc, o.modify_date FROM sys.triggers AS t INNER JOIN sys.objects AS o ON o.object_id = t.object_id WHERE o.name LIKE 'u%_AZRCRV_%'
The view can either by run manually in SQL Server Management Studio or plugged into either SmartList Designer or SmartList Builder. Once all custom items have been located, they can be extracted and preserved as scripts to be redeployed after the upgrade if necessary.
This has come up from a couple of different clients in the last few weeks. It would be good if approval workflows could be added to Receivings Transaction Entry and also the Enter/Match Invoices windows.
While there is workflow on the PO, a PO might not be fully received/invoiced so it would be good to have approval of the receivings document or the invoice.
The MS Connect suggestion can be found here.
Please take a look and cast your vote.