Microsoft Dynamics GP 2016 is available for download

The most waited Microsoft Dynamics GP 2016 is released and we can download from below link.

Download Link:

Product Release Downloads for Microsoft Dynamics GP 2016

Hope this helps!!!

Advertisements

How to clear Dynamics GP companies which are not available in SQL server?

Use the below SQL query to clear all the Dynamics GP companies which are no longer available in SQL server and showing on Dynamics GP company drop down.

Run the below query against master database and replace “DYNAMCS” from this script with your system database name if you are using system database name other than DYNAMICS.

set nocount on
use master

declare @dbNames char(5), @GPSystem char(15), @Statement char(3400)
create table ##GPSystem (GPSystem char(15))

declare FindGPSystem cursor for
select name from sysdatabases where len(name) <= 5 and name not in (‘model’,’msdb’) order by name
open FindGPSystem
fetch next from FindGPSystem into @dbNames
while (@@fetch_status <> -1) begin
set @Statement = ‘if exists (select 1 from ‘ + rtrim(@dbNames) + ‘..sysobjects
where type = ”U” and name = ”SY00100”)
insert into ##GPSystem (GPSystem)
select DBNAME from ‘ + rtrim(@dbNames) + ‘..SY00100’
exec (@Statement)
fetch next from FindGPSystem into @dbNames
end
close FindGPSystem
deallocate FindGPSystem

if not exists (select 1 from ##GPSystem)
insert into ##GPSystem (GPSystem) select ‘DYNAMICS’ — Use your Sytem database Name instead of DYNAMICS if you are using other than DYNAMICS
delete from ##GPSystem where GPSystem not in (select name from sysdatabases)

declare CleanGPSystem cursor for
select GPSystem from ##GPSystem order by GPSystem
open CleanGPSystem
fetch next from CleanGPSystem into @GPSystem
while (@@fetch_status <> -1) begin
set @Statement = ‘use ‘ + rtrim(@GPSystem) + ‘
declare @statement char(150)
delete SY01500 where INTERID not in (select name from master..sysdatabases)

declare CMPANYID_Cleanup CURSOR for
select ”delete ” + o.name + ” where CMPANYID not in (0,-32767)
and CMPANYID not in (select CMPANYID from SY01500)”
from sysobjects o join syscolumns c on o.id = c.id and o.type = ”U”
where c.name = ”CMPANYID” and o.name <> ”SY01500”
order by o.name
open CMPANYID_Cleanup
fetch next from CMPANYID_Cleanup into @statement
while (@@fetch_status <> -1) begin
exec (@statement)
fetch next from CMPANYID_Cleanup into @statement
end
close CMPANYID_Cleanup
deallocate CMPANYID_Cleanup

declare companyID_Cleanup1 CURSOR for
select ”delete ” + rtrim(o.name) + ” where companyID not in (0,-32767)
and companyID not in (select CMPANYID from SY01500)”
from sysobjects o join syscolumns c on o.id = c.id and o.type = ”U”
where c.name = ”companyID” and o.name <> ”SY01500” and o.name <> ”syDeployedReports”
order by o.name
open companyID_Cleanup1
fetch next from companyID_Cleanup1 into @statement
while (@@fetch_status <> -1) begin
exec (@statement)
fetch next from companyID_Cleanup1 into @statement
end
close companyID_Cleanup1
deallocate companyID_Cleanup1

declare companyID_Cleanup2 CURSOR for
select ”delete ” + rtrim(o.name) + ” where companyID <> ””’ + rtrim(@GPSystem) + ””’
and companyID <>”””” and companyID not in (select INTERID from SY01500)”
from sysobjects o join syscolumns c on o.id = c.id and o.type = ”U”
where c.name = ”companyID” and o.name <> ”SY01500” and o.name = ”syDeployedReports”
order by o.name
open companyID_Cleanup2
fetch next from companyID_Cleanup2 into @statement
while (@@fetch_status <> -1) begin
exec (@statement)
fetch next from companyID_Cleanup2 into @statement
end
close companyID_Cleanup2
deallocate companyID_Cleanup2

declare db_name_Cleanup CURSOR for
select ”delete ” + rtrim(o.name) + ” where db_name <> ””’ + rtrim(@GPSystem) + ””’
and db_name <> ”””” and db_name not in (select INTERID from SY01500)”
from sysobjects o join syscolumns c on o.id = c.id and o.type = ”U”
where c.name = ”db_name”
order by o.name
open db_name_Cleanup
fetch next from db_name_Cleanup into @statement
while (@@fetch_status <> -1) begin
exec (@statement)
fetch next from db_name_Cleanup into @statement
end
close db_name_Cleanup
deallocate db_name_Cleanup

declare dbname_Cleanup CURSOR for
select ”delete ” + rtrim(o.name) + ” where DBNAME <> ””’ + rtrim(@GPSystem) + ””’
and DBNAME <> ”””” and DBNAME not in (select INTERID from SY01500)”
from sysobjects o join syscolumns c on o.id = c.id and o.type = ”U”
where c.name = ”DBNAME” and o.name not in (”SLB10100”,”ERB10100”,”NLB10100”)
order by o.name
open dbname_Cleanup
fetch next from dbname_Cleanup into @statement
while (@@fetch_status <> -1) begin
exec (@statement)
fetch next from dbname_Cleanup into @statement
end
close dbname_Cleanup
deallocate dbname_Cleanup

delete SY40502 where BARULEID not in (select BARULEID from SY40500)
delete SY40503 where BARULEID not in (select BARULEID from SY40500)
delete SY40504 where BARULEID not in (select BARULEID from SY40500)
delete SY40505 where BARULEID not in (select BARULEID from SY40500)
delete SY40506 where BARULEID not in (select BARULEID from SY40500)’
exec (@Statement)
fetch next from CleanGPSystem into @GPSystem
end
close CleanGPSystem
deallocate CleanGPSystem

drop table ##GPSystem
set nocount off
go

Hope this helps!!!

How to change SQL server Collation?

SQL server collation is very important for Installing, Migrating (Dynamics GP from one server to another) and upgrade Dynamics GP. We always need to use standard collation which is mentioned in GP installation guide. Suppose if we want to change the SQL server collation after everything was configured in SQL server, we can rebuild our SQL server database with following below steps.

Before change the collation of SQL server, we need take a backup of all databases(lDF and MDF files) used within the SQL server instance. Then run the below command to change the SQL server collation.

  1. open Command Prompt with administrator.
  2. Then go to the SQL server media file location. (eg cd “D:\SQL 2012\”)
  3. Run the below command.

SETUP.EXE /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=Your Instance Name /SAPWD=SAPassword /SQLCOLLATION=New Collation Name /SQLSYSADMINACCOUNTS=SQL Admin Account

Example:

SETUP.EXE /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SOMA\GP1 /SAPWD=Welcome.123 /SQLCOLLATION=Arabic_CI_AI /SQLSYSADMINACCOUNTS=”SOMA\Administrator”

4. After changed the collation open the SQL server management studio and attach all the database LDF and MDF file which was taken backup before changing the collation.

Hope this helps!!!

SQL query for Analytical Accounting Transaction Details in Dynamics GP

Please use the below query to get all open and historical year analytical accounting transaction details with journal entry number.
SELECT [‘aaGLHdr’].[year1]
AS
‘Year’,
[‘aaGLHdr’].[jrnentry]
AS ‘Journal Entry’,
Rtrim([‘aaGLHdr’].[aagltrxsource])
AS ‘Transaction Source’,
(SELECT Rtrim([actnumst])
FROM [gl00105] AS [‘Account Index Master’]
WHERE [‘Account Index Master’].[actindx] =
[‘Account Master’].[actindx]) AS
‘Account Number’,
[‘aaGLAssign’].[debitamt]
AS ‘Debit Amount’,
[‘aaGLAssign’].[crdtamnt]
AS ‘Credit Amount’,
Rtrim([‘aaTrxDim’].[aatrxdim])
AS ‘Trx Dimension’,
Rtrim([‘aaTrxDim’].[aatrxdimdescr])
AS ‘Trx Dimension Description’,
Rtrim([‘aaTrxDimCodeSetp’].[aatrxdimcode])
AS ‘Trx Dimension Code’,
Rtrim([‘aaTrxDimCodeSetp’].[aatrxdimcodedescr])
AS ‘Trx Dim Code Description’,
[‘aaGLHdr’].[glpostdt]
AS ‘GL Posting Date’,
[‘aaGLHdr’].[aaglhdrid]
AS ‘AA Header ID’,
[‘aaGLDist’].[aagldistid]
AS ‘AA Distribution ID’,
[‘aaGLAssign’].[aaglassignid]
AS ‘AA Assignment ID’,
[‘aaGLAssign’].[aaassignedpercent] / 100
AS ‘Assignment Percentage’,
Rtrim([‘aaGLHdr’].[aatrxsource])
AS ‘AA Transaction Source’,
[‘Account Master’].[actindx]
AS ‘Account Index’,
Rtrim([‘Account Master’].[actdescr])
AS ‘Account Description’,
Rtrim([dbo].[Dyn_func_account_type]([‘aaGLDist’].[accttype]))
AS ‘Account Type’,
Rtrim(Isnull([‘aaAliasMstr’].[aaalias], ”))
AS ‘Alias’,
Rtrim([‘aaGLDist’].[aaassetid])
AS ‘Asset ID’,
Rtrim([‘aaGLDist’].[aabookid])
AS ‘Book ID’,
Rtrim([‘aaGLDist’].[aacustid])
AS ‘Customer ID’,
Rtrim([‘aaGLDist’].[employid])
AS ‘Employee ID’,
Rtrim([‘aaGLDist’].[aaitemid])
AS ‘Item Number’,
Rtrim([‘aaGLDist’].[aasiteid])
AS ‘Site ID’,
Rtrim([‘aaGLDist’].[aavendid])
AS ‘Vendor ID’,
Rtrim([‘aaGLDist’].[curncyid])
AS ‘Currency ID’,
[‘aaGLDist’].[currnidx]
AS ‘Currency Index’,
[‘aaGLDist’].[denxrate]
AS ‘Denomination Exchange Rate’,
[‘aaGLDist’].[exchdate]
AS ‘Exchange Date’,
Rtrim([‘aaGLDist’].[exgtblid])
AS ‘Exchange Table ID’,
[‘aaGLDist’].[time1]
AS ‘Exchange Time’,
Rtrim([‘aaGLDist’].[interid])
AS ‘Intercompany ID’,
[‘aaGLDist’].[mctrxstt]
AS ‘MC Transaction State’,
[‘aaGLAssign’].[ordbtamt]
AS ‘Originating Debit Amount’,
[‘aaGLAssign’].[orcrdamt]
AS ‘Originating Credit Amount’,
Rtrim([dbo].[Dyn_func_rate_calculation_method]([‘aaGLDist’].[rtclcmtd]))
AS
‘Rate Calculation Method’,
Rtrim([‘aaGLDist’].[ratetpid])
AS ‘Rate Type ID’,
Rtrim([‘aaGLAssign’].[distref])
AS ‘Distribution Reference’,
Rtrim([dbo].[Dyn_func_gl_ledger_description]([‘aaGLHdr’].[ledger_id]))
AS
‘Ledger Description’,
Rtrim([dbo].[Dyn_func_gl_ledger_name]([‘aaGLHdr’].[ledger_id]))
AS ‘Ledger Name’,
[‘aaGLHdr’].[rctrxseq]
AS ‘Recurring TRX Sequence’,
[‘aaGLDist’].[seqnumbr]
AS ‘Sequence Number’,
[‘aaGLDist’].[aachangedate]
AS ‘Change Date’,
[‘aaGLDist’].[aachangetime]
AS ‘Change Time’
FROM [aag30003] AS [‘aaGLCode’] WITH (nolock)
LEFT OUTER JOIN [aag30001] AS [‘aaGLDist’] WITH (nolock)
ON [‘aaGLCode’].[aaglhdrid] = [‘aaGLDist’].[aaglhdrid]
AND [‘aaGLCode’].[aagldistid] = [‘aaGLDist’].[aagldistid]
LEFT OUTER JOIN [aag30000] AS [‘aaGLHdr’] WITH (nolock)
ON [‘aaGLCode’].[aaglhdrid] = [‘aaGLHdr’].[aaglhdrid]
AND [‘aaGLCode’].[aaglhdrid] = [‘aaGLHdr’].[aaglhdrid]
LEFT OUTER JOIN [gl00100] AS [‘Account Master’] WITH (nolock)
ON [‘aaGLDist’].[actindx] = [‘Account Master’].[actindx]
LEFT OUTER JOIN [aag00401] AS [‘aaTrxDimCodeSetp’] WITH (nolock)
ON [‘aaGLCode’].[aatrxdimid] =
[‘aaTrxDimCodeSetp’].[aatrxdimid]
AND [‘aaGLCode’].[aatrxcodeid] =
[‘aaTrxDimCodeSetp’].[aatrxdimcodeid]
LEFT OUTER JOIN [aag30002] AS [‘aaGLAssign’] WITH (nolock)
ON [‘aaGLCode’].[aaglhdrid] = [‘aaGLAssign’].[aaglhdrid]
AND [‘aaGLCode’].[aagldistid] =
[‘aaGLAssign’].[aagldistid]
AND [‘aaGLCode’].[aaglassignid] =
[‘aaGLAssign’].[aaglassignid]
LEFT OUTER JOIN [aag00800] AS [‘aaAliasMstr’] WITH (nolock)
ON [‘aaGLAssign’].[aaaliasid] = [‘aaAliasMstr’].[aaaliasid]
LEFT OUTER JOIN [aag00400] AS [‘aaTrxDim’] WITH (nolock)
ON [‘aaGLCode’].[aatrxdimid] = [‘aaTrxDim’].[aatrxdimid]

UNION ALL
–Historical Data

SELECT [‘aaGLHdr’].[year1]
AS
‘Year’,
[‘aaGLHdr’].[jrnentry]
AS ‘Journal Entry’,
Rtrim([‘aaGLHdr’].[aagltrxsource])
AS ‘Transaction Source’,
(SELECT Rtrim([actnumst])
FROM [gl00105] AS [‘Account Index Master’]
WHERE [‘Account Index Master’].[actindx] =
[‘Account Master’].[actindx]) AS
‘Account Number’,
[‘aaGLAssign’].[debitamt]
AS ‘Debit Amount’,
[‘aaGLAssign’].[crdtamnt]
AS ‘Credit Amount’,
Rtrim([‘aaTrxDim’].[aatrxdim])
AS ‘Trx Dimension’,
Rtrim([‘aaTrxDim’].[aatrxdimdescr])
AS ‘Trx Dimension Description’,
Rtrim([‘aaTrxDimCodeSetp’].[aatrxdimcode])
AS ‘Trx Dimension Code’,
Rtrim([‘aaTrxDimCodeSetp’].[aatrxdimcodedescr])
AS ‘Trx Dim Code Description’,
[‘aaGLHdr’].[glpostdt]
AS ‘GL Posting Date’,
[‘aaGLHdr’].[aaglhdrid]
AS ‘AA Header ID’,
[‘aaGLDist’].[aagldistid]
AS ‘AA Distribution ID’,
[‘aaGLAssign’].[aaglassignid]
AS ‘AA Assignment ID’,
[‘aaGLAssign’].[aaassignedpercent] / 100
AS ‘Assignment Percentage’,
Rtrim([‘aaGLHdr’].[aatrxsource])
AS ‘AA Transaction Source’,
[‘Account Master’].[actindx]
AS ‘Account Index’,
Rtrim([‘Account Master’].[actdescr])
AS ‘Account Description’,
Rtrim([dbo].[Dyn_func_account_type]([‘aaGLDist’].[accttype]))
AS ‘Account Type’,
” AS ‘Alias’,
Rtrim([‘aaGLDist’].[aaassetid])
AS ‘Asset ID’,
Rtrim([‘aaGLDist’].[aabookid])
AS ‘Book ID’,
Rtrim([‘aaGLDist’].[aacustid])
AS ‘Customer ID’,
Rtrim([‘aaGLDist’].[employid])
AS ‘Employee ID’,
Rtrim([‘aaGLDist’].[aaitemid])
AS ‘Item Number’,
Rtrim([‘aaGLDist’].[aasiteid])
AS ‘Site ID’,
Rtrim([‘aaGLDist’].[aavendid])
AS ‘Vendor ID’,
Rtrim([‘aaGLDist’].[curncyid])
AS ‘Currency ID’,
[‘aaGLDist’].[currnidx]
AS ‘Currency Index’,
[‘aaGLDist’].[denxrate]
AS ‘Denomination Exchange Rate’,
[‘aaGLDist’].[exchdate]
AS ‘Exchange Date’,
Rtrim([‘aaGLDist’].[exgtblid])
AS ‘Exchange Table ID’,
[‘aaGLDist’].[time1]
AS ‘Exchange Time’,
Rtrim([‘aaGLDist’].[interid])
AS ‘Intercompany ID’,
[‘aaGLDist’].[mctrxstt]
AS ‘MC Transaction State’,
[‘aaGLAssign’].[ordbtamt]
AS ‘Originating Debit Amount’,
[‘aaGLAssign’].[orcrdamt]
AS ‘Originating Credit Amount’,
Rtrim([dbo].[Dyn_func_rate_calculation_method]([‘aaGLDist’].[rtclcmtd]))
AS
‘Rate Calculation Method’,
Rtrim([‘aaGLDist’].[ratetpid])
AS ‘Rate Type ID’,
Rtrim([‘aaGLAssign’].[distref])
AS ‘Distribution Reference’,
Rtrim([dbo].[Dyn_func_gl_ledger_description]([‘aaGLHdr’].[ledger_id]))
AS
‘Ledger Description’,
Rtrim([dbo].[Dyn_func_gl_ledger_name]([‘aaGLHdr’].[ledger_id]))
AS ‘Ledger Name’,
[‘aaGLHdr’].[rctrxseq]
AS ‘Recurring TRX Sequence’,
[‘aaGLDist’].[seqnumbr]
AS ‘Sequence Number’,
[‘aaGLDist’].[aachangedate]
AS ‘Change Date’,
[‘aaGLDist’].[aachangetime]
AS ‘Change Time’
FROM [aag40003] AS [‘aaGLCode’] WITH (nolock)
LEFT OUTER JOIN [aag40001] AS [‘aaGLDist’] WITH (nolock)
ON [‘aaGLCode’].[aaglhdrid] = [‘aaGLDist’].[aaglhdrid]
AND [‘aaGLCode’].[aagldistid] = [‘aaGLDist’].[aagldistid]
LEFT OUTER JOIN [aag40000] AS [‘aaGLHdr’] WITH (nolock)
ON [‘aaGLCode’].[aaglhdrid] = [‘aaGLHdr’].[aaglhdrid]
AND [‘aaGLCode’].[aaglhdrid] = [‘aaGLHdr’].[aaglhdrid]
LEFT OUTER JOIN [gl00100] AS [‘Account Master’] WITH (nolock)
ON [‘aaGLDist’].[actindx] = [‘Account Master’].[actindx]
LEFT OUTER JOIN [aag00401] AS [‘aaTrxDimCodeSetp’] WITH (nolock)
ON [‘aaGLCode’].[aatrxdimid] =
[‘aaTrxDimCodeSetp’].[aatrxdimid]
AND [‘aaGLCode’].[aatrxcodeid] =
[‘aaTrxDimCodeSetp’].[aatrxdimcodeid]
LEFT OUTER JOIN [aag40002] AS [‘aaGLAssign’] WITH (nolock)
ON [‘aaGLCode’].[aaglhdrid] = [‘aaGLAssign’].[aaglhdrid]
AND [‘aaGLCode’].[aagldistid] =
[‘aaGLAssign’].[aagldistid]
AND [‘aaGLCode’].[aaglassignid] =
[‘aaGLAssign’].[aaglassignid]
LEFT OUTER JOIN [aag00400] AS [‘aaTrxDim’] WITH (nolock)
ON [‘aaGLCode’].[aatrxdimid] = [‘aaTrxDim’].[aatrxdimid]

Hope this helps!!!

Import Customers using Data Import framework in AX 2012 R3

  1. Open AX application and select the Appropriate company where you want to import your sample customers.
  2. Go to Data import export framework window and select the “Data import/export parameter” under the setup section.     1
  3. From this “Data import/export parameter” window set your desired common path for data import and export and click validate to verify the path then close the window.2
  4. In the same window, click the Processing group under the common section.3
  5. Enter the Group Name and description for your sample customer import process. Then select the group name and click the Entities button.4
  6. Click “New” button from this window and select the Entity as “Customer” then select the source data format. If you already defined the source data format, just select it and go to step 8 or right click from this field and select view details to define your new source data format.5
  7. In the Source data formats widow create a new source name CSV and define the file format and delimiter as shown below screen shot and close this window.6
  8. Select the Source data format as CSV and click the “Generate source file”.7
  9. Click “Next”.8
  10. By default, the mandatory fields are marked automatically. You can also select additional fields as you desired (eg. Here I have selected Name field as additionally). Then click the “Generate sample file”.9
  11. The sample file will generate as shown below. You need to save this file as CSV format to the path specified in step 3.10
  12. Click “Finish”.11
  13. Open the saved file and enter the customer details as shown below.12
  14. Then select the saved CSV file by clicking the browse icon.13
  15. Now select the “Preview source file” and verify the data.1415
  16. Then click “Get staging data”.16
  17. Click OK button.                                                                                                                                17
  18. Click Run and press OK to process the sample data to staging table.                                                          18 1919 20
  19. After successfully processed the staging data process. Click “Copy data to target” to move the staging table data to customer table.21
  20. Select and Run the job to process the staging data as shown below screenshots.       22 23 24 25
  21. Once the job successfully run, go to All customers and you can see the new customers.26

Hope this helps!!!

Document Attachment Preview issue in GP 2015R2

As we are all know GP 2015 R2 has been released with more features. Hope we liked all. But, I faced an issue while viewing attached documents other than sa user.

To fix this issue we need update the latest hot fix from Microsoft. Please use the below link to download and update your GP upto date.

Fix list:

There were two issues fixed in this patch that are critical for our users.

  1. Updating Custom Field in an Alternate Modified Form not working with SBA – this is mainly for ISV’s that are writing their products and using SBA (service based architecture)
  2. Document attach functionality is not working for any user other than ‘sa’.

Hotfix Includes R2 and Canadian Payroll Mid-year Tax Update – Version Number: 14.00.0804

https://mbs.microsoft.com/customersource/northamerica/GP/downloads/service-packs/MDGP2015_PatchReleases

Hope this helps!!!

Purchasing All-in-one Document Viewer – Microsoft Dynamics GP 2015

Purchasing All-in-One View window is available in Microsoft Dynamics GP 2015 R2 for locating vendor records and displaying related purchasing documents in the same window.

We can use the Purchasing All-in-One View window to select that vendor and then view all documents related to the payment, such as the purchase order, receipt, invoice, credits, miscellaneous charges and finance charges, and payment with having link to vendor’s inquiries.

You can access the Purchasing All-in-One View window from the following windows and navigation lists:

  • Your home page
  • Vendor Maintenance
  • Purchase Order Processing Document Inquiry
  • Vendor Inquiry
  • Payables Transaction Inquiry – Vendor
  • Payables Transaction Inquiry – Document
  • Purchase Order Processing Document Inquiry
  • Purchase Order Processing Item Inquiry
  • Navigation lists and any views of these lists
  • Vendors
  • All Purchasing Transactions
  • Payables Transactions
  • Purchase Order Transactions

To directly open Purchasing All-in-One View window, go to Purchasing >> Inquiry >> All-in-One View, or Home >>
Quick Links >> All-in-One View.

Untitled

This window opens without any data. You can specify the vendor and the details of the
information to display in the window. Each section there is an filter button available to view particular records.

Top of the window there is an Options button available, it will allows us to select which columns and document types to display in the window.

For example, if you don’t use Purchase Orders, clear the Purchase Orders selection and the first column displays receipts. The Credit Documents column marked to display by default, but with this column marked, up to six columns of data can be shown.
You can view the last five transactions by document date, arranged by the newest to the oldest in each column. Use the sort order ( ) buttons to change the order for transaction information. You can view the next set of transactions by clicking Next 5.

Hope this helps!!!