Next Routing Sequence for Work Orders in Dynamics GP

I was asked to create a SmartList using SmartList Builder, which, among other things, would show the next Routing Sequence for Work Orders.  The client wanted the Work Center Description and not the Sequence Number.

In the end I had to nest a query within a nested query to get the required results.  Here is the core of the query - feel free to use it in your own endeavors:

Select A.MANUFACTUREORDER_I,WR.RTSEQDES_I

From WR010130 WR

inner join

(Select MANUFACTUREORDER_I,MIN(RTSEQNUM_I) Sequence from WR010130 where DONECB_I !=1 Group by MANUFACTUREORDER_I) A

on WR.MANUFACTUREORDER_I = A.MANUFACTUREORDER_I

and WR.RTSEQNUM_I = A.Sequence  

"You do not have access privileges needed to change this quantity" in GP Manufacturing

In Dynamics GP, as a general rule, a user assigned the POWERUSER role or the sa account are "all powerful," and can perform any task. What happens when the sa or a POWERUSER tries to change the ending quantity on the Manufacturing Order and they receive the following error. "You do not have access privileges needed to change this quantity?" I have seen this issue a couple of times in the forum recently, so I thought it would be prudent to write a blog article on the topic.

There is a simple cause and a simple solution to this problem. Dynamics GP Manufacturing was once a third party product (a very long time ago), and a programmer, was likely asked to make sure that a user could not just change the quantities on a Manufacturing Order.  So this programmer created a feature, outside typical GP security setup to control access to Manufacturing Order quantity field changes.

In the end, what was produced is security more akin to Hold Processing or Tolerances in various Financial and Distribution Modules; a security setting that exists at the module configuration level and not at the System Security level.

When the user receives the "You do not have access privileges needed to change this quantity." error, either they have not be granted access, or this feature hasn't been set up at all - it is typical the latter.  If you implement GP Manufacturing, you should add explanation and configuration of this feature to your setup checklist.

Here's an overview of how to set this feature up:

First - navigate to Manufacturing > Setup > System Defaults > MO Schedule Prefs and select your Default Settings.  Enter a Process Security ID for Manufacturing Qty Overrides. 

You should receive a dialogue box indicating the ID doesn't exist, and asking if you would like to create it.  You should click Add.

Clicking Add will trigger System Security, if a System Password has been configured. So, you'll need to know this password before you begin to setup Process Security.

Once you have provided the correct system password, the Process Security Setup window will open.  At this point you have a decision to make.  Configure security to prompt for a password when a Manufacturing Quantity is changed, or configure security to only allow approved users to change Manufacturing Quantities.

While the easiest to setup and maintain option is Password control, passwords have a tendency to 'escape into the wild' and the next thing you know, your control is control in name only. I am a fan of configuring user specific control. If you go the User ID route, you should add this step to your New User Check List. If you don't have a New User Check List, create one.  

If you go the Password route, simply enter a password here, and distribute the password to personnel authorized to change quantities on Manufacturing Orders.  If not, selecting the User ID radio button will bring up a list box, where User ID's authorized to change quantities on Manufacturing Orders can be inserted.

I am not a big fan of the design of this screen. In order to add users, you must click on the Magnifying glass, look up a user and select it, you cannot tab through the fields typing in User ID's.

What is not immediately obvious, is opening the Process Security setup window, which opens automatically, when prompted by initiating configuration, is there is no clear way to open this window, after initial setup has been completed.  So, a shortcut to this window should be added to your home screen. 

This window can be accessed under Manufacturing > Setup > Process Security. I think it would be better to add a shortcut from this window.

To add a shortcut to your Home Screen, first, go to your Home Screen. Then right click in the Navigation Pane and select Add > Add Window.  

This will open up the Add Window Shortcut Window.  To Add the Process Security Setup window, you must browse to and select Manufacturing > 3rd Party > Process Security Setup (Alphabetically) and click Add.

Earlier I mentioned the reason this feature fell outside normal GP System Security setup, was due to the fact Manufacturing was once a 3rd party application - you will note, many of the Manufacturing Windows are still listed as 3rd party.  This is something to be aware of when working with Manufacturing Windows, Reports and the like.

Now that you've added this shortcut, you're ready to move forward using this feature.

Printing BOM Notes on Reports

How can we insert brief instructions or comments (i.e. Turn cap 5 times, do not over-tighten) into our Bill of Materials?  Rather than just printing an assembly with all the components necessary to build an item, we want to add a few comments to make the items consistent across all assemblers. 

Possibly the easiest way to accomplish such a task would be to add a note to the Bill of Material in the Bill of Material Entry window. There is quite a bit of room in the Notes field for instructions. 

Getting the notes to print on the desired report can be a little perplexing because the link is a bit convoluted. The notes text is stored in the Manufacturing Notes table [MN010000]. The Bill of Material Master Record is stored in the Bill of Material Header table [BM010415]. The Note Index is stored in the Item Engineering Data [IVR10015] able, so there can only be one Note for all Bill of Material (regardless of Category).

Here is a query to connect the data:

select

BH.ITEMNMBR Item#,

IM.ITEMDESC Item_Description,

MNF.NOTETEXT_I BOM_Note

from BM010415 BH 

-- BH is BOM Header BM010415

Left Join IVR10015 IED on BH.ITEMNMBR = IED.ITEMNMBR 

-- IED is Item Engineering Data IVR10015

Left Join MN010000 MNF on IED.MFGNOTEINDEX_I = MNF.NOTEINDX 

-- MNF is Manufacturing Note File MN010000

Left Join IV00101 IM on BH.ITEMNMBR = IM.ITEMNMBR 

-- IM is Item Master IV00101

Where BH.ITEMNMBR = '100XLGA' and BH.BOMCAT_I = 1 

-- BOMCAT_I = 1 is Manufacturing BOM

/* BOM Notes are linked to the BOM record through the Item Engineering Data Table (IVR10015)

The MFGNOTEINDEX_I field value maps to the NOTEINDX field in the Manufacturing Notes File MN010000*/

Resolving Dynamics GP General Ledger Account Indexes to Account Numbers in SQL Queries

Many records in Dynamics GP contain references to accounts. As an example, master records like Items, Customers, Vendors, Fixed Assets have default posting accounts. If you use tools like SQL Server Reporting Services (SSRS) and / or SQL Server Management Studio (SSMS) you have probably noticed values for Account Numbers in most GP Tables are stored as account indexes (in many tables ACTINDX).

Since Dynamics GP accounts can be up to 66 characters long with 10 segments, developers saved precious space by storing account indexes rather than the full Account Number in most tables.  The challenge of presenting the actual account number in queries and reports where tables contain only account indexes requires a solution. Enter the GL00105 Table (Account Index Master). This table contains the Account Index, Segments and full Account Numbers.

Using this table and Left Joins, it is possible to display the Account Number, where only an Account Index exists. The following query is an example of a complicated query meant to demonstrate the usage of joins in concert with the Account Index Master table (GL00105). This query returns the default posting accounts, and alternate posting accounts for Vendors in Dynamics GP.

Select

V.VENDORID Vendor_ID,

V.VENDNAME Vendor_Name,

PMAP.ACTNUMST AP,

PMCSH.ACTNUMST Cash,

PMDAV.ACTNUMST Discounts_Avail,

PMDTK.ACTNUMST Discounts_Taken,

PMFIN.ACTNUMST Finance_Charges,

PMMSCH.ACTNUMST Miscellaneous,

PMFRT.ACTNUMST Freight,

PMTAX.ACTNUMST Tax,

PMWRT.ACTNUMST Writeoffs,

PMPRCH.ACTNUMST Purchases,

PMTDSC.ACTNUMST Trade_Discount,

ACPUR.ACTNUMST Accrued_Purchases,

PURPV.ACTNUMST Purchase_Price_Variance,

GLD.ACTNUMST Alternate_Payables_Acct

from PM00200 V

LEFT join PM00203 PA on V.VENDORID = PA.VENDORID

Left join GL00105 PMAP on PMAP.ACTINDX = V.PMAPINDX

Left Join GL00105 PMCSH on PMCSH.ACTINDX = V.PMCSHIDX

Left Join GL00105 PMDAV on PMDAV.ACTINDX = V.PMDAVIDX

Left Join GL00105 PMDTK on PMDTK.ACTINDX = V.PMDTKIDX

Left Join GL00105 PMFIN on PMFIN.ACTINDX = V.PMFINIDX

Left Join GL00105 PMMSCH on PMMSCH.ACTINDX = V.PMMSCHIX

Left Join GL00105 PMFRT on PMFRT.ACTINDX = V.PMFRTIDX

Left Join GL00105 PMTAX on PMTAX.ACTINDX = V.PMTAXIDX

Left Join GL00105 PMWRT on PMWRT.ACTINDX = V.PMWRTIDX

Left Join GL00105 PMPRCH on PMPRCH.ACTINDX = V.PMPRCHIX

Left Join GL00105 PMTDSC on PMTDSC.ACTINDX = V.PMTDSCIX

Left Join GL00105 ACPUR on ACPUR.ACTINDX = V.ACPURIDX

Left Join GL00105 PURPV on PURPV.ACTINDX = V.PURPVIDX

Left Join GL00105 GLD on PA.ACTINDX = GLD.ACTINDX

SQL Database Stuck in Restore State - SQL

Sometimes when a SQL Restore operation fails the database being restored can wind up stuck in a Restoring State. At this point, literally nothing can be done to the database, well almost nothing.

One common way this may occur is if a restore is attempted and the correct Recovery and Replace values are not selected by the user.

Doing a google.com search on the issue, I was directed to a stackoverflow. Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

Relevant stackoverflow article

I referenced the article with an ulterior motive. You will note, if you review the link, there were a number of posts related to this issue, but only one is marked as a verified answer. Indeed this post resolved the issue. So when participating in forums, and asking questions, it is important to the community to mark any verified answer as helpful.

The best way to get a database out of the restore state is to complete the restore operation. To do this, use the correct restore options; see the query below:

RESTORE DATABASE YourDB

   FROM DISK = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\YourDB.bak'

   WITH REPLACE,RECOVERY

Purchase Order Cannot be Received after GP Crash During PO Receipt

A user who was in the middle of receiving a PO and GP 2013 crashed. Assuming the transaction would have been rolled back; the user logged back in and went to receive the PO, but received the following message "All lines have been received".

Looking at the PO showed nothing received and status of PO was Released.

Analysis of the POP10500 table, showed a receipt which does not exist in GP and status is still ZERO but quantity shipped shows data. Reconciling the PO does not resolve the issue.

Since the issue appeared to be caused by the errant receipt in the POP10500 table the first approach is to use the system to solve the problem. Try to open the Receipt in Dynamics GP and delete it. If this does not work, then there is one more possibility for an in system fix - Check Links.
Check Links is a maintenance tool, which validates the data in GP tables, and removes or recreates information based on what it finds. Goto Microsoft Dynamics GP > Maintenance > Check Links.  Change the series to Purchasing and then select the Purchasing Transactions table, click Insert and then click OK to process. Alternatively you can insert all Purchasing Tables. Then click Process. Check Links will produce a report of changes made to the system. Often times, these changes will make it possible to access transaction in GP again, edit, delete or post them as appropriate.

If Check Links doesn't resolve the issue, the next approach would be to delete the specific Receipt in the POP10500 by using the following SQL Script

DELETE POP10500 WHERE POPRCTNM='XXXX'

Note replace the 'XXXX' with the actual receipt number.  

Finally, run Check Links a second time to be sure the deleted record doesn't cause any problems, and then try to receive your PO.

If removing the errant record from the PO Receipt table does not resolve the issue. You can use the following scripts to analyze the Purchase Order itself to determine if the problem is the PO Line Status. If POLNESTA (PO Line Status) is set to Received (4), it will need to be reset to Released (2), in order to receive against it.

/* Set the PO Number below by changing 'XXXX' */

DECLARE @PONumber as VARCHAR(15)

SET @PONumber = 'XXXX'

select

POH.PONUMBER,

CASE POH.POSTATUS

      WHEN 1 THEN 'New'

      WHEN 2 ThEN 'Released'

      WHEN 3 THEN 'Change Order'

      WHEN 4 THEN 'Received'

      WHEN 5 THEN 'Closed'

      WHEN 6 THEN 'Cancelled'

      ELSE 'ERROR'

END PO_Status,

POL.LineNumber,

CASE POL.POLNESTA

      WHEN 1 THEN 'New'

      WHEN 2 THEN 'Released'

      WHEN 3 THEN 'Change Order'

      WHEN 4 THEN 'Received'

      WHEN 5 THEN 'Closed'

      When 6 THEN 'Cancelled'

      ELSE 'ERROR'

END PO_Line_Status,

POH.VENDORID Vendor_ID,

POH.VENDNAME Vendor_Name,

POL.ITEMNMBR Item_Number,

POL.ITEMDESC Item_Description,

* from POP10100 POH

Inner Join POP10110 POL on POH.PONUMBER = POL.PONUMBER

WHERE POH.PONUMBER = @PONumber

/* Run these queries only to change the PO Header and PO Line Status back to Released

Change 'XXXX' to the target PO Number prior to running these queries  */

--Update POP10110 set POLNESTA = '2' WHERE PONUMBER = 'XXXX'

How To Enable the Copier Series in Dynamics GP; can't find Copy Button in SOP, POP or Inventory

KB Article 936563

This article describes how to enable the Copy button in the Sales Transaction Entry window, in the Purchase Order Entry window, and in the Item Maintenance window in Microsoft Dynamics GP. To use the Copy button, you must grant access to certain fields. This article describes how to grant access to these fields.

In versions that are earlier than Microsoft Business Solutions-Great Plains 8.0, if the Copy button is not enabled, you must install a chunk file to enable the Copy button in specific windows. In Microsoft Business Solutions-Great Plains 8.0, in Microsoft Dynamics GP 9.0, and in Microsoft Dynamics GP 10.0, the Copier Series is integrated with the code. Therefore, you do not have to install the chunk file. However, you must grant access to the Copier Series for the Copy button for specific windows in Sales Order Processing, in Purchase 

Order Processing, and in Inventory.

To determine the fields that have to be enabled, follow these steps.

Microsoft Dynamics GP 9.0 and earlier versions

1.      Determine whether the Copier Series is enabled. To do this, point to Customize on the Tools menu, and then clickCustomization Status.

2.      Determine whether access is granted for the Copier Series. To do this, follow these steps:

a.      On the Tools menu, point to Setup, point to System, and then click Security.

b.      If you are prompted, type the system password in the Please Enter Password box, and then click OK.

Note If you receive the following message, click No:

Security and Advanced Security are both installed. You should use only the Security Setup window or Advanced Security window to change user security settings. Would you like to open Advanced Security?

c.      In the Company list, click the company that must have the Copy button enabled.

d.      In the User ID list, click the ID of the user who must have the Copy button enabled.

e.      In the Product list, click Copier Series.

f.       In the Type list, click Windows.

g.      In the Series list, click Sales to enable the Copy button in the Sales Transaction Entry window.

h.      Under Access List, double-click Copy a Sales Order.

i.        In the Series list, click Purchasing to enable the Copy button in the Purchase Order Entry window.

j.        Under Access List, double-click Copy a Purchase Order.

k.      In the Series list, click Inventory to enable the Copy button in the Item Maintenance window.

l.        Under Access List, double-click Item Copy.

m.    Click OK.

Note An asterisk appears next to the option that you selected under Access List.

Microsoft Dynamics GP 10.0 and Newer

1.      Determine whether the Copier Series is enabled. To do this, point to Tools on the Microsoft Dynamics GP menu, point toCustomize, and then click Customization Status.

2.      Determine whether access is granted for the Copier Series. To do this, follow these steps:

a.      On the Microsoft Dynamics GP menu, point to Tools, point to Setup, point to System, and then click User Security.

b.      If you are prompted, type the system password in the Please Enter Password box, and then click OK.

c.      In the User field, click the magnifying glass icon, and then select the User ID that must have the Copy button enabled.

d.      In the Company list, click the company that must have the Copy button enabled.

e.      Double-click the security role ID that must have the Copy button enabled.

f.       In the Security Role Setup window, double-click the security task ID that is created for the user.

g.      In the Security Task Setup window, double-click the security task ID that is created for the user.

h.      In the Product list, click Copier Series.

i.        In the Type list, click Windows.

j.        In the Series list, click Sales to enable the Copy button in the Sales Transaction Entry window.

k.      Under Access List, click the box next to Copy a Sales Order.

l.        In the Series list, click Purchasing to enable the Copy button in the Purchase Order Entry window.

m.    Under Access List, click the box next to Copy a Purchase Order.

n.      In the Series list, click Inventory to enable the Copy button in the Item Maintenance window.

o.      Under Access List, click the box next to Item Copy.

CASE to convert MOP10213.MO_Activity_Reason_I to usable text values - SQL

I have amassed a number of useful CASE statements, which can be incorporated into SQL queries run against Dynamics GP. When Querying the Manufacturing Order Activity Reason Code field in Dynamics GP AKA (MO_Activity_Reason_I ), use the following case statement to return text versions of the default statuses in Dynamics GP - if the code is not correctly translated the code value will be returned.

CASE MOP10213.MO_ACTIVITY_REASON_I
WHEN 17 THEN 'Scheduled' --Odd but sometimes this value is used

WHEN 31 THEN 'Status Change'

WHEN 32 THEN 'Allocate'

WHEN 33 THEN 'Reverse Allocate'

WHEN 34 THEN 'Issue'

WHEN 35 THEN 'Reverse Issue'

WHEN 36 THEN 'Scrap'

WHEN 37 THEN 'Reverse Scrap'

WHEN 38 THEN 'Raw Material Relief'

WHEN 39 THEN 'Finished Good Post'

WHEN 40 THEN 'Reverse Finished Good Post'

WHEN 41 THEN 'Close'

WHEN 42 THEN 'Complete'

WHEN 43 THEN 'Post Variance From WIP'

WHEN 44 THEN 'Labor Data Collection'

WHEN 45 THEN 'Machine Data Collection'

WHEN 46 THEN 'Outsourced Costs'

WHEN 47 THEN 'Scheduled'

WHEN 48 THEN 'Picklist Built'

WHEN 49 THEN 'Change Working Routing'

WHEN 50 THEN 'Financial Activity'

ELSE convert(Varchar(2),MOP10213.MO_ACTIVITY_REASON_I,1)

END AS Reason_Code

Is there a report to relate a General Ledger Journal Entry to a Manufacturing Order in Dynamics GP? - SQL

This information can be found by clicking on Inquiry > Manufacturing > Manufacturing Orders > Order Activity. This is a great screen, which a user can employ to drill back to the Journal Entry that was created by the transaction that is listed on this window. Once there, highlight the transaction and click the blue arrow on top tool bar of scrolling window to identify the Journal Entry. 

That being said, while this approach can get you from a particular Manufacturing Order to the related Journal Entry (Entries), it does not support rapid analysis of high transaction volumes. 

The following query links Inventory transactions in the Manufacturing Order Activity table MOP10213 to the Journal Entry information contained in the HITB table SEE30303 - obviously HITB must be deployed for this to work. Additionally, only detail transactions posted after the initialization of the HITB tool will be available for linking.

SELECT 

A.MANUFACTUREORDER_I MO_Number,

CASE A.MO_ACTIVITY_REASON_I

      WHEN 34 THEN 'Issue'

      WHEN 38 THEN 'Raw Material Relief'

      WHEN 39 THEN 'Finished Good Post'

      ELSE convert(Varchar(2),a.MO_ACTIVITY_REASON_I,1)

END AS Reason_Code,

CASE A.DOCTYPE

      WHEN 1 THEN 'IV Transaction'

      ELSE 'Not An Inventory Transaction'

END AS Document_Type,

B.DOCNUMBR Document_Number,

convert(varchar(10),B.DOCDATE,101) Document_Date,

B.ITEMNMBR Item_Number,

B.QTYBSUOM*B.TRXQTY AS Quantity,

C.JRNENTRY,

C.GLPOSTDT,

C.TRXREFERENCE

FROM [MOP10213] AS A

LEFT JOIN IV30300 AS B

ON A.IVDOCNBR = B.DOCNUMBR

Left Join SEE30303 as C

on B.DOCNUMBR = C.DOCNUMBR

WHERE A.DOCTYPE = 1

ORDER BY A.MANUFACTUREORDER_I, A.IVDOCNBR