Friday, December 19, 2014

Dynamics GP - Payables Transaction Inquiry error "The stored procedure createSQLTmpTable returned the following results: DBMS: 2627, Microsoft Dynamics GP: 0"

Problem:
When opening a transaction inquiry window to view transactions for a vendor, you get the following error for some vendors.
"The stored procedure createSQLTmpTable returned the following results: DBMS: 2627, Microsoft Dynamics GP: 0"

Cause:
This is caused by a record existing in both the Work AND History Tables.
This generally happens when a batch gets stuck, or half-posts and has to be recovered using the script to force-recover it. Some transactions may post to history, but not get cleaned out of the Work Table.

Solution:
Run this


select *
--delete
from pm20000
where
VENDORID in ('MyAffectedVendorID') and
DOCNUMBR in (
select DOCNUMBR  from pm30200)

Wednesday, December 10, 2014

Dynamics GP - Simplified SP to Load seermHATBwrapper into a temp table

--Create Temp Table
/****** Object:  Table [dbo].[BI_HATB_Temp]    Script Date: 12/10/2014 2:47:18 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BI_HATB_Temp](
[APPLY_AMOUNT] [numeric](19, 5) NOT NULL,
[AGING_AMOUNT] [numeric](19, 5) NOT NULL,
[CUSTNMBR] [char](15) NOT NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CUSTNAME] [char](65) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [BALNCTYP] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [USERDEF1] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CNTCPRSN] [char](61) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [PHONE1] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [SLPRSNID] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [SALSTERR] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [PYMTRMID] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CRLMTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CRLMTPER] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CRLMTPAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CRLMTTYP] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CUSTCLAS] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [SHRTNAME] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ZIP] [char](11) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [STATE] [char](29) NOT NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CUDSCRIPTN] [char](31) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AGNGDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CHCUMNUM] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DOCNUMBR] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [RMDTYPAL] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DSCRIPTN] [char](31) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DCURNCYID] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORTRXAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CURTRXAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AGNGBUKT] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CASHAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [COMDLRAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [SLSAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [COSTAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [FRTAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [MISCAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [TAXAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DISAVAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DDISTKNAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DWROFAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [TRXDSCRN] [char](31) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DOCABREV] [char](3) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CHEKNMBR] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DOCDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DUEDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [GLPOSTDT] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DISCDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [POSTDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DINVPDOF] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DCURRNIDX] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DXCHGRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORCASAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORSLSAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORCSTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORDAVAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORFRTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORMISCAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORTAXAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORCTRXAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORORGTRX] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DORDISTKN] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DORWROFAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DDENXRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DMCTRXSTT] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [Aging_Period_Amount] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [APFRDCNM] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [APFRDCTY] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [FROMCURR] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [APTODCNM] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [APTODCTY] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [APPTOAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ACURNCYID] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DATE1] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [POSTED] [tinyint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ADISTKNAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AWROFAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [PPSAMDED] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [GSTDSAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ACURRNIDX] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AXCHGRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [RLGANLOS] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORAPTOAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AORDISTKN] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AORWROFAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ADENXRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AMCTRXSTT] [smallint] NOT NULL

GO

SET ANSI_PADDING OFF
GO

--Create Custom Stored Procedure to clear table and load new data into it
/****** Object:  StoredProcedure [dbo].[BI_Hatb_load]    Script Date: 12/10/2014 3:00:10 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[BI_Hatb_load]
@I_dAgingDate1 datetime     = NULL
AS
BEGIN
set nocount on
truncate table  bi_hatb_temp
insert into bi_hatb_temp
([APPLY_AMOUNT]
      ,[AGING_AMOUNT]
      ,[CUSTNMBR]
      ,[CUSTNAME]
      ,[BALNCTYP]
      ,[USERDEF1]
      ,[CNTCPRSN]
      ,[PHONE1]
      ,[SLPRSNID]
      ,[SALSTERR]
      ,[PYMTRMID]
      ,[CRLMTAMT]
      ,[CRLMTPER]
      ,[CRLMTPAM]
      ,[CRLMTTYP]
      ,[CUSTCLAS]
      ,[SHRTNAME]
      ,[ZIP]
      ,[STATE]
      ,[CUDSCRIPTN]
      ,[AGNGDATE]
      ,[CHCUMNUM]
      ,[DOCNUMBR]
      ,[RMDTYPAL]
      ,[DSCRIPTN]
      ,[DCURNCYID]
      ,[ORTRXAMT]
      ,[CURTRXAM]
      ,[AGNGBUKT]
      ,[CASHAMNT]
      ,[COMDLRAM]
      ,[SLSAMNT]
      ,[COSTAMNT]
      ,[FRTAMNT]
      ,[MISCAMNT]
      ,[TAXAMNT]
      ,[DISAVAMT]
      ,[DDISTKNAM]
      ,[DWROFAMNT]
      ,[TRXDSCRN]
      ,[DOCABREV]
      ,[CHEKNMBR]
      ,[DOCDATE]
      ,[DUEDATE]
      ,[GLPOSTDT]
      ,[DISCDATE]
      ,[POSTDATE]
      ,[DINVPDOF]
      ,[DCURRNIDX]
      ,[DXCHGRATE]
      ,[ORCASAMT]
      ,[ORSLSAMT]
      ,[ORCSTAMT]
      ,[ORDAVAMT]
      ,[ORFRTAMT]
      ,[ORMISCAMT]
      ,[ORTAXAMT]
      ,[ORCTRXAM]
      ,[ORORGTRX]
      ,[DORDISTKN]
      ,[DORWROFAM]
      ,[DDENXRATE]
      ,[DMCTRXSTT]
      ,[Aging_Period_Amount]
      ,[APFRDCNM]
      ,[APFRDCTY]
      ,[FROMCURR]
      ,[APTODCNM]
      ,[APTODCTY]
      ,[APPTOAMT]
      ,[ACURNCYID]
      ,[DATE1]
      ,[POSTED]
      ,[ADISTKNAM]
      ,[AWROFAMNT]
      ,[PPSAMDED]
      ,[GSTDSAMT]
      ,[ACURRNIDX]
      ,[AXCHGRATE]
      ,[RLGANLOS]
      ,[ORAPTOAM]
      ,[AORDISTKN]
      ,[AORWROFAM]
      ,[ADENXRATE]
      ,[AMCTRXSTT]
 )


exec seermHATBSRSWrapper @I_dAgingDate=@I_dAgingDate1,@I_cStartCustomerNumber=N'',@I_cEndCustomerNumber=N'',@I_cStartCustomerName=N'',@I_cEndCustomerName=N'',@I_cStartClassID=N'',@I_cEndClassID=N'',@I_cStartSalesPersonID=N'',@I_cEndSalesPersonID=N'',@I_cStartSalesTerritory=N'',@I_cEndSalesTerritory=N'',@I_cStartShortName=N'',@I_cEndShortName=N'',@I_cStartState=N'',@I_cEndState=N'',@I_cStartZipCode=N'',@I_cEndZipCode=N'',@I_cStartPhoneNumber=N'',@I_cEndPhoneNumber=N'',@I_cStartUserDefined=N'',@I_cEndUserDefined=N'',@I_tUsingDocumentDate=0,@I_dStartDate='1900-01-01 00:00:00',@I_dEndDate='2014-12-09 00:00:00',@I_sIncludeBalanceTypes=0,@I_tExcludeNoActivity=1,@I_tExcludeMultiCurrency=1,@I_tExcludeZeroBalanceCustomer=1,@I_tExcludeFullyPaidTrxs=1,@I_tExcludeCreditBalance=0,@I_tExcludeUnpostedAppldCrDocs=1,@I_tConsolidateNAActivity=0

select * from BI_HATB_TEMP
END


GO



--Execute BI_HATB_TEMP with one parameter, aging date

exec bi_hatb_load @I_dAgingDate1='2014-12-31 00:00:00'

Tuesday, December 9, 2014

Dynamics GP - SQL command to run the Historical Aged Trial Balance - seermhatbsrswrapper

exec seermHATBSRSWrapper @I_dAgingDate='2014-12-31 00:00:00',@I_cStartCustomerNumber=N'',@I_cEndCustomerNumber=N'',@I_cStartCustomerName=N'',@I_cEndCustomerName=N'',@I_cStartClassID=N'',@I_cEndClassID=N'',@I_cStartSalesPersonID=N'',@I_cEndSalesPersonID=N'',@I_cStartSalesTerritory=N'',@I_cEndSalesTerritory=N'',@I_cStartShortName=N'',@I_cEndShortName=N'',@I_cStartState=N'',@I_cEndState=N'',@I_cStartZipCode=N'',@I_cEndZipCode=N'',@I_cStartPhoneNumber=N'',@I_cEndPhoneNumber=N'',@I_cStartUserDefined=N'',@I_cEndUserDefined=N'',@I_tUsingDocumentDate=0,@I_dStartDate='1900-01-01 00:00:00',@I_dEndDate='2014-12-09 00:00:00',@I_sIncludeBalanceTypes=0,@I_tExcludeNoActivity=1,@I_tExcludeMultiCurrency=1,@I_tExcludeZeroBalanceCustomer=1,@I_tExcludeFullyPaidTrxs=1,@I_tExcludeCreditBalance=0,@I_tExcludeUnpostedAppldCrDocs=1,@I_tConsolidateNAActivity=0

Monday, December 8, 2014

Friday, November 28, 2014

Dynamics GP - SQL View - All Posted and Unposted GL Transactions Detail and Summary by period and account

See Original view here
http://victoriayudin.com/2011/04/27/sql-view-for-all-gl-transactions-in-dynamics-gp/


--All GL Details
----------------------------------------------------------------------------------------------------------
SELECT     GL.Trx_Status, GL.TRXDATE AS Trx_Date, GL.JRNENTRY AS Journal_Entry, GM.ACTNUMST AS Account_Number,
                      GA.ACTDESCR AS Account_Description, GL.DEBITAMT AS Debit_Amount, GL.CRDTAMNT AS Credit_Amount, GL.REFRENCE AS Reference,
                      GL.SOURCDOC AS Source_Document, GL.ORTRXSRC AS Originating_TRX_Source, GL.ORMSTRID AS Originating_Master_ID,
                      GL.ORMSTRNM AS Originating_Master_Name, GL.ORDOCNUM AS Originating_Doc_Number, GL.CURNCYID AS Currency_ID,
                      GL.LASTUSER AS Last_User, GL.USWHPSTD AS User_Who_Posted, GL.OPENYEAR AS GLYear, GL.PERIODID AS GLPeriod
FROM         (SELECT     ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT,
                                              CURNCYID, 'Open' AS Trx_Status, LASTUSER, USWHPSTD, OPENYEAR, PERIODID
                       FROM          dbo.GL20000
                       WHERE      (SOURCDOC NOT IN ('BBF', 'P/L')) AND (VOIDED = 0)
                       UNION ALL
                       SELECT     ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT,
                                             CURNCYID, 'History' AS Trx_Status, LASTUSER, USWHPSTD, HSTYEAR, PERIODID
                       FROM         dbo.GL30000
                       WHERE     (SOURCDOC NOT IN ('BBF', 'P/L')) AND (VOIDED = 0)
                       UNION ALL
                       SELECT     GD.ACTINDX, GH.TRXDATE, GH.SOURCDOC, GH.JRNENTRY, GH.ORTRXSRC, GH.REFRENCE, GD.ORDOCNUM, GD.ORMSTRID,
                                             GD.ORMSTRNM, GD.DEBITAMT, GD.CRDTAMNT, GH.CURNCYID, 'Work' AS Trx_Status, GH.LASTUSER, GH.USWHPSTD, GH.OPENYEAR,
                                             GH.PERIODID
                       FROM         dbo.GL10000 AS GH INNER JOIN
                                             dbo.GL10001 AS GD ON GH.JRNENTRY = GD.JRNENTRY
                       WHERE     (GH.VOIDED = 0)) AS GL INNER JOIN
                      dbo.GL00105 AS GM ON GL.ACTINDX = GM.ACTINDX INNER JOIN
                      dbo.GL00100 AS GA ON GL.ACTINDX = GA.ACTINDX

--All GL Summary
---------------------------------------------------------------------------------------------------------------------
SELECT     GLYear, GLPeriod, Account_Number, Account_Description, SUM(Debit_Amount) AS Debit, SUM(Credit_Amount) AS Credit,
                      SUM(Debit_Amount - Credit_Amount) AS Total
FROM         dbo.BI_GL_Trx
GROUP BY Account_Number, Account_Description, GLYear, GLPeriod

Thursday, November 27, 2014

Dynamics GP - SQL VIEW - PO, Received status, Invoiced status

SELECT     POP10100.PONUMBER, a.POPRCTNM, a.VNDDOCNM, a.APStatus, a.BCHSOURC, a.VCHRNMBR, PM00200.VENDORID, PM00200.VENDNAME,
                      CASE WHEN poprctnm IS NOT NULL THEN 'Received' ELSE 'Not Received' END AS RctStatus, CASE WHEN vchrnmbr IS NOT NULL
                      THEN 'Invoiced' ELSE 'Not Invoiced' END AS InvStatus
FROM         POP10100 LEFT OUTER JOIN
                      PM00200 ON POP10100.VENDORID = PM00200.VENDORID LEFT OUTER JOIN
                          (SELECT     'Work' AS APStatus, POP10300.POPRCTNM, POP10300.VNDDOCNM, POP10300.BCHSOURC, POP10310.RCPTLNNM,
                                                   POP10310.PONUMBER, POP10300.VENDORID, POP10300.VCHRNMBR, POP10310.ITEMDESC, POP10310.ITEMNMBR
                            FROM          POP10300 INNER JOIN
                                                   POP10310 ON POP10300.POPRCTNM = POP10310.POPRCTNM
                            UNION
                            SELECT     'Posted' AS APstatus, POP30300.POPRCTNM, POP30300.VNDDOCNM, POP30300.BCHSOURC, POP30310.RCPTLNNM,
                                                  POP30310.PONUMBER, POP30300.VENDORID, POP30300.VCHRNMBR, POP30310.ITEMDESC, POP30310.ITEMNMBR
                            FROM         POP30300 INNER JOIN
                                                  POP30310 ON POP30300.POPRCTNM = POP30310.POPRCTNM) AS a ON POP10100.PONUMBER = a.PONUMBER
GROUP BY a.APStatus, a.POPRCTNM, a.VNDDOCNM, a.BCHSOURC, a.VCHRNMBR, POP10100.PONUMBER, PM00200.VENDORID, PM00200.VENDNAME

Wednesday, November 26, 2014

Dynamics GP - Report Writer - Display all Comment Lines, even if the comment is larger than 4 lines with Autogrow

Normally this cannot be done.
However, if you switch the report to Text, and use the Comment field (not the array) it will display the full value of the field, and grow and shrink automatically.

Dynamics GP - How to copy / migrate an AFA Financial Report from one company to another



  1. Find the next available AFA Report ID in the new company
    1. select * from AF40100
    2. The next id is the next available number
  2. Copy all data from the following tables
    1. AF40101, AF40102, AF40103, AF40104, AF40105, AF40106, AF40107, AF40108, AF40200, and AF40201
  3. Replace the report id with the new report ID in the new company
    1. update af40101 set reportid = [newid] where reportid = [oldid]
You can export the data from pervasive by browsing to each table and exporting to a data files.
However, pervasive will export an additional column of 0's before each actual column of data.
After cleaning, the first column from pervasive is not needed.
you must clean the data and remove these columns to import the data into SQL.
Once cleaned, you can copy and paste directly into the AFA tables.


See original solution here
http://support.microsoft.com/kb/863224

Tuesday, November 25, 2014

Dynamics NAV - How to import New company Opening Balances

On each account card that you want to update directly, enable the "Direct Posting" tickbox on the account card. This allows direct posting to the account without a subledger transaction.

For AR and AP balances, setup the initial posting groups to debit and credit the same accounts so there is a net 0 movement on the account. The actual balances can then be entered as journals if you tick the "Direct Posting" for those accounts

For Bank and Inventory Balances
http://www.dynamicsnavconsultant.com/tag/beginning-balance/

Solution here
http://support.microsoft.com/kb/944227

Tuesday, November 18, 2014

Dynamics NAV 2013 R2 - How to Create a new Company


  • Open NAV RTC
  • Search for Companies
    • Administration>IT Administration>Companies
  • Add new company
  • Switch companies to login to the new company
    • Company will initialize
Populate with default data
  • Export these packages from an existing company, then import them to your new company
    • NOSERIES
    • LS DISTRIBUTION (Only required if using LS Retail in new company)
    • BASICCONFIG
    • NAVBASIC
    • LS BASE (Only required if using LS Retail in new company)
Related Issues
  • "Customer has one of more open ledger entries" when trying to set the IC Partner code
    • Review the Customer Ledger Entries screen
      • Add a filter where open = yes
    • Enter a journal to close all of these transactions
  • "Vendor has one of more open ledger entries" when trying to set the IC Partner code
    • Review the Vendor Ledger Entries screen
      • Add a filter where open = yes
    • Enter a journal to close all of these transactions

Friday, November 14, 2014

Dynamics NAV - Rapidstart


  • How to Create a Template, insert Data and import to NAV
    • Create Package
    • Create Worksheet
    • Assign Worksheet to Package
    • Export Template to Excel from Worksheet or Package Table
    • Enter data to import into template
    • Import Data from Template
    • Apply Data

Tuesday, November 11, 2014

Dynamics GP - Check to Words shows extra 0's for cents. Check to Words is too long and is truncated at 80 characters.

Issue:
Payables check prints incorrect cents
If your currency has more than 2 decimal places, and you print checks, the check to Words function that prints the check amount in words on the check prints the cents with all of the zeroes for the number of decimal places the currency is set to.

Example. - 5 DP currency prints as

100.20 shows as One Hundred Dollars And 20000 Cents

Solution:

  • Open PSTL
  • Look under Misc Tools
  • Look for Decimal Place Tool
  • Tick the Box next to the Blue Tick (It will be a Red Tick after you tick it)


This will force the Check to Words to only display 2 decimal places in the description.

Original Solution
http://support.microsoft.com/kb/961423

However, this field is not on the cheque by default, you must add the "Amount in words" field from PM Payment work.
However, the "Amount in Words" field will throw an error if you use it directly.
Ignore it completely and instead, use the STRING A 255 Field. It will show the correct information once the PSTL tool has been installed.

If you want to wrap the field, you must use two (or more) calculated fields using the rw_parsestring function to split the string.
Then rw_parsestring against that calculated string.

  • CalcLine1 = RW_ParseString(Pmt_Work.STRING A 255 50  1)
  • CalcLine2 = RW_ParseString(Pmt_Work.STRING A 255 50  2)
OR you can use substring

  • CalcLine1 = RW_SubString(Pmt_Work.STRING A 255 1  60)
  • CalcLine2 = RW_SubString(Pmt_Work.STRING A 255 61  60)

Friday, November 7, 2014

Dynamics NAV - Install NAV 2013 R2 and LS Retail 7.10

This also works for NAV 2015 CU1 and LS Retail 8.01
  • Download the W1 install and W1 demo database media from 
  • Download LS Retail install 
  • Download Latest version of LS Retail Data Director
  • Extract DVD, Run setup
  • Choose an installation
    • If you choose install demo, it will install an instance of SQL 2012 Express with the demo company
      • Run the LS install, it will extract all the files you need
      • Restore the bak file over your demo company
        • If you cannot install the bak file, you can do a database upgrade to install the objects into the existing demo company
        • Open the demo database in the NAV dev environment
        • Upload your partner keys
        • Import the fob from the ls retail>objects directory
        • Set SQL user as db_owner in SQL Management Studio
        • Add current user to database in NAV Client
        • Compile all objects
        • Build server application objects
    • If you choose custom
      • Install All Modules (Deselect what you don't need)
  • Install the LS Toolboxes
    • Navigate to LS Retail\LS7.10.01\Setup\LS Nav 2013 (7.10.01) Toolbox
    • Install the Client Components
    • Install the Server Components
  • Install Data Director
    • Install Data Director>Tick 2013 RTC and 2013 SRV
    • Run the Data Director Configuration Tool>Click Get Config
  • Launch the Development Environment
    • Open Database
    • Click All
    • Click Tools>Compile
    • Click Tools>Build Server Application Objects
  • Launch Dynamics NAV Client
    • Search for Users
    • Add current user and any other users
  • Restart NAV service
  • Launch NAV, and use LS Retail
  • Run the LS Help installer from Installs>LS>LS>Online Help

How to Install the POS
  • Launch NAV
  • Search for the "Import Export Worksheet"
  • For Each Profile (Functionality, Hardware, Menu, Interface, Style)
    • Select the profile
    • On the top bar, Click Import
    • Select the LS Rapidstart Profile xml file
      • LS7.10\LS Retail\LS7.10.01\Setup\LS Nav 2013 (7.1) Rapid Start
  • For Each Terminal, ensure that the Functionality Profile and Hardware profile are set to ##DEFAULT
  • Configure Data Director to push all data to POS location
  • Go to physical POS device
    • Install NAV server and client
    • Install LS retail and toolboxes
    • Install Data director
    • Create retail user on that machine
      • Give access to only that store and terminal
  • Log in to main server
    • Find job in LS scheduler
    • Run job to push data
  • Create shortcut that will run the "Run Client" which will load the POS screen
How to import base NAV and LS configuration data

  • Search for Import Export Worksheet
    • For each line on the worksheet, Import the profile.xml in the LS Retail setup
  • Import and apply all Rapidstart Packages
  • Update Packages as necessary



Issues:
  • Nav service starts, but then stops after a while
    • Install the database components on the SQL server
    • You did not install NAV as an administrator
    • Ensure you are a local administrator, delete the demo database, run NAV setup and repair NAV.
    • Disable the firewall on the database server and the application server or open the relevant ports
  • You do not have permission to create a database
    • Do not select any database components, and install all other components with no sql connection data
    • Create the instance manually in the admin console after the install completes

Thursday, November 6, 2014

Dynamics GP - Voided Sales transactions appear in Historical Stock Status Report

The Historical Stock Status Report and the Item Stock Inquiry read their data from the IV30300 table.

Whenever a transaction is voided, the corresponding record is supposed to be removed from the IV30300 table.

If records are showing on your Historical Stock Status that should not be there, the offending records need to be removed.

Look for any doc number and item number in SOP30300 that is not in IV30300
Also look for any Doc numbers in IV30300 with multiple Trxsorce values. Chances are, the lowest trxsorce values are supposed to be removed.

--delete voided SOP30300 stuck in IV30300
--------------------------------------------------------------
delete from iv30300 where dex_row_id in (
select distinct dex_row_id from(
SELECT     IV30300.TRXSORCE, IV30300.DOCTYPE, IV30300.DOCNUMBR, IV30300.DOCDATE, IV30300.HSTMODUL, IV30300.CUSTNMBR, IV30300.ITEMNMBR, IV30300.LNSEQNBR, IV30300.UOFM,
                      IV30300.TRXQTY, IV30300.UNITCOST, IV30300.EXTDCOST, IV30300.TRXLOCTN, IV30300.TRNSTLOC, IV30300.TRFQTYTY, IV30300.TRTQTYTY, IV30300.IVIVINDX, IV30300.IVIVOFIX,
                      IV30300.DECPLCUR, IV30300.DECPLQTY, IV30300.QTYBSUOM, IV30300.Reason_Code, IV30300.DEX_ROW_ID, SOP30200.VOIDSTTS
FROM         SOP30200 INNER JOIN
                      SOP30300 ON SOP30200.SOPTYPE = SOP30300.SOPTYPE AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE INNER JOIN
                      IV30300 ON SOP30300.SOPNUMBE = IV30300.DOCNUMBR AND SOP30300.ITEMNMBR = IV30300.ITEMNMBR
WHERE     (SOP30200.VOIDSTTS = 1)
) as a)


--Delete IV30300 that have no corresponding SOP30300 lines
-------------------------------------------------------------------

delete from iv30300 where dex_row_id in (
SELECT    IV30300.DEX_ROW_ID
FROM         (SELECT DISTINCT TOP (100) PERCENT DOCNUMBR, COUNT(ITEMNMBR) AS Expr1, DOCTYPE, ITEMNMBR, MIN(TRXSORCE) AS Expr2
                       FROM          (SELECT DISTINCT TOP (100) PERCENT DOCNUMBR, DOCTYPE, TRXSORCE, ITEMNMBR
                                               FROM          IV30300 AS IV30300_1
                                               GROUP BY DOCDATE, ITEMNMBR, DOCNUMBR, DOCTYPE, TRXSORCE
                                               ORDER BY DOCNUMBR) AS derivedtbl_1
                       GROUP BY DOCNUMBR, DOCTYPE, ITEMNMBR
                       HAVING      (DOCTYPE = 6) AND (COUNT(ITEMNMBR) > 1)
                       ORDER BY DOCNUMBR, ITEMNMBR) AS b INNER JOIN
                      IV30300 ON b.DOCTYPE = IV30300.DOCTYPE AND b.DOCNUMBR = IV30300.DOCNUMBR AND b.ITEMNMBR = IV30300.ITEMNMBR AND b.Expr2 = IV30300.TRXSORCE
                      )

Friday, October 31, 2014

Dynamics GP - View - EFT Vendors

SELECT     SY06000.VENDORID, PM00200.VENDNAME, PM00200.ADDRESS1, PM00200.ADDRESS2, PM00200.ADDRESS3, PM00200.CITY, PM00200.STATE, PM00200.VNDCNTCT, PM00200.ZIPCODE, PM00200.COUNTRY, PM00200.PHNUMBR1, PM00200.PHNUMBR2,
                  PM00200.PHONE3, PM00200.FAXNUMBR, PM00200.VNDCLSID, SY06000.EFTBankAcct, SY06000.EFTBankBranch, SY06000.EFTBankCode, SY06000.EFTBankBranchCode, SY06000.EFTBankType, SY06000.FRGNBANK, SY06000.BANKNAME,
                  SY06000.EFTBankCheckDigit, SY06000.IntlBankAcctNum, SY06000.CustVendCountryCode, SY06000.DeliveryCountryCode, SY06000.BNKCTRCD, SY06000.CBANKCD, SY06000.ADDRESS1 AS Expr1, SY06000.ADDRESS2 AS Expr2, SY06000.ADDRESS3 AS Expr3,
                  SY06000.ADDRESS4, SY06000.RegCode1, SY06000.RegCode2, SY06000.BankInfo7, SY06000.EFTTransitRoutingNo, SY06000.CURNCYID, SY06000.EFTTransferMethod, SY06000.EFTAccountType, SY06000.EFTTerminationDate, SY06000.ADRSCODE
FROM        SY06000 INNER JOIN
                  PM00200 ON SY06000.VENDORID = PM00200.VENDORID

Thursday, October 30, 2014

Dynamics GP - View to reconcile Bank Transactions to GL

--------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_BankTrx_All]
AS

SELECT     a.CMRECNUM, a.sRecNum, a.RCRDSTTS, a.CHEKBKID, a.CMTrxNum, a.SRCDOCNUM, a.SRCDOCTYP, a.TRXDATE, a.GLPOSTDT, a.TRXAMNT, a.ORIGAMT, a.Checkbook_Amount, a.CURNCYID, a.CMLinkID, a.paidtorcvdfrom, a.DSCRIPTN, a.Recond, a.reconnumb,
                  a.VOIDED, a.VOIDDATE, dbo.GL00105.ACTNUMST, dbo.CM00100.DSCRIPTN AS ChkBkDesc
FROM        dbo.GL00105 INNER JOIN
                  dbo.CM00100 ON dbo.GL00105.ACTINDX = dbo.CM00100.ACTINDX RIGHT OUTER JOIN
                      (SELECT     CMRECNUM, sRecNum, RCRDSTTS, CHEKBKID, CMTrxNum, SRCDOCNUM, SRCDOCTYP, TRXDATE, GLPOSTDT, TRXAMNT, ORIGAMT, Checkbook_Amount, CURNCYID, CMLinkID, paidtorcvdfrom, DSCRIPTN, Recond, CAST(RECONUM AS varchar(50))
                                         AS reconnumb, VOIDED, VOIDDATE
                       FROM        dbo.CM20200
                       UNION
                       SELECT     CMRECNUM, sRecNum, RCRDSTTS, CHEKBKID, RCPTNMBR, SRCDOCNUM, SRCDOCTYP, receiptdate, GLPOSTDT, RCPTAMT, ORIGAMT, Checkbook_Amount, CURNCYID, CMLinkID, RcvdFrom, DSCRIPTN, DEPOSITED,
                                         CAST(depositnumber AS varchar(50)) AS depnumb, VOIDED, VOIDDATE
                       FROM        dbo.CM20300) AS a ON dbo.CM00100.CHEKBKID = a.CHEKBKID

GO
----------------------------------------------------------------------------

create view [dbo].[BI_Posted_GL_Trx]
as

select
YEAR1 Trx_Year,
TRXDATE Trx_Date,
JRNENTRY Journal_Entry,
ORTRXSRC Originating_TRX_Source,
REFRENCE Reference,
ORMSTRID Originating_Master_ID,
ORMSTRNM Originating_Master_Name,
ORDOCNUM Originating_Doc_Number,
DEBITAMT Debit_Amount,
CRDTAMNT Credit_Amount,
ACTNUMST Account_Number,
ACTDESCR Account_Description,
ACCATDSC Account_Category,
CURNCYID Currency_ID,
USWHPSTD User_Who_Posted

from
(select ACTINDX, OPENYEAR YEAR1, TRXDATE, JRNENTRY, ORTRXSRC,
        REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT,
        CRDTAMNT, CURNCYID, USWHPSTD
 from GL20000
 where SOURCDOC not in ('BBF','P/L') and VOIDED = 0

 union all

 select ACTINDX, HSTYEAR YEAR1, TRXDATE, JRNENTRY, ORTRXSRC,
        REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT,
        CRDTAMNT, CURNCYID, USWHPSTD
 from GL30000
 where SOURCDOC not in ('BBF','P/L') and VOIDED = 0) GL

inner join GL00105 GM
     on GL.ACTINDX = GM.ACTINDX
inner join GL00100 GA
     on GL.ACTINDX = GA.ACTINDX
inner join GL00102 C
     on GA.ACCATNUM= C.ACCATNUM

GO
----------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_Posted_GL_Trx_smry]
AS
SELECT     Trx_Year, Trx_Date, Journal_Entry, Originating_TRX_Source, Reference, Originating_Master_ID, Originating_Master_Name, Originating_Doc_Number, SUM(Debit_Amount) AS DebitAmt,
                      SUM(Credit_Amount) AS CreditAmt, Account_Number, Account_Description, Account_Category, Currency_ID, User_Who_Posted, CASE WHEN LEFT(originating_doc_number, 3)
                      = 'TRF' THEN 0 WHEN SUM(debit_amount) > 0 AND SUM(credit_amount) > 0 THEN SUM(debit_amount) ELSE SUM(Debit_Amount - Credit_Amount) END AS AbsAmt
FROM         dbo.BI_Posted_GL_Trx
GROUP BY Trx_Year, Trx_Date, Journal_Entry, Originating_TRX_Source, Reference, Originating_Master_ID, Originating_Master_Name, Originating_Doc_Number, Account_Number, Account_Description,
                      Account_Category, Currency_ID, User_Who_Posted


GO

--------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_Rec_GL_vs_Bank]
AS
SELECT     dbo.BI_Posted_GL_Trx_smry.Trx_Year, dbo.BI_Posted_GL_Trx_smry.Trx_Date, dbo.BI_Posted_GL_Trx_smry.Journal_Entry, dbo.BI_Posted_GL_Trx_smry.Originating_TRX_Source, dbo.BI_Posted_GL_Trx_smry.Reference,
                  dbo.BI_Posted_GL_Trx_smry.Originating_Master_ID, dbo.BI_Posted_GL_Trx_smry.Originating_Master_Name, dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number, dbo.BI_Posted_GL_Trx_smry.DebitAmt, dbo.BI_Posted_GL_Trx_smry.CreditAmt,
                  dbo.BI_Posted_GL_Trx_smry.Account_Number, dbo.BI_Posted_GL_Trx_smry.Account_Description, dbo.BI_Posted_GL_Trx_smry.Account_Category, dbo.BI_Posted_GL_Trx_smry.Currency_ID, dbo.BI_Posted_GL_Trx_smry.User_Who_Posted,
                  dbo.BI_Posted_GL_Trx_smry.AbsAmt, dbo.BI_BankTrx_All.*
FROM        dbo.BI_Posted_GL_Trx_smry INNER JOIN
                      (SELECT DISTINCT ACTNUMST
                       FROM        dbo.BI_BankTrx_All AS BI_BankTrx_All_1) AS bankacts ON dbo.BI_Posted_GL_Trx_smry.Account_Number = bankacts.ACTNUMST LEFT OUTER JOIN
                  dbo.BI_BankTrx_All ON dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number = dbo.BI_BankTrx_All.CMTrxNum

GO

--------------------------------------------------------------------------------------

CREATE VIEW [dbo].[BI_Rec_Bank_vs_GL]
AS
SELECT     dbo.BI_Posted_GL_Trx_smry.Trx_Year, dbo.BI_Posted_GL_Trx_smry.Trx_Date, dbo.BI_Posted_GL_Trx_smry.Journal_Entry, dbo.BI_Posted_GL_Trx_smry.Originating_TRX_Source, dbo.BI_Posted_GL_Trx_smry.Reference,
                  dbo.BI_Posted_GL_Trx_smry.Originating_Master_ID, dbo.BI_Posted_GL_Trx_smry.Originating_Master_Name, dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number, dbo.BI_Posted_GL_Trx_smry.DebitAmt, dbo.BI_Posted_GL_Trx_smry.CreditAmt,
                  dbo.BI_Posted_GL_Trx_smry.Account_Number, dbo.BI_Posted_GL_Trx_smry.Account_Description, dbo.BI_Posted_GL_Trx_smry.Account_Category, dbo.BI_Posted_GL_Trx_smry.Currency_ID, dbo.BI_Posted_GL_Trx_smry.User_Who_Posted,
                  dbo.BI_Posted_GL_Trx_smry.AbsAmt, dbo.BI_BankTrx_All.CMRECNUM, dbo.BI_BankTrx_All.sRecNum, dbo.BI_BankTrx_All.RCRDSTTS, dbo.BI_BankTrx_All.CHEKBKID, dbo.BI_BankTrx_All.CMTrxNum, dbo.BI_BankTrx_All.SRCDOCNUM,
                  dbo.BI_BankTrx_All.SRCDOCTYP, dbo.BI_BankTrx_All.TRXDATE, dbo.BI_BankTrx_All.GLPOSTDT, dbo.BI_BankTrx_All.TRXAMNT, dbo.BI_BankTrx_All.ORIGAMT, dbo.BI_BankTrx_All.Checkbook_Amount, dbo.BI_BankTrx_All.CURNCYID, dbo.BI_BankTrx_All.CMLinkID,
                  dbo.BI_BankTrx_All.paidtorcvdfrom, dbo.BI_BankTrx_All.DSCRIPTN, dbo.BI_BankTrx_All.Recond, dbo.BI_BankTrx_All.reconnumb, dbo.BI_BankTrx_All.VOIDED, dbo.BI_BankTrx_All.VOIDDATE, dbo.BI_BankTrx_All.ACTNUMST, dbo.BI_BankTrx_All.ChkBkDesc
FROM        dbo.BI_Posted_GL_Trx_smry INNER JOIN
                      (SELECT DISTINCT ACTNUMST
                       FROM        dbo.BI_BankTrx_All AS BI_BankTrx_All_1) AS bankacts ON dbo.BI_Posted_GL_Trx_smry.Account_Number = bankacts.ACTNUMST RIGHT OUTER JOIN
                  dbo.BI_BankTrx_All ON dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number = dbo.BI_BankTrx_All.CMTrxNum

GO
------------------------------------------------------------------------------------------------

eOne Flexicoder - Flexicoder does not recode distributions automatically

Issue:
After a user enters a transaction, the flexicoder triggers seem to not fire, and the distributions are not updated

Solution:
Most likely, the client that is entering the transaction has a bad Flexicoder install.
Reinstall flexicoder on the client using the admin account, and grant full access to the Dynamics GP program files folder.


Related Issues:
Flexicoder will recode distributions correctly under the following conditions:

  • Flexicoder is correctly installed on the server
  • Flexicoder is correctly installed on the client
  • Flexicoder has been correctly configured to recode segments based on some criteria
  • Flexicoder has been correctly configured to recode specific document types
  • The document is saved
  • The document is posted

Flexicoder will not recode distributions correctly if:
  • The user is not an administrative user, and does not have access to the gp program files folder
    • The installation may fail silently during install if you do not have full access
    • The installation may fail if another instance of GP is running in the bckground
  • An older version of the dynamics or company database is restored after installing flexicoder
  • The user posting transactions does not have flexicoder correctly installed and configured
  • The final account combination does not exist in the chart of accounts,and you have not configured automatic account creation


Wednesday, October 29, 2014

Dynamics GP - Workflow - Cannot add active directory user as manager to workflow

Dynamics GP workflow uses the Active Directory Manager field to determine the user's manager.

The user account must have a First name, and a manager defined in Active Directory to allow the user to be selected as a manager on a workflow.

Additional Info
https://community.dynamics.com/gp/b/dynamicsgp/archive/2014/10/09/workflow-2-0-39-s-workflow-user-setup-window-options-part-3-workflow-role.aspx

Dynamics GP - Encumbrance Management - Ticking enable encumbrance hangs, freezes, not responding


Issue:
After installing the Encumbrance management module, you have to enable encumbrance from tools>setup>purchasing.
When you tick enable encumbrance management, and fill in the budget information, the blue bar does not move, and the system appear to freeze or hang

Cause:
It will attempt to create encumbrances for all open GL journals.
Then it will attempt to create encumbrances for all open PO's and receipts.
If you have a large number of unposted journals or open po's, this process may take an extremely long time.


Resolution:
  1. Post all journals
  2. Close as many PO's as possible
  3. OR you can just leave it running, and eventually it will finish creating all the encumbrances required. (during our testing, it created ~5 encumbrances / second)

Monday, October 27, 2014

Dynamics GP - Integration Manager - ADO Field is Nothing

Cause:
You have duplicate column header names.


Solution:
Rename the column headers in your data file to have unique names.

SQL 2012 - MSDN, Partner, Azure Default License Expires after 120 days

Issue:
By default, when you download the SQL2012 installation files, the correct key is included in the setup files.

However, during the installation, by default, it uses the evaluation key.
After 120 days, the SQL 2012 instance throws a license expiration error, and refuses connections.


Solution:
Enter the correct key and update the installation using the correct key.
By default, the SQL2012 install ALWAYS installs the evaluation keys. You cannot change them.
You must register SQL using your correct keys after it has completed installing.


Wednesday, October 22, 2014

Dynamics GP - SQL View - Sales Header and Detail, Work and History

--------------------------------------
--Sales Open
-------------------------------------

SELECT     TOP (100) PERCENT dbo.SOP10100.SOPNUMBE AS [SOP Number], dbo.SOP10100.DOCDATE AS [Document Date], dbo.SOP10100.GLPOSTDT AS [GL Post Date],
                  CASE WHEN SOP10100.soptype = 4 THEN SOP10200.XTNDPRCE * - 1 ELSE SOP10200.xtndprce END AS Sales, CASE WHEN SOP10100.soptype = 4 THEN SOP10200.EXTDCOST * - 1 ELSE SOP10200.extdcost END AS Costs,
                  CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END AS Qty, dbo.SOP10100.CUSTNMBR AS [Customer Number],
                  CASE WHEN SOP10100.soptype = 1 THEN 'Quote' WHEN SOP10100.soptype = 2 THEN 'Order' WHEN SOP10100.soptype = 3 THEN 'Invoice' WHEN SOP10100.soptype = 4 THEN 'Return' WHEN SOP10100.soptype = 5 THEN 'BackOrder' WHEN SOP10100.soptype = 6
                   THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code], dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class],
                  dbo.SOP10200.QTYBSUOM AS [Base Unit Of Measure], (CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END) * dbo.SOP10200.QTYBSUOM AS [Units Sold], dbo.SOP10200.STATE, dbo.SOP10200.PRSTADCD,
                  dbo.SOP10100.SLPRSNID AS [Salesperson ID], dbo.IV00101.ITEMNMBR AS [Item Number], dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP10100.ShipToName, dbo.RM00101.COMMENT1, dbo.RM00101.SLPRSNID AS Customer_Slprsnid,
                  CASE WHEN sop10100.soptype = 4 THEN sop10200.trdisamt * - 1 ELSE sop10200.trdisamt END AS TrDisamt, CASE WHEN sop10100.soptype = 4 THEN sop10200.mrkdnamt * - 1 ELSE sop10200.mrkdnamt END AS Mrkdnamt,
                  CASE WHEN sop10100.soptype = 4 THEN sop10100.trdisamt * - 1 ELSE sop10100.trdisamt END AS DocDiscount
FROM        dbo.SOP10100 INNER JOIN
                  dbo.SOP10200 ON dbo.SOP10100.SOPTYPE = dbo.SOP10200.SOPTYPE AND dbo.SOP10100.SOPNUMBE = dbo.SOP10200.SOPNUMBE INNER JOIN
                  dbo.IV00101 ON dbo.SOP10200.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                  dbo.IV40400 ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD INNER JOIN
                  dbo.RM00101 ON dbo.SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR
WHERE     (dbo.SOP10100.VOIDSTTS = 0) AND (dbo.SOP10200.CMPNTSEQ = 0) AND (dbo.SOP10100.SOPTYPE IN (3, 4))

--------------------------------------
--Sales History
-------------------------------------
SELECT     TOP (100) PERCENT dbo.SOP30200.SOPNUMBE AS [SOP Number], dbo.SOP30200.DOCDATE AS [Document Date], dbo.SOP30200.GLPOSTDT AS [GL Post Date],
                  CASE WHEN sop30200.soptype = 4 THEN sop30300.XTNDPRCE * - 1 ELSE sop30300.xtndprce END AS Sales, CASE WHEN sop30200.soptype = 4 THEN sop30300.EXTDCOST * - 1 ELSE sop30300.extdcost END AS Costs,
                  CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END AS Qty, dbo.SOP30200.CUSTNMBR AS [Customer Number],
                  CASE WHEN sop30200.soptype = 1 THEN 'Quote' WHEN sop30200.soptype = 2 THEN 'Order' WHEN sop30200.soptype = 3 THEN 'Invoice' WHEN sop30200.soptype = 4 THEN 'Return' WHEN sop30200.soptype = 5 THEN 'BackOrder' WHEN sop30200.soptype = 6 THEN
                   'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code], dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class],
                  dbo.SOP30300.QTYBSUOM AS [Base Unit Of Measure], (CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END) * dbo.SOP30300.QTYBSUOM AS [Units Sold], dbo.SOP30300.STATE, dbo.SOP30300.PRSTADCD,
                  dbo.SOP30200.SLPRSNID AS [Salesperson ID], dbo.IV00101.ITEMNMBR AS [Item Number], dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP30200.ShipToName, dbo.RM00101.COMMENT1, dbo.RM00101.SLPRSNID AS Customer_Slprsnid,
                  CASE WHEN sop30200.soptype = 4 THEN sop30300.trdisamt * - 1 ELSE sop30300.trdisamt END AS Trdisamt, CASE WHEN sop30200.soptype = 4 THEN sop30300.mrkdnamt * - 1 ELSE sop30300.mrkdnamt END AS Mrkdnamt,
                  CASE WHEN sop30200.soptype = 4 THEN sop30200.trdisamt * - 1 ELSE sop30200.trdisamt END AS DocDiscount
FROM        dbo.SOP30200 INNER JOIN
                  dbo.SOP30300 ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE INNER JOIN
                  dbo.IV00101 ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                  dbo.IV40400 ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD INNER JOIN
                  dbo.RM00101 ON dbo.SOP30200.CUSTNMBR = dbo.RM00101.CUSTNMBR
WHERE     (dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30300.CMPNTSEQ = 0) AND (dbo.SOP30200.SOPTYPE IN (3, 4))

How to Import Excel 2003 32 bit file to SQL 2008 32 bit

Related errors:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Solution1 
Change the TEMP and TMP variables on the machine to a public location, allow all users full access
(Right click my computer>properties>Advanced>Environment Variables> Change the paths)
Original Post
http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx


For 32-Bit Excel, and SQL use
--------------------------------------------------------------
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;HDR=YES;Database=C:\temp\test.xls',
    'select * from [sheet1$]')




If you continue to get the error, try installing the 2007 data access components. A Full install of office should work just as well.
Run windows update and install all service packs
http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

If you install the 2007 Data Access Components, you can use this command to open the files.
Both methods will work.

SELECT * FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\test\test.xls;HDR=Yes', 'Select * from [sheet1$]' )

How to install 64-bit Data Access Component Engine on a machine with 32-bit office components.

Error:
You cannot install the 64-bit version of Microsoft access Database engine 2010 because you currently have 32-bit office products installed

Solution:
Install in passive mode
Remove the registry reference to mmo.dll

Original post


Related Errors:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

SQL - How to get the day name of a date

select DATENAME( dw, CONVERT( date, getdate(), 103 ) ) as BDay

Replace getdate() with your field

Saturday, October 18, 2014

Dynamics GP - How to fix discrepancies between GL and Subledger


  • Smartlist the GL for the transaction Value to determine if the transactions may have gone to the wrong account.
  • If a transaction exists in the Subledger, and not in the GL
    • Manually enter a GL journal for the distribution.
  • If a transaction exists in the GL and not in the subledger
    • Void/reverse the GL transaction, and re-enter the transaction from the subledger
  • If a transaction is not being displayed in your Bank rec, but is in the gl and not in the subledger
    • Void/reverse the GL transaction, and re-enter the transaction from the Bank transaction entry screen.
  • If a transaction is not being displayed in your Bank rec, but is in the gl and in the subledger
    • Void/reverse the Subledger transaction, and re-enter the transaction from the Bank transaction entry screen.

Dynamics GP - Security Task Setup Window Locations. Where is the task located?

Reason Codes - Microsoft Dynamics GP>Windows>Company
Reason Code Setup - Microsoft Dynamics GP>Windows>Company

Thursday, October 9, 2014

Dynamics GP - Smartlist Export Error - "Excel cannot open the file [filename] because the file format or file extension is not valid."

We got this error happening because one of the text fields was using "[" and "]" and "(" and ")" characters that was causing the smartlist to export incorrectly.

We removed the characters and everything exported fine.

Dynamics NAV - Warehouse Handling is Required


Check The fields on the Warehouse Tab, set them to true

1. Require Receive

2. Require Pick fields





Original Solution
http://dynamicsuser.net/forums/p/29337/154874.aspx

Wednesday, October 8, 2014

Dynamics GP - Modifier and VBA Visual Basic - ODBC Connection

Private Sub GetItemCost()

Dim objRec
Dim objConn
Dim cmdString

Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=GP\GPSQL;Initial Catalog=TWO;User Id=sa;Password=pw"
objConn.Open


cmdString = "Select CURRCOST from IV00101 where (ITEMNMBR='" + ItemNumber + "')"

Set objRec = objConn.Execute(cmdString)

If objRec.EOF = True Then
ManufacturingOrderReceiptEn.CurrencyM105 = ""
Else
ManufacturingOrderReceiptEn.CurrencyM105 = objRec!CURRCOST
End If
objConn.Close
End Sub

Ensure that you add all fields you need to VB
Ensure you run GP as administrator or the VB will not save
Call this in the body to execute for each record
ManufacturingOrderReceiptEn.CurrencyM105 is an empty calculated field
You must put the calculated field in the BODY or the appropriate section where the VB code is running

Sunday, October 5, 2014

Dynamics GP - SQL View - Inventory Base UofM

SELECT     dbo.IV00101.ITEMNMBR, dbo.IV00101.UOMSCHDL, dbo.IV40201.BASEUOFM, dbo.IV40201.UMDPQTYS
FROM         dbo.IV00101 INNER JOIN
                      dbo.IV40201 ON dbo.IV00101.UOMSCHDL = dbo.IV40201.UOMSCHDL


------------------------------------------------------------------------------------
--UofM Conversion Table

SELECT        IV00101.ITEMNMBR, IV00101.UOMSCHDL, IV40201.BASEUOFM, IV40201.UMDPQTYS, IV40202.EQUIVUOM, IV40202.EQUOMQTY, IV40202.QTYBSUOM, IV40202.UOFM
FROM            IV00101 INNER JOIN
                         IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDL INNER JOIN
                         IV40202 ON IV40201.UOMSCHDL = IV40202.UOMSCHDL

Wednesday, October 1, 2014

eOne Smartconnect - Information Node taGLTransactionHeaderInsert not found in GLTransactionType. The element node is required for this transaction type.

Your map is grouping incorrectly on fields that are not the keys identified in the map header.

Either fix the keys in the map header, or change the grouping fields in the map.

SQL 2008 R2 - How to setup log shipping

Pre-requisites
  • User account being used to setup the log shipping have sysadmin role in sql
  • Primary SQL server
    • Primary Backup Folder that Primary and Secondary servers can access
    • Primary database set to Full or differential recovery
  • Secondary SQL Server
    • Secondary Backup Folder  that Primary and Secondary servers can access
Setup Log Shipping
  • Right click on the database you want to ship on the primary server>Tasks>Ship Transaction Logs
  • Add secondary server
  • The easiest options is to let it generate a full backup and restore it to the secondary (first option)
  • Restore Transaction Log in norecovery mode if you don't need to access it
  • Restore Transaction Log in standby mode if you need to access it
To bring a restoring database from NORECOVERY to RECOVERY

RESTORE DATABASE database_name WITH RECOVERY

If you script out the whole thing to a query window, the script has two parts, one for the primary, one for the secondary.

Run each part of the script manually on each respective server to create the jobs if it doesn't do it automatically.

Good guide here
http://www.mssqltips.com/sqlservertip/2301/step-by-step-sql-server-log-shipping/

Dynamics GP - PO Prepayments

PO Prepayments allows you to create Check Payments against PO's as if they were invoices.


  1. Setup PO Prepayments
    1. Tools>Setup>Purchasing>PO Processing
    2. Tick Allow Purchase Order Prepayments near the bottom of the window
      1. If you tick create manual prepayment, it allows you to print the check directly from the PO screen when it is closed
      2. If you do not tick create manual payment, you must use the check payment process to generate the payment
    3. This enables the PO Prepayment field on the PO Entry Window on the left by the remaining subtotal
  2. Create a PO
  3. Enter a value in the prepayment field, save and close
  4. Create Check Batch
    1. Transactions>Purchasing>Edit check Batch
    2. Create batch, tick Purchasing Prepayment Batch on the top right hand side
    3. Close and reopen the Edit Check Batch window
    4. The batch should only display PO's that can be prepaid
    5. Select PO's, print checks
  5. Once a payment is made against a PO, you cannot edit the PO
  6. To edit the PO, you must remove the payment.

Tuesday, September 30, 2014

SQL 2008 R2 - Log Shipping Error - The log shipping secondary database TWO has restore threshold of 45 minutes and is out of sync

Problem:
This occurs when the logs are unable to be restored to the secondary database, and the secondary database falls out of sync or misses a log.

Solution:
Reconfigure log shipping completely.


Saturday, September 27, 2014

Dynamics GP - How to reset all Average Costed Inventory Costs at a specific Historical Date

Adjust all stock out at that date to ensure your HITB reads 0

--Run this to identify all of the cost rows that need to be updated, change the date accordingly.
--copy the last column, and run the scripts to update each row.


SELECT IV10200.QTYONHND, IV10200.ITEMNMBR, IV10200.TRXLOCTN, IV10200.DATERECD, IV10200.RCTSEQNM, IV10200.DEX_ROW_ID, IV10200.UNITCOST,

'update iv10200 set qtyonhnd = 0 where dex_row_id = ' + CAST(IV10200.DEX_ROW_ID AS varchar(50)) AS UpdOnHnd

FROM IV10200 INNER JOIN

(SELECT ITEMNMBR, MAX(Expr1) AS MaxSeq

FROM (SELECT ITEMNMBR, MAX(RCTSEQNM) AS Expr1, DATERECD

FROM IV10200 AS IV10200_1

GROUP BY ITEMNMBR, DATERECD

HAVING (DATERECD <= CONVERT(DATETIME, '2014-06-30 00:00:00', 102))) AS atdate

GROUP BY ITEMNMBR) AS MaxLine ON IV10200.ITEMNMBR = MaxLine.ITEMNMBR AND IV10200.RCTSEQNM = MaxLine.MaxSeq

WHERE (IV10200.QTYONHND <> 0)

Dynamics GP - Manufacturing - MO Status does not change to partially received or completed

This occurs when Post to GL for Inventory Transactions is not ticked.

Manufacturing Receipts do not consume raw materials from WIP if Post to GL for Inventory Transactions is not ticked,
Since the WIP is not consumed, the MO never detects a receipt, and the MO status never changes,

HOWEVER, the raw material stock and finished goods stock quantities are adjusted correctly.


Solution:
Tick Post to GL on the Inventory Transaction Posting setup.


Related Issues:
Receipt window shows values in qty to consume AND qty to backflush - This occurs because the initial partial receipt is not detected by manufacturing, and issued stock gets stuck in WIP.


Saturday, September 20, 2014

Dynamics GP - SQL View - Item Class, Inventory Class Accounts

/****** Object:  View [dbo].[BI_InvClass]    Script Date: 09/20/2014 08:12:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_InvClass]
AS
SELECT     dbo.IV40400.ITMCLSCD, dbo.IV40400.ITMCLSDC, GL00105_1.ACTNUMST AS Inv, GL00105_10.ACTNUMST AS InvOffset, GL00105_2.ACTNUMST AS InvCOGS,
                      GL00105_3.ACTNUMST AS InvSls, GL00105_4.ACTNUMST AS InvMarkdown, GL00105_5.ACTNUMST AS SlsReturns, GL00105_6.ACTNUMST AS InvInUse,
                      GL00105_7.ACTNUMST AS InvInSvc, GL00105_8.ACTNUMST AS Damaged, GL00105_9.ACTNUMST AS InvVariance, GL00105_15.ACTNUMST AS DropShip,
                      GL00105_13.ACTNUMST AS PurchPriceVar, GL00105_14.ACTNUMST AS UnrPurchPriceVar, GL00105_12.ACTNUMST AS InvReturns,
                      GL00105_11.ACTNUMST AS AssemblyVar, dbo.GL00105.ACTNUMST AS StdCostReval
FROM         dbo.GL00105 AS GL00105_6 RIGHT OUTER JOIN
                      dbo.GL00105 AS GL00105_8 RIGHT OUTER JOIN
                      dbo.GL00105 RIGHT OUTER JOIN
                      dbo.IV40400 ON dbo.GL00105.ACTINDX = dbo.IV40400.IVSCRVIX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_11 ON dbo.IV40400.ASMVRIDX = GL00105_11.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_12 ON dbo.IV40400.IVRETIDX = GL00105_12.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_14 ON dbo.IV40400.UPPVIDX = GL00105_14.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_13 ON dbo.IV40400.PURPVIDX = GL00105_13.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_15 ON dbo.IV40400.DPSHPIDX = GL00105_15.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_9 ON dbo.IV40400.IVVARIDX = GL00105_9.ACTINDX ON GL00105_8.ACTINDX = dbo.IV40400.IVDMGIDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_7 ON dbo.IV40400.IVINSVIX = GL00105_7.ACTINDX ON GL00105_6.ACTINDX = dbo.IV40400.IVINUSIX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_5 ON dbo.IV40400.IVSLRNIX = GL00105_5.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_4 ON dbo.IV40400.IVSLDSIX = GL00105_4.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_3 ON dbo.IV40400.IVSLSIDX = GL00105_3.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_2 ON dbo.IV40400.IVCOGSIX = GL00105_2.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_10 ON dbo.IV40400.IVIVOFIX = GL00105_10.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_1 ON dbo.IV40400.IVIVINDX = GL00105_1.ACTINDX

GO


Thursday, September 18, 2014

Dynamics GP - SQL View - Connect GL to Inventory Notes

/****** Object:  View [dbo].[BI_GL_INV_Notes]    Script Date: 09/22/2014 13:04:18 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI_GL_INV_Notes]'))
DROP VIEW [dbo].[BI_GL_INV_Notes]
GO

/****** Object:  View [dbo].[BI_GL_INV_Notes]    Script Date: 09/22/2014 13:04:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_GL_INV_Notes]
AS
SELECT     GL.JRNENTRY, dbo.SY03900.NOTEINDX AS InvNoteIndx, dbo.SY03900.TXTFIELD AS InvNote, IV.TRXSORCE, IV.IVDOCTYP, IV.DOCNUMBR, IV.DOCDATE,
                      IV.BCHSOURC, IV.BACHNUMB, IV.NOTEINDX, IV.GLPOSTDT, IV.SRCRFRNCNMBR, IV.SOURCEINDICATOR, IV.DEX_ROW_TS, IV.DEX_ROW_ID
FROM         (SELECT     TRXSORCE, IVDOCTYP, DOCNUMBR, DOCDATE, BCHSOURC, BACHNUMB, NOTEINDX, GLPOSTDT, SRCRFRNCNMBR, SOURCEINDICATOR,
                                              DEX_ROW_TS, DEX_ROW_ID
                       FROM          dbo.IV30200) AS IV INNER JOIN
                          (SELECT     JRNENTRY, NOTEINDX, ORCTRNUM
                            FROM          dbo.GL20000
                            UNION
                            SELECT     JRNENTRY, NOTEINDX, ORCTRNUM
                            FROM         dbo.GL30000) AS GL ON IV.DOCNUMBR = GL.ORCTRNUM LEFT OUTER JOIN
                      dbo.SY03900 ON IV.NOTEINDX = dbo.SY03900.NOTEINDX

GO


Monday, September 15, 2014

Dynamics GP - Limit item list to Items with BOMS only - Manufacturer, Manufacturing Item Number and Manufacturing Item Description

The inventory smartlist has the fields Manufacturer, Manufacturing Item Number and Manufacturing Item Description which are all blank. To populate these fields, you have to update the Manufacturing Item Number Maintenance screen, and mark a line as primary.

These additional fields are located in the Item Purchasing Options window if you click on the yellow gear next to the item number.

Data is stored in the IV00115

If you want to populate this table with current internal BOM listing, you can use this script. It will populate any manufacturing data for any items with boms that have no populated manufacturing data.


INSERT INTO IV00115
                      (ITEMNMBR, MANUFACTURER, MNFCTRITMNMBR, ItemDesc, PRIMARYITEM)
SELECT     BM010415.ITEMNMBR, 'Mycompany' AS comp, BM010415.ITEMNMBR AS Expr1, 'Manufactured' as Mf, 1 as p
FROM         BM010415 LEFT OUTER JOIN
                      IV00115 AS IV00115_1 ON BM010415.ITEMNMBR = IV00115_1.ITEMNMBR
WHERE     (IV00115_1.DEX_ROW_ID IS NULL)

Now create a smartlist that filters on the Mfg. Item Description = "Manufactured"
You can Now use this smartlist as a filter on the item lookup screen
You can set it as a default if you wish

Dynamics GP - Script to set all item engineering items to "Made" once a BOM exists for the item - "This is not a made item"

--Sets Items to "Make or Buy" and fulfil method "Make to Stock"
----------------------------------------------------------
update IVR10015 set EFFECTIVEDATE_I = '2017-01-01', ITEMSTATUS_I = 1, MAKEBUYCODE_I = 2, ITEMFULFILLMETHOD = 1 WHERE ITEMNMBR IN (SELECT ITEMNMBR FROM BM010415)



Wednesday, September 10, 2014

Dynamics GP - Company Setup and Posting Setup Tables - Set all reports to print to screen

SY03300 - Payment Terms master
SY04100 - Bank Master

SY02200 - Posting Print Setup Table


  • ASECTMNT - Ask for destination
  • PRNTJRNL - Print
  • PRTOPRNT - Print to Printer
  • PRTOSCNT - Print to Screen
    • --Set all posting journals to print to screen
    • update sy02200 set prtoscnt = 1 where prtoscnt = 0
  • PRTOFLNT - Print to File




SY02300 - Posting Setup Tables

  • PTGENLED - Post to GL
  • AUTPSTGL - Post Through GL
  • ALTRXPST - Allow TRx Posting
  • INCMCINF - Include Multicurrency
  • UPSTDTFR - Posting date from: 0 - batch, 1 - trx


SSRS - How to hide the "ERROR: subreport could not be shown"

Problem:
ERROR: subreport could not be shown
shows up wherever the subreport doesn't get valid parameters

By default, SSRS will not render an empty report.

The error you are getting is due to the fact that the parameter you are passing to the subreport is an invalid parameter because you have setup Parameters to be taken from a query, thus limiting the list of valid parameters.

If you change the available values of the subreport parameter to "none" then the subreport will accept all inputs, and simply render blank instead of throwing the error.

Solution:
Change the "Available Values" option of your subreport parameter(s) to "none"

Monday, September 8, 2014

One Drive - How to reset One Drive

Original Article
http://its.uiowa.edu/support/article/102675



  1. Go to the OneDrive cloud in the tray (bottom right hand corner - see above)
  2. Right click and say Stop Syncing folder (it will give you a warning about permanently stopping this).  That is ok.
  3. Go to c:\users\%username%\onedrive @ yourname
  4. Rename the folder to onedrive@ yourname_old.  This will preserve the old data.
  5. Shut down any Microsoft products (Outlook, Word, Excel, etc.)
  6. Start Task Manager (Ctrl+Alt+Del)
  7. End the following processes:  groove.exe, msosync.exe
  8. Go to c:\users\%username%\AppData\Local\Microsoft\Office\SPW - delete everything in here.  Close the window.
  9. Go to c:\users\%username%\AppData\Local\Microsoft\Office\15.0\OfficeFileCache - delete everything in here.  Close the window.
  10. Restart your workstation. 
  11. If the sync process does not start automatically, open a browser and go to onedrive.uiowa.edu.
  12. Click on Sync and let it create a new sync folder
  13. If there are files missing, you will have to go to your _old folder and copy the files to your new sync area.

Saturday, September 6, 2014

Friday, September 5, 2014

eOne Smartconnect - node tapmtransactioninsert not found in pmtransaction type

This error occurs if you have an incorrect key field set on the map and it cannot group the information correctly.
Check your key field and set it to a unique value.

Dynamics GP 2013 - Smartlist export rounds large numbers to thousands

http://www.boyerassoc.com/blog/dynamics-gp-2013-smartlist-export-error

Tuesday, September 2, 2014

Dynamics GP - Currency Tables

dynamics.dbo.MC00100 - Exchange Rate Master
dynamics.dbo.MC40200 - Currency Master

Saturday, August 30, 2014

Dynamics GP - smartlist error - Unhandled script exception: Index 0 of local array is out of range in script 'ASI_Initialize_Explorer_Tree'. Script terminated.

Error:
Unhandled script exception:
Index 0 of local array is out of range in script 'ASI_Initialize_Explorer_Tree'. Script terminated.

Original solution: https://community.dynamics.com/gp/f/32/t/128874.aspx

Find your company id
SELECT CMPNYNAM as Company_Name,CMPANYID Company_ID FROM SY01500

Run this script replacing -1 with your company id, and dynamics with your system database name

insert into DYNAMICS..ADH00100 values ('0',  '498','3',  'Requisition Line Items','sa','-1')
insert into DYNAMICS..ADH00100 values ('0',  '499','3',  'Purchase Requisitions','sa','-1')

Tuesday, August 26, 2014

Dynamics GP - User Security Tables

All tables are in the dynamics database

SY01500 - Company Master
SY10500 - User - Role Assignment
SY10600 - Role - Task Assignment
SY09400 - Object List
SY10700 - Object - Task assignment
SY09000 - Security Tasks
SY09100 - Security Roles

Friday, August 22, 2014

Dynamics GP - Fix the HITB - Records are missing

http://support.microsoft.com/kb/2714780
https://community.dynamics.com/gp/f/32/p/99479/278940.aspx


These following views and sp are experimental. It has worked in my specific situation to restore the missing HITB records. It will identify all missing records, reconstruct what is required, then resequence them based on the current sequence numbering in the HITB. Execute the BI_HITB_RECRUN to populate the SEE30303 with the correctly sequenced missing records.

/****** Object:  View [dbo].[BI_HITB_Reconcile]    Script Date: 09/19/2014 15:28:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_HITB_Reconcile]
AS
SELECT DISTINCT
                      TOP (100) PERCENT dbo.SOP30200.USER2ENT, dbo.IV00101.ITMCLSCD, dbo.SOP30200.VOIDSTTS, dbo.SEE30303.ITEMNMBR AS HITBItemNo, dbo.SOP30300.ITEMNMBR,
                      dbo.SOP30200.DOCDATE, dbo.SOP30200.GLPOSTDT, dbo.SOP30300.SOPNUMBE, CASE sop30200.SOPTYPE WHEN 3 THEN 6 WHEN 4 THEN 5 END AS doctype, dbo.SOP30300.LOCNCODE,
                      '' AS Rctnum, '' AS RctSeqNum, 1 AS pchsrcty, 1 AS qtytype, dbo.SOP30300.UOFM,
                      dbo.SOP30300.QTYBSUOM * CASE WHEN sop30200.soptype = 3 THEN QUANTITY * - 1 ELSE quantity END AS TrxQtyinbs,
                      CASE WHEN sop30200.soptype = 3 THEN QUANTITY * - 1 ELSE quantity END AS TrxQty, 0 AS varqty, dbo.SOP30300.UNITCOST,
                      CASE WHEN sop30200.soptype = 3 THEN sop30300.extdcost * - 1 ELSE sop30300.extdcost END AS ExtCost, dbo.SOP30300.DECPLQTY, dbo.SOP30300.DECPLCUR, 0 AS overrec, 0 AS overrel,
                      0 AS overreldate, '' AS Rctnum1, 'SOP' AS Hstmodul, '' AS ortrxsrc, dbo.SOP30300.LNITMSEQ AS Lnseqnmbr, dbo.SOP30300.LNITMSEQ AS lnseq, dbo.SOP30300.CMPNTSEQ, '' AS scrfr, '' AS vend,
                      '' AS po, dbo.SOP30200.REFRENCE, 3 AS Vctn, dbo.SOP30300.INVINDX, dbo.SOP30300.CSLSINDX, gl.JRNENTRY, gl.TRXSORCE,
                      CASE WHEN sop30200.soptype = 4 THEN XTNDPRCE ELSE 0 END AS Debitamt, CASE WHEN sop30200.soptype = 3 THEN XTNDPRCE ELSE 0 END AS CreditAmt, gl.TRXDATE AS Date1, 0 AS Time1,
                      dbo.SOP30300.DEX_ROW_ID
FROM         dbo.SOP30300 INNER JOIN
                      dbo.SOP30200 ON dbo.SOP30300.SOPTYPE = dbo.SOP30200.SOPTYPE AND dbo.SOP30300.SOPNUMBE = dbo.SOP30200.SOPNUMBE INNER JOIN
                      dbo.IV00101 ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                          (SELECT     JRNENTRY, TRXDATE, ORDOCNUM, TRXSORCE
                            FROM          dbo.GL30000
                            GROUP BY JRNENTRY, TRXDATE, ORDOCNUM, TRXSORCE) AS gl ON dbo.SOP30300.SOPNUMBE = gl.ORDOCNUM LEFT OUTER JOIN
                      dbo.IV10002 ON dbo.SOP30300.ITEMNMBR = dbo.IV10002.ITEMNMBR AND dbo.SOP30300.SOPNUMBE = dbo.IV10002.IVDOCNBR FULL OUTER JOIN
                      dbo.SEE30303 ON dbo.SOP30300.UOFM = dbo.SEE30303.UOFM AND dbo.SOP30300.SOPNUMBE = dbo.SEE30303.DOCNUMBR AND dbo.SOP30300.ITEMNMBR = dbo.SEE30303.ITEMNMBR
WHERE     (CASE WHEN sop30200.soptype = 4 THEN QUANTITY * - 1 ELSE quantity END <> 0) AND (dbo.SEE30303.ITEMNMBR IS NULL) AND (dbo.SOP30300.SOPTYPE IN (3, 4)) AND
                      (dbo.SOP30200.VOIDSTTS = 0) AND (NOT (dbo.SOP30300.ITEMNMBR IN ('*misc')))
ORDER BY dbo.SOP30200.DOCDATE DESC

GO

/****** Object:  View [dbo].[BI_HITB_Reconcile2]    Script Date: 09/19/2014 15:28:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_HITB_Reconcile2]
AS
SELECT     ITEMNMBR, DOCDATE, GLPOSTDT, SOPNUMBE, doctype, LOCNCODE, Rctnum, RctSeqNum, pchsrcty, qtytype, UOFM, TrxQtyinbs, TrxQty, varqty, UNITCOST, ExtCost, DECPLQTY, DECPLCUR,
                      overrec, overrel, overreldate, Rctnum1, Hstmodul, ortrxsrc, Lnseqnmbr, lnseq, CMPNTSEQ, scrfr, vend, po, REFRENCE, Vctn, INVINDX, CSLSINDX, JRNENTRY, TRXSORCE, Debitamt, CreditAmt,
                      Date1, Time1, DEX_ROW_ID AS dexrow
FROM         dbo.BI_HITB_Reconcile

GO

/****** Object:  View [dbo].[BI_HITB_Reconcile3]    Script Date: 09/19/2014 15:29:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_HITB_Reconcile3]
AS
SELECT     dbo.bi_hitb_rec.ITEMNMBR, seqnm.Next_SEQNUMBR AS SeqNo, dbo.bi_hitb_rec.DOCDATE, dbo.bi_hitb_rec.GLPOSTDT, dbo.bi_hitb_rec.SOPNUMBE, dbo.bi_hitb_rec.doctype,
                      dbo.bi_hitb_rec.LOCNCODE, dbo.bi_hitb_rec.Rctnum, dbo.bi_hitb_rec.RctSeqNum, dbo.bi_hitb_rec.pchsrcty, dbo.bi_hitb_rec.qtytype, dbo.bi_hitb_rec.UOFM, dbo.bi_hitb_rec.TrxQtyinbs,
                      dbo.bi_hitb_rec.TrxQty, dbo.bi_hitb_rec.varqty, dbo.bi_hitb_rec.UNITCOST, dbo.bi_hitb_rec.ExtCost, dbo.bi_hitb_rec.DECPLQTY, dbo.bi_hitb_rec.DECPLCUR, dbo.bi_hitb_rec.overrec,
                      dbo.bi_hitb_rec.overrel, dbo.bi_hitb_rec.overreldate, dbo.bi_hitb_rec.Rctnum1, dbo.bi_hitb_rec.Hstmodul, dbo.bi_hitb_rec.ortrxsrc, dbo.bi_hitb_rec.Lnseqnmbr, dbo.bi_hitb_rec.lnseq,
                      dbo.bi_hitb_rec.CMPNTSEQ, dbo.bi_hitb_rec.scrfr, dbo.bi_hitb_rec.vend, dbo.bi_hitb_rec.po, dbo.bi_hitb_rec.REFRENCE, dbo.bi_hitb_rec.Vctn, dbo.bi_hitb_rec.INVINDX, dbo.bi_hitb_rec.CSLSINDX,
                      dbo.bi_hitb_rec.JRNENTRY, dbo.bi_hitb_rec.TRXSORCE, dbo.bi_hitb_rec.Debitamt, dbo.bi_hitb_rec.CreditAmt, dbo.bi_hitb_rec.Date1, dbo.bi_hitb_rec.Time1, dbo.bi_hitb_rec.dexrow
FROM         dbo.bi_hitb_rec INNER JOIN
                          (SELECT     ITEMNMBR, MAX(SEQNUMBR) + 1 AS Next_SEQNUMBR
                            FROM          dbo.SEE30303 AS SEE30303_1
                            GROUP BY ITEMNMBR) AS seqnm ON dbo.bi_hitb_rec.ITEMNMBR = seqnm.ITEMNMBR

GO

/****** Object:  StoredProcedure [dbo].[BI_HITB_RecRun]    Script Date: 09/19/2014 15:39:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[BI_HITB_RecRun]

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @dex as int

  drop table bi_hitb_rec
  select  * into bi_hitb_rec from bi_hitb_reconcile2
 
 
  while (SELECT     COUNT(*) AS Expr1
FROM         BI_HITB_Rec) >=1
begin
select @dex=MAX(dexrow) from bi_hitb_rec
  insert into see30303 select
  [ITEMNMBR]
      ,[SeqNo]
      ,[DOCDATE]
      ,[GLPOSTDT]
      ,[SOPNUMBE]
      ,[doctype]
      ,[LOCNCODE]
      ,[Rctnum]
      ,[RctSeqNum]
      ,[pchsrcty]
      ,[qtytype]
      ,[UOFM]
      ,[TrxQtyinbs]
      ,[TrxQty]
      ,[varqty]
      ,[UNITCOST]
      ,[ExtCost]
      ,[DECPLQTY]
      ,[DECPLCUR]
      ,[overrec]
      ,[overrel]
      ,[overreldate]
      ,[Rctnum1]
      ,[Hstmodul]
      ,[ortrxsrc]
      ,[Lnseqnmbr]
      ,[lnseq]
      ,[CMPNTSEQ]
      ,[scrfr]
      ,[vend]
      ,[po]
      ,[REFRENCE]
      ,[Vctn]
      ,[INVINDX]
      ,[CSLSINDX]
      ,[JRNENTRY]
      ,[TRXSORCE]
      ,[Debitamt]
      ,[CreditAmt]
      ,[Date1]
      ,[Time1]
  from bi_hitb_reconcile3 where dexrow = @dex
delete from bi_hitb_rec where dexrow = @dex
print @dex
  end
END


GO