Wednesday, July 16, 2014

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)

Monday, July 14, 2014

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

Wednesday, July 9, 2014

Tuesday, July 8, 2014

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, 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

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


Sunday, July 6, 2014

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.

Wednesday, July 2, 2014

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.