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

Fixed Asset Transaction Analysis - Transaction per day - SQL

Recently, I answered a forum post for a user who wanted to know the number of transactions by module entered in a given day.  I authored a series of queries which allow a user to input a date and execute the query. 

The queries identify the number of unique transactions and the number of lines in the table. Dividing one by the other will reveal the average lines per transaction.  

The Fixed Asset tables presented a unique challenge.  Transactions are written to the FA00902 table, and have either been interfaced with the General Ledger or not, so there is no easy way to identify transactions for a specific date. 

I came up with an approach I think works best.  The following queries return Transactions Not Interfaced with the GL - these all have the same GL Interface Date (GLINTTRXDATE) value of 1900-01-01 00:00:00.000. Transactions that have already been interfaced with the GL can be identified by a specific date, but will generally have been interfaced at a month-end.

This query reveals the Number of Fixed Asset Transactions, which have not been interfaced with the GL and interfaced transactions on a given day, as well as the number of overall lines.

/* Fixed Asset Transactions NOT Interfaced with GL */

select COUNT(FA_Trxs) FA_Trxs from (

select count(FA_Doc_Number) FA_Trxs from FA00902

Where GLINTTRXDATE = '01/01/1900'

Group by FA_Doc_Number ) FAT

/* Fixed Asset Transaction Lines NOT Interfaced with GL */

Select COUNT(FA_Doc_Number) FA_Trx_Lines from FA00902

Where GLINTTRXDATE = '01/01/1900'

DECLARE @Date as DATETIME

Set @Date = '12/31/2016'

/* Fixed Asset Transactions Interfaced with GL */

select COUNT(FA_Trxs) FA_Trxs from (

select count(FA_Doc_Number) FA_Trxs from FA00902

Where GLINTTRXDATE = @Date

Group by FA_Doc_Number ) FAT

/* Fixed Asset Transaction Lines Interfaced with GL */

Select COUNT(FA_Doc_Number) FA_Trx_Lines from FA00902

Where GLINTTRXDATE = @Date