SQL脚本得到Epicor客制化信息

由于Epicor没有一个界面可以检查系统的客制化信息，如果你在系统中有很多的客制化，那追踪起来就很麻烦，通过下面的SQL Script可以轻松的得到这些数据。

–Get 自定义的BAQ

select Company,QueryID,AuthorID,Description,DisplayPhrase,IsGlobal,IsShared,SystemFlag,Version,Updatable,SystemFlag 
from  Ice.QueryHdr 
where SystemFlag=0 and IsShared=1 
order by QueryID, AuthorID

–Get 自定义的BPM

select Company,
Case when Source='BO' then 'BPM' 
     when Source='DB' then 'DataDirectives'
	 when Source='DQ' then 'BAQMethod' 
 else 'Other' end Source,
DirectiveGroup,BpMethodCode,DirectiveType,Name,[Order],
case when IsEnabled=1 then 'InUse' else 'Obsolete' end [Status],Body 
from  Ice.BpDirective 
where Thumbnail<>'' 
order by DirectiveGroup desc,BpMethodCode,[Order]

–Get 自定义的Dashboard

select Company,ProductID,DefinitionID,Description,DashBdVersion,DataBaseVersion,
LastDeployedBy,LastDeployedDate,LastUpdatedBy,LastUpdated,
DashboardSchema,DashboardAssembly,HasDashboardAssembly 
from Ice.DashBdDef 
where SystemFlag=0 
order by DefinitionID

–Get 自定义的Customization form

select Company,ProductID,TypeCode,Key1 as CustIDOrName,Description,
Key2 as AppForm,LastUpdatedBy,LastUpdated,SysCharacter04 as [Status],
CommentText
from Ice.XXXDef where SystemFlag=0 and TypeCode in ('Customization') and Key1<>'CustomContextMenu'
Order by Key1

–Get 自定义的QuickSearch

select Company,QuickSearchID,Description,ExportID,LikeDataFieldTableID,LikeDataFieldName,ReturnFieldTableID,ReturnFieldName,
CallFrom,UserID,Version,IsShared,BaseDefault
 from Ice.QuickSearch where SystemFlag=0 order by QuickSearchID

–Get 自定义的BAQ Report

select Company,BAQRptID,Description,FormTitle,ReportTitle,ExportID,ReportID,SSRSReportName,IsCrystalReport 
from Ice.BAQReport
where SystemFlag=0

–Get 自定义的Posting Rules

select b.Company,b.ACTTypeUID,a.ACTRevisionUID,b.DisplayName,b.DetailedDescription,
       a.RevisionCode,a.Description,a.RevisionStatus,
	   a.SendToReviewJournal,a.IsDefault,a.RType
     from Erp.ACTRevision a
Left Join Erp.ACTType     b ON a.ACTTypeUID=b.ACTTypeUID
 where a.RevisionStatus='Active' and a.RevisionCode<>'Base Std'

–Get 自定义的BAQ & WhereUsed

select a.Company,a.QueryID,a.AuthorID,a.Description,a.DisplayPhrase,a.IsGlobal,
       a.IsShared,a.SystemFlag,a.Version,a.Updatable,a.SystemFlag,
	   b.Description as WhereUsedQuickSearch,
	   c.ReportID as WhereUsedRptID,
	   c.Description as WhereUsedRptDesc,
	   c.SSRSReportName as SSRSReportName,
       d.DefinitionID as WhereUsedDashBd
     from Ice.QueryHdr    a
Left Join Ice.QuickSearch b ON a.QueryID=b.ExportID  
Left Join Ice.BAQReport   c ON a.QueryID=c.BAQRptID
Left Join Ice.DashBdBAQ   d ON a.QueryID=d.QueryID
where a.SystemFlag=0 and a.IsShared=1 
order by a.QueryID, a.AuthorID

–Get UD Field

select SystemCode,DataTableID,DBTableName,FieldName,Seq,Description,DataType,UDRequired,UDReadOnly,FieldFormat
  from Ice.ZDataField 
  where DataTableID in (select DataTableID from Ice.ZDataTable where TableType='UD') and FieldName not in ('UD_SysRevID','ForeignSysRowID')