Dynamics GP -Collections Management - Unable to get extended version information for product 1157

Uninstall any previous Collections Management product you have installed.
Remove the collections management module from the GP add/remove install list

The upgrade routines for Collections Management are included in the new version, and occur independently of the GP upgrade

http://blog.profad.com/collections-management-tips-tricks/year-end-2013-for-collections-management/

Dynamics GP - Navigation Bar Tables

dynamics.SY07130 - Navigation Bar buttons (they are displayed in the order of the cmdsequence)

Dynamics GP - Find all transactions where an item is allocated

http://support.microsoft.com/kb/857144

Dynamics GP Analytical Accounting Tables

To report on AA codes on transactions, use the following tables

AAG20000 - Trx Hdr (Connect the docnumbr to sop or ar tables for details)
AAG20001 - Trx Dtl
AAG20002 - Trx AA Assignment
AAG20003 - AA Code

Dynamics NAV - Upgrade checklist from 2009 R2 to 2013 R2

Stuff you need:

• Upgrade toolkit for NAV 2013 R2 is in the install media here
• User License Key for NAV 2013R2
• Partner License key for NAV 2013R2

Stuff to Read:

• http://blogs.msdn.com/b/nav/archive/2013/11/16/upgrade-toolkit-for-upgrading-data-from-microsoft-dynamics-nav-2009-r2-and-microsoft-dynamics-nav-2009-sp1-to-microsoft-dynamics-nav-2013-r2.aspx
• http://msdn.microsoft.com/en-us/library/hh169484(v=nav.71).aspx

Hardware Requirements:

• NAV 2013 R2 requires 64-bit OS and SQL
• Windows Server 2008 R2
• SQL 2008 R2

Dynamics GP - View to Reconcile Inventory Subledger to GL

View for all Posted GL Transactions

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

--Original View for all GL Trx
-------------------------------------------------------------------------------------------------
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

--A summarized version of the view
----------------------------------------------------------------------------------------------
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
------------------------------------------------------------------------------------------------

--View for all Inventory Transactions - iv30300 table, credit side
------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_InvTrxSmry]
AS
SELECT     dbo.IV30300.TRXSORCE, dbo.IV30300.DOCTYPE, dbo.IV30300.DOCNUMBR, dbo.IV30300.DOCDATE, dbo.IV30300.HSTMODUL, SUM(dbo.IV30300.EXTDCOST) AS Amt,
                      dbo.GL00105.ACTNUMST AS IvAct, dbo.GL00100.ACTDESCR AS IvActDesc, CASE WHEN TRXQTY >= 0 THEN 1 ELSE - 1 END AS PosNeg,
                      SUM(ROUND(dbo.IV30300.UNITCOST * dbo.IV30300.TRXQTY * CASE WHEN trxqty >= 0 THEN 1 ELSE - 1 END, 2)) AS AmtNoLC, CASE WHEN LEFT(docnumbr, 3)
                      = 'TRF' THEN 0 WHEN LEFT(docnumbr, 3) = 'RCT' THEN SUM(round(dbo.IV30300.UNITCOST * dbo.IV30300.TRXQTY, 2)) ELSE SUM(extdcost)
                      * CASE WHEN trxqty >= 0 THEN 1 ELSE - 1 END END AS AbsAmtNoLC
FROM         dbo.GL00100 INNER JOIN
                      dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX INNER JOIN
                      dbo.IV30300 ON dbo.GL00105.ACTINDX = dbo.IV30300.IVIVINDX
GROUP BY dbo.IV30300.TRXSORCE, dbo.IV30300.DOCTYPE, dbo.IV30300.DOCNUMBR, dbo.IV30300.DOCDATE, dbo.IV30300.HSTMODUL, dbo.GL00105.ACTNUMST, dbo.GL00100.ACTDESCR,
                      CASE WHEN TRXQTY >= 0 THEN 1 ELSE - 1 END

GO


--View for all Inventory Transactions - iv30300 table, debit side
------------------------------------------------------------------------------------------------

CREATE VIEW [dbo].[BI_InvTrxSmry_Offset]
AS
SELECT     dbo.IV30300.TRXSORCE, dbo.IV30300.DOCTYPE, dbo.IV30300.DOCNUMBR, dbo.IV30300.DOCDATE, dbo.IV30300.HSTMODUL, SUM(dbo.IV30300.EXTDCOST) AS Amt,
                      GL00105_1.ACTNUMST AS IvOffsetAct, GL00100_1.ACTDESCR AS IvOffsetActDesc, CASE WHEN TRXQTY >= 0 THEN 1 ELSE - 1 END AS PosNeg,
                      SUM(ROUND(dbo.IV30300.UNITCOST * dbo.IV30300.TRXQTY * CASE WHEN trxqty >= 0 THEN 1 ELSE - 1 END, 2)) AS AmtNoLC, CASE WHEN LEFT(docnumbr, 3)
                      = 'TRF' THEN 0 WHEN LEFT(docnumbr, 3) = 'RCT' THEN SUM(round(dbo.IV30300.UNITCOST * dbo.IV30300.TRXQTY, 2)) * - 1 ELSE SUM(extdcost)
                      * CASE WHEN trxqty >= 0 THEN 1 ELSE - 1 END * - 1 END AS AbsAmtNoLC
FROM         dbo.IV30300 INNER JOIN
                      dbo.GL00105 AS GL00105_1 ON dbo.IV30300.IVIVOFIX = GL00105_1.ACTINDX INNER JOIN
                      dbo.GL00100 AS GL00100_1 ON GL00105_1.ACTINDX = GL00100_1.ACTINDX
GROUP BY dbo.IV30300.TRXSORCE, dbo.IV30300.DOCTYPE, dbo.IV30300.DOCNUMBR, dbo.IV30300.DOCDATE, dbo.IV30300.HSTMODUL, GL00105_1.ACTNUMST, GL00100_1.ACTDESCR,
                      CASE WHEN TRXQTY >= 0 THEN 1 ELSE - 1 END

GO

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

--View for all journals that touch inventory accounts
---------------------------------------------------------------------------------------------------

CREATE VIEW [dbo].[BI_GL_Inventory_Journals]
AS
SELECT     Journal_Entry
FROM         dbo.BI_Posted_GL_Trx
GROUP BY Journal_Entry, Account_Category
HAVING      (Account_Category = 'inventory')

GO

---------------------------------------------------------------------------------------------------------
--View to pull all data together, and compare GL to SL to identify missing SL
---------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_GL_INV_Rec]
AS
SELECT     TOP (100) PERCENT 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.Trx_Year, dbo.BI_Posted_GL_Trx_smry.Trx_Date, dbo.BI_Posted_GL_Trx_smry.Originating_Master_ID, dbo.BI_Posted_GL_Trx_smry.Originating_Master_Name,
                      dbo.BI_Posted_GL_Trx_smry.Currency_ID, dbo.BI_Posted_GL_Trx_smry.User_Who_Posted, 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.Originating_Doc_Number, dbo.BI_Posted_GL_Trx_smry.DebitAmt AS GLDebamt,
                      dbo.BI_Posted_GL_Trx_smry.CreditAmt AS GLCredAmt, dbo.BI_InvTrxSmry_Offset.DOCNUMBR AS SLDebitDoc, dbo.BI_InvTrxSmry_Offset.Amt AS SLDebitAmt,
                      dbo.BI_InvTrxSmry.DOCNUMBR AS SLCreditDoc, dbo.BI_InvTrxSmry.Amt AS SLCreditAm, CASE WHEN dbo.BI_InvTrxSmry.DOCNUMBR IS NULL
                      THEN BI_InvTrxSmry_Offset.DOCNUMBR ELSE dbo.BI_InvTrxSmry.docnumbr END AS SLDoc, CASE WHEN dbo.BI_InvTrxSmry.Amt IS NULL
                      THEN BI_InvTrxSmry_Offset.Amt ELSE dbo.BI_InvTrxSmry.Amt END AS SLAmt, CASE WHEN account_category = 'inventory' THEN (abs(ISNULL(dbo.BI_InvTrxSmry.Amt, 0)
                      - ISNULL(dbo.BI_Posted_GL_Trx_smry.debitAmt, 0))) - abs((ISNULL(BI_InvTrxSmry_Offset.Amt, 0) - ISNULL(dbo.BI_Posted_GL_Trx_smry.creditAmt, 0))) ELSE 0 END AS GLSLDiff
FROM         dbo.BI_GL_Inventory_Journals INNER JOIN
                      dbo.BI_Posted_GL_Trx_smry ON dbo.BI_GL_Inventory_Journals.Journal_Entry = dbo.BI_Posted_GL_Trx_smry.Journal_Entry LEFT OUTER JOIN
                      dbo.BI_InvTrxSmry ON dbo.BI_Posted_GL_Trx_smry.AbsAmt = dbo.BI_InvTrxSmry.AbsAmtNoLC AND dbo.BI_Posted_GL_Trx_smry.Account_Number = dbo.BI_InvTrxSmry.IvAct AND
                      dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number = dbo.BI_InvTrxSmry.DOCNUMBR LEFT OUTER JOIN
                      dbo.BI_InvTrxSmry_Offset ON dbo.BI_Posted_GL_Trx_smry.AbsAmt = dbo.BI_InvTrxSmry_Offset.AbsAmtNoLC AND
                      dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number = dbo.BI_InvTrxSmry_Offset.DOCNUMBR AND dbo.BI_Posted_GL_Trx_smry.Account_Number = dbo.BI_InvTrxSmry_Offset.IvOffsetAct
ORDER BY dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number, dbo.BI_Posted_GL_Trx_smry.Journal_Entry

GO
---------------------------------------------------------------------------------------------------------
--View to pull all data together, and compare GL to SL to identify missing GL
---------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_GL_INV_Rec_NoGL]
AS
SELECT     TOP (100) PERCENT dbo.BI_InvTrxSmry.DOCNUMBR AS SLDoc, dbo.BI_InvTrxSmry.Amt AS SLAmt, dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number, dbo.BI_InvTrxSmry.TRXSORCE,
                      dbo.BI_InvTrxSmry.DOCTYPE, dbo.BI_InvTrxSmry.DOCDATE, YEAR(dbo.BI_InvTrxSmry.DOCDATE) as DocYr, dbo.BI_InvTrxSmry.HSTMODUL, dbo.BI_InvTrxSmry.IvAct, dbo.BI_InvTrxSmry.IvActDesc
FROM         dbo.BI_GL_Inventory_Journals INNER JOIN
                      dbo.BI_Posted_GL_Trx_smry ON dbo.BI_GL_Inventory_Journals.Journal_Entry = dbo.BI_Posted_GL_Trx_smry.Journal_Entry RIGHT OUTER JOIN
                      dbo.BI_InvTrxSmry ON dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number = dbo.BI_InvTrxSmry.DOCNUMBR
WHERE     (dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number IS NULL)
ORDER BY dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number, dbo.BI_Posted_GL_Trx_smry.Journal_Entry

GO

Dynamics NAV - LS Retail - POS Design - Create a button that inserts a specific amount for a tender type

Problem:
We have a situation where we want users to be able to collect payments in only 100 or 500 amounts, and want to force two specific buttons that enter a fixed payment amount.

Solution:
On the button, if you have it enter the amount first, and then run the tender type, it accepts the payment.

Command: amount_k
Parameter: 100 (payment amount)
Post command: tender_k
Post Parameter:20 (tender type)

Dynamics GP PSTL Tools download - All versions

PSTL Download:
https://mbs.microsoft.com/partnersource/northamerica/deployment/downloads/service-packs/NOAM_PSTL


GP2015 PSTL Tools List
-------------------------------------------

• Chapter 1: Installing Professional Services Tools Library 6
• Installation 6
• Security setup 9
• Chapter 2: Using System Tools 12
• Using Shortcuts Copy 12
• Using Toolkit 13
• Using Menu Inquiry Utility 15
• Using Update User Date Utility 16
• Chapter 3: Using Financial Tools 17
• Using Account Modifier/Combiner 17
• Using Checkbook Modifier 22
• Using Fiscal Period Modifier 23
• Using General Ledger Master Record Triggers 24
• Chapter 4: Using Sales Tools 25
• Using Customer Name Modifier 25
• Using Receivables Management Master Record Triggers 26
• Using Receivables Management Transaction Unapply 27
• Using Salesperson Modifier 28
• Using Sales Order Processing—Customer Item Lookup 29
• Using Territory Modifier 31
• Using Territory Combiner 32
• Using SOP PO Number Check 33
• Chapter 5: Using Inventory Tools 36
• Using Inventory Site Combiner 36
• Using Inventory Site Modifier 37
• Using Item Description Modifier 38
• Using Item Number Combiner 39
• Using Item Number Modifier 40
• Using Item Reconciler 41
• Chapter 6: Using Purchasing Tools 42
• Using 1099 Modifier 42
• Using Payables Management Master Record Triggers 44
• Using Payables Management Minimum Check 45
• Using Select Checks Combiner 46
• Using Vendor Name Modifier 47
• Using Minimum PO/Receipt Number 48
• Using POP Cost Defaulter 49
• Chapter 7: Using Payroll Tools 50
• Using Employee Modifier 50
• Using the Certified Payroll Report 51
• Chapter 8: Using Miscellaneous Tools 55
• Using Fixed Asset Modifier 55
• Using Default Add Item POP/SOP 56
• Using Doc Date Verify 57
• Using Decimal Place Tool 58
• Using Company Copy 59

Dynamics GP - Reconcile to GL - Inventory- Unhandled Script Exception. Long integer out of range. Results Invalid. EXCEPTION_CLASS_SCRIPT_OUT_OF_RANGE

Error:
When you run the Reconcile to GL routine against the inventory module, you get this error
“Unhandled Script Exception. Long integer out of range. Results Invalid. EXCEPTION_CLASS_SCRIPT_OUT_OF_RANGE”

Solution:

This error can occur if too many records are being returned.

Reduce the date range, or number of accounts and try processing again.

SQL Linked Server to an External SQL Server

You can use the aliases in the SQL Configuration Manager to setup user-friendly SQL Server names

Provider: Sql server native client 10 or 11
Product Name: SQLSERVER
Data source: www.publicaddress.com\SQLSERVERNAME,port
example: test.mydomain.com\TEST,1433

Port 1433 has to be open through the public address, and forwarded to the sql server

Security: tick be made using this security context, enter remote sa password

Management Reporter - Cannot connect to server

http://support.microsoft.com/kb/2862020

Note:
The server and clients must all be on the same time zone with the same time (within 5 mins) or the client will not be able to connect to the server.