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