Often in the course of a customer implementation I've found myself thinking "Why don't they keep a list of their important GP setup?" I know the answer to that, but that's another post. I'd really like something like this:
System SQL Server: 9.00.1399.06, RTM, Developer Edition (64-bit) TWO eConnect Version: 10.0.0.0 TWO Company Name: Fabrikam, Inc. TWO Sitename: Two , Inc TWO Multi-currency: True TWO Functional Currency: Z-US$ TWO Functional Currency Format: #,##0.00 TWO Functional Currency Decimals: 2 TWO Default Site: NORTH TWO Multiple Bins: True TWO Qty Shortage Option: Back Order Balance TWO Extended Pricing: N\A TWO SOP Setup Options: Auto-Assign Lot Nums: False TWO SOP Setup Options: Auto-Assign Serial Nums: True TWO Prices Not Required In Price List: True TWO Pickup Ship Methods: PICKUP, TWO Deliver Ship Methods: COURIER, DROP SHIP, EXPRESS MAIL, OVERNIGHT, LOCAL DELIVERY, MAIL, TWO DAY, GROUND, INTERNATIONAL, NEXT DAY, TWO "Use separate Fulfillment Process" Checked: FUL, SPECORD, ORDMAN, TWO "Use separate Fulfillment Process" Un-Checked: PHNORD, REPORD, STDORD, RMAORD, DISTRIBUTION,
Well, I sat down and wrote a SQL script to show me exactly that! Here it is; hope you find it helpful too.
use TWO
DECLARE @dbname char(30)
set @dbname = DB_NAME()
DECLARE @spGPSettings TABLE (
DBName char(30),
Config char(50),
Value char(500)
);
declare @tempstr varchar(1024)
--DECLARE @sqlstr char(500)
--set @sqlstr = 'use ' + @dbname + ''
-- SQL Server Version
insert into @spGPSettings
SELECT 'System', Config='SQL Server:', Value=rtrim(convert(char, SERVERPROPERTY('productversion'))) + ', ' + rtrim(convert(char, SERVERPROPERTY ('productlevel'))) + ', ' + rtrim(convert(char, SERVERPROPERTY ('edition')))
-- eConnect
declare @spTempTable table (DatabaseName char(20), Version char(20), CompanyName char(50));
insert into @spTempTable
exec DYNAMICS..taeConnectVersionInfoDYNAMICS
insert into @spGPSettings
select @dbname, Config='eConnect Version:', Value=convert(char, Version) from @spTempTable where DatabaseName = @dbname
insert into @spGPSettings
select @dbname, Config='Company Name:', Value=convert(char, CompanyName) from @spTempTable where DatabaseName = @dbname
-- company
insert into @spGPSettings
select @dbname, Config='Sitename:', Value=isnull(rtrim(SITENAME), '') from DYNAMICS..SY03500
-- Multi Currency
if (select count(*) from CM00100 where CURNCYID <> '')>0 begin
insert into @spGPSettings
select @dbname, Config='Multi-currency:', Value='True'
end else if (select count(*) from CM00100 where CURNCYID <> '') = 0 begin
insert into @spGPSettings
select @dbname, Config='Multi-currency:', Value='False'
end
-- Currency Setup
insert into @spGPSettings
select
@dbname, Config='Functional Currency:',
Value=rtrim(isnull(funcCurr.CURNCYID, ''))
from MC40000 as currSetup (nolock)
left join DYNAMICS..MC40200 as funcCurr (nolock) on funcCurr.CURNCYID=currSetup.FUNLCURR
insert into @spGPSettings
select
@dbname, Config='Functional Currency Format:',
Value=cast(replace(rtrim('#,##0 ' + substring('00000',1, isnull((select top 1 DECPLCUR-1 from DYNAMICS..MC40200 as a join MC40000 as b on a.CURRNIDX=b.FUNCRIDX), 2))), ' ', '.') as varchar(15))
from MC40000 as currSetup (nolock)
left join DYNAMICS..MC40200 as funcCurr (nolock) on funcCurr.CURNCYID=currSetup.FUNLCURR
insert into @spGPSettings
select
@dbname, Config='Functional Currency Decimals:',
Value=cast(isnull(funcCurr.DECPLCUR ,3)-1 as char)
from MC40000 as currSetup (nolock)
left join DYNAMICS..MC40200 as funcCurr (nolock) on funcCurr.CURNCYID=currSetup.FUNLCURR
-- default warehouse
insert into @spGPSettings
select @dbname, Config='Default Site:', Value=LOCNCODE from SOP40100
-- multiple bins
insert into @spGPSettings
select @dbname, Config='Multiple Bins:', Value=case ENABLEMULTIBIN when 1 then 'True' else 'False' end from IV40100
-- Qty Shortage Option
insert into @spGPSettings
select @dbname, Config='Qty Shortage Option:', Value=case DFQTYSOP when 1 then 'Override Shortage' when 2 then 'Sell Balance' when 3 then 'Back Order Balance' when 4 then 'Back Order All' when 5 then 'Cancel Balance' when 6 then 'Cancel All' when 7 then 'Distribute' end from SOP40100
-- Extended Price (?)
if (select count(EXPRINST) from DYNAMICS..SY05501 where CMPANYID=(select CMPANYID from DYNAMICS..SY01500 (nolock) where INTERID=@dbname))>0 begin
insert into @spGPSettings
select @dbname, Config='Extended Pricing:', Value='Installed'
end else if (select count(EXPRINST) from DYNAMICS..SY05501 where CMPANYID=(select CMPANYID from DYNAMICS..SY01500 (nolock) where INTERID=@dbname))=0 begin
insert into @spGPSettings
select @dbname, Config='Extended Pricing:', Value='N\A'
end
-- sop setup: auto-assign l/n, s/n
insert into @spGPSettings
select @dbname, Config='SOP Setup Options: Auto-Assign Lot Nums:', Value=case SOPALLOW_3 when 1 then 'True' else 'False' end from SOP40100
insert into @spGPSettings
select @dbname, Config='SOP Setup Options: Auto-Assign Serial Nums:', Value=case SOPALLOW_2 when 1 then 'True' else 'False' end from SOP40100
-- Prices_Not_Required_In_Pr
insert into @spGPSettings
select @dbname, Config='Prices Not Required In Price List:', Value=case Prices_Not_Required_In_Pr when 1 then 'True' else 'False' end from SOP40100 (nolock)
-- Shipping methods
set @tempstr = ''
select @tempstr = coalesce(@tempstr, '', '') + rtrim(SHIPMTHD) + ', '
from SY03000 where SHIPTYPE = 0
insert into @spGPSettings
select @dbname, Config='Pickup Ship Methods:', Value=@tempstr
--
set @tempstr = ''
select @tempstr = coalesce(@tempstr, '', '') + rtrim(SHIPMTHD) + ', '
from SY03000 where SHIPTYPE = 1
insert into @spGPSettings
select @dbname, Config='Deliver Ship Methods:', Value=@tempstr
-- docs with separate fulfillment process
set @tempstr = ''
SELECT @tempstr = coalesce(@tempstr, '', '') + rtrim(DOCID) + ', '
FROM TWO.dbo.SOP40200 WITH ( NOLOCK) WHERE SOPTYPE = 2 and USPFULPR = 1
insert into @spGPSettings
select @dbname, Config='"Use separate Fulfillment Process" Checked:', Value=substring(@tempstr, 0, 500)
--
set @tempstr = ''
SELECT @tempstr = coalesce(@tempstr, '', '') + rtrim(DOCID) + ', '
FROM TWO.dbo.SOP40200 WITH ( NOLOCK) WHERE SOPTYPE = 2 and USPFULPR = 0
insert into @spGPSettings
select @dbname, Config='"Use separate Fulfillment Process" Un-Checked:', Value=substring(@tempstr, 0, 500)
select * from @spGPSettings