Engineering Change Request View SQL Script

Since Dynamics GP Manufacturing originally began its life as a third party product, some of the core features supporting Manufacturing modules never made it into the product. For instance, Quality Assurance, Engineering Change Management, Routings, Forecasting and Job Costing do not have any out-of-the-box SmartLists.

I typically wind up using SQL Views, SmartList Builder and now SmartList Designer to create custom built SmartLists for these modules. In this post, I have included a SQL Script, which can be used to create a SQL View as the foundation for SmartLists or SQL Server Reporting Services (SSRS) Reports for Engineering Change Requests. Here it is:

Create VIEW Engineering_Change_Request

AS

select

DATEENTERED_I Date_of_Request,

CASE WHEN CUST.CUSTNMBR is not null then CUST.CUSTNAME

       Else  ''

End Customer_Name,

CASE WHEN CUST.CUSTNMBR is not null then CUST.CUSTNMBR

       ELSE ''

End Customer_ID,

ECMH.ENDDATE ECR_Complete_by_Date,

ECMH.ITEMNMBR Part_Number,

ECMH.ITEMDESC Part_Description,

'' Old_Rev,

ECMH.REVISIONLEVEL_I New_Rev,

ECMH.ECM_Short_Description ECR_Description,

DoEC.text1 BOM_Changes,

RFC.text2 Reason_for_Change,

NCO.text3 Notify_Customer,

EI.text4 Expected_Impact,

ECML.DISPOSITIONNOTES_I WIP_Instructions

from EC010031 ECMH

inner join EC050031 ECML on ECMH.ECNumber = ECML.ECNumber

left join RM00101 CUST on ECMH.CUSTNMBR = CUST.CUSTNMBR

left join EC010100 DoEC on ECMH.ECNumber = DoEC.ECNumber

left join EC010200 RFC on ECMH.ECNumber = RFC.ECNumber

left join EC010300 NCO on ECMH.ECNumber = NCO.ECNumber

left join EC010400 EI on ECMH.ECNumber = EI.ECNumber

GO

Grant Select on Engineering_Change_Request to DYNGRP  

Manufacturing Detail Labor Reporting

Dynamics GP has multiple mechanisms to record labor and machine transactions to calculate costs during manufacturing production. Time and Machine transactions can be entered through either the Time Card Entry or Data Collection Window. The tables populated by these two windows are different tables altogether.

Work In Process reporting and inquiry tools begin with the selection of the source of the data and provides the ability to sort and filter data by key fields, which makes WIP reporting intuitive from within Dynamics GP. 

That being said, it is not uncommon for users to desire very specific report designs to meet users' needs.  Additionally, SQL Server Reporting Services, Analysis Cubes and other tools are provided expressly because, not every constituent needs nor has access to Dynamic GP. 

Consequently, when generating tools-based reports on WIP labor detail, it is important to know which tool is being used to collect the data, and which tables contain the associated records.

The Data Collection window is a common way to enter labor (direct and indirect) as well as machine costs for work orders. With a simple wedge scanner and some well-placed barcode font, entering data in this window is fast and accurate. This approach is commonly used when there is a central location in the shop, where work order data is collected. The Data Collection window can be accessed using the following navigation: 

(Manufacturing > Transactions > WIP > Data Collection).

Data entered in the Data Collection window is stored in the SF010014 - Data Collection Master and SF010115 - Data Collection Line Items tables (most of the relevant data is stored in the SF010115 table). When joined to the UPR00100 - Employee Master and LC010014 - Labor Code Master tables, a complete picture of detailed labor transactions is achieved.  Here is a basic query, which returns all labor related transactions from the SF010115 table.

NOTE: The Labor Code Master table contains the Shop Rate for the Labor Code, which will be used if the Employee does not have a specific value assigned for the Labor Code in Payroll Setup. The query below assumes the shop rate is used.

-- Labor Detail when entered in Data Collection Window

Select

DCT.MANUFACTUREORDER_I

,DCT.RTSEQNUM_I

,CASE DCT.DATAENTRYTYPE_I

      when 1 then 'Direct_Labor'

      when 2 then 'Machine_Cost'

      when 3 then 'Indirect_Labor'

ELSE 'ERROR'

End Entry_Type   

,DCT.EMPLOYID Employee_ID

,RTRIM(RTRIM(EMP.LASTNAME)+', '+RTRIM(EMP.FRSTNAME)+' '+RTRIM(EMP.MIDLNAME)) Employee_Name

,LCD.COST_I ShopRate

,DCT.ELAPSEDTIME_I Elapsed_Time

,DCT.SEQUENCECOST_I Labor_Cost

,DCT.FIXOVERMARK_I Fixed_Overhead

,DCT.Variable_Overhead_Amount Variable_Overhead

,DCT.PIECES_I Pieces

,DCT.REJECTS_I Rejects

,DCT.PIECECOST_I Piece_Cost

,CONVERT(varchar(10),DCT.ACTUALFINISHDATE_I,101) Finish_Date

from SF010115 DCT

left join LC010014 LCD on DCT.LABORCODE_I = LCD.LABORCODE_I

Left join UPR00100 EMP on DCT.EMPLOYID = EMP.EMPLOYID

Where DCT.DATAENTRYTYPE_I in (1,3)

Alternatively, the Time Card Entry window can be used to enter Detailed Labor data into Dynamics GP. The Time Card Entry window is an employee based entry system, and is more frequently used when each work-center/employee has access to the window, and updates their time data throughout the day. The Time Card Entry window can be accessed using the following navigation:

(Manufacturing > Transactions > WIP > Time Card Entry)

Time Card Entry data is stored in the SF010600 - DC_Labor_Batch_Entry_Hdr and SF010601 - DC_Labor_Batch_Entry_Dtl (Data Collection Labor Batch Entry Header and Detail) tables. These tables contain a wealth of data and therefore do not require links to additional tables. 

-- Labor Detail when entered in Time Entry Window

select * from SF010600 TEH

left join SF010601 TED on TEH.DCHDRNUM_I = TED.DCHDRNUM_I

Feel free to reach out to me, should you have questions about Detailed Labor Reporting for WIP in Dynamics GP Manufacturing.

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

Foundation Query for Historical Fixed Asset Depreciation Reporting in SSRS or SLB

Prior to version 2013 of Dynamics GP there was not Historical Depreciation Reporting.  Here is a query, which can be used as a foundation for reporting on depreciation using SmartList Builder or SQL Server Reporting Services (SSRS)

DECLARE @Year INT, @StartPeriod TINYINT, @EndPeriod TINYINT

Set @Year = '2015' --Sets the Year of the Report

Set @StartPeriod = '1' --Sets the Starting Period of the Report

Set @EndPeriod = '12' --Sets the Ending Period of the Report

select

FAM.ASSETID Asset_ID,

FAM.ASSETIDSUF Asset_Suffix,

FAM.ASSETDESC Asset_Description,

GLT.FAPERIOD 'Period',

GLT.FAYEAR 'Year',

--GLT.FA_Doc_Number, --Removes this column, not in versions prior to 2013

convert(char(10),GLT.DEPRFROMDATE,101) Dep_From_Date,

convert(char(10),GLT.DEPRTODATE,101) Dep_To_Date,

GLD.ACTNUMST Account,

GLM.ACTDESCR Acct_Description,

cast(GLT.AMOUNT as money) Amount

from FA00902 GLT

left join FA00100 FAM on GLT.ASSETINDEX = FAM.ASSETINDEX

left join GL00100 GLM on GLT.GLINTACCTINDX = GLM.ACTINDX

left join GL00105 GLD on GLT.GLINTACCTINDX = GLD.ACTINDX

where FAYEAR = @Year

and FAPERIOD between @StartPeriod and @EndPeriod

Order by GLT.FAPERIOD,FAM.ASSETID,FAM.ASSETIDSUF,GLD.ACTNUMST

Deploying SSRS Reports for Dynamics GP

Checklist for deploying SSRS Reports:

1. Install SSRS.  SSRS is installed from SQL installation media. If SQL is already installed, from Programs and Features Select SQL and choose Change.

2. Set SSRS Service to recover automatically in case of failure - ensure it starts automatically.

3. SSRS requires IIS for versions prior to SQL 2008; for SQL 2005 and prior, make sure IIS is installed and enabled.

4. After installation, access SSRS with the user who installed it - only this user gets permissions by default. Under Site Settings > Security add users to their correct security groups.  Make sure you add the Domain Admins group as Administrators of SSRS.

5. If you cannot access SSRS through your browser, make sure you are starting your browser with administrator permissions.

6. If you still encounter difficulties accessing the SSRS site, make sure your SSRS site is added to your compatibility list or set your browser compatibility correctly - depends on the version.

Once SSRS services are running and accessible, make sure you deploy SSRS reports from GP. Here is a link to a really solid article on how to deploy SSRS Reports in Dynamics GP:

Nataraj on Dynamics GP SSRS deployment.