Emerald 4.0 Database Structure

AccountTypes

Column	Datatype	Null	Description
AccountTypeID	int	IDENTITY	IDENTITY / AutoNumber
AccountType	varchar(30)	NO	Name of the service type
Cost	numeric(18,4)		The base monthly cost of the service type
Null	varchar(64)		Null Option of the service type
ApplyDiscount	smallint		Whether to apply termed discounts or not.
TaxID	int		The TaxID of the associated Tax (or NULL for none).
RateID			The Rate the service type is associated to.
ChargeTypeID			The ChargeTypeID of the associated Charge (or NULL for none).
ExternalSystemID			External System the service type is associated to (or NULL for none).
GroupID			The GroupID of the associated Group (or NULL for global).
DNISGroupID			The DNIS Group the Account Type can dial in from (or NULL if not applicable).
SortOrder	smallint	NO	The sort order when displayed in a list (lowest displayed first).
OverDue			Default extension, in days, to the Expiration date.
GLCodeID	int		Reserved
LoginLimit			The default currency login limit for the SubAccount.
TimeLeft			The default login time left for the SubAccount. This should be set to NULL if no time limit is set.
HomeDirSize			Default home directory size limit.
SendBill	smallint		Default per service send bill options
RemoteAccess			Default remote access setting
CommissionType	int
CommissionValue
PayPeriodID			Default pay period
RatingOptionID			This table is to manage the rating options available within Emerald.  For example, Emerald GBE, rate-rec, other integrated external rating engines.  One entry per rating engine available The table contains the rating engine identification, as well as the configuration of the communication between the two systems. It may be a possibility down the road to allow more than one processing configuration per Rating Engine -- processing would then be determined by the data record Account Type instead of just the associated Rating Engine.
DataSourceConfigID			This table stores the configuration data necessary to track and manage data source input/output into our system.

Actions

Column	Datatype	Null Option	Description
ActionID	int	IDENTITY
IncidentID		NO
CreateDate	datetime
Operator	varchar(15)
Null	text
Hide	int	NO
LastModifyDate	datetime
LastModifyUser	varchar(32)
LMD_TS	timestamp

Addresses

Column	Datatype	Null Option	Description
AddressID	int	IDENTITY	IDENTITY / AutoNumber
CustomerID			CustomerID of account to associate address information with.
AddressTypeID		NO	Type of address from the AddressTypes table.
Address1	varchar(50)		Address 1
Address2			Address 2
City	varchar(40)		City
State			State
Zip	varchar(15)		Zip
Province	varchar(40)		Province
Country			Country
FirstName	varchar(25)		First Name
LastName			Last Name
Phone	varchar(20)		Phone

AddressTypes

Column	Datatype	Null Option	Description
AddressTypeID	int	IDENTITY	IDENTITY / AutoNumber
AddressType	varchar(25)	NO	Name of address type.
SortOrder	int

Aliases

Column	Datatype	Null Option	Description
AliasID	int	IDENTITY	IDENTITY / AutoNumber
Alias	varchar(64)	NO	Mail alias of the associated service.
AccountID	int		The service this alias is for.
CreateDate	datetime		Alias creation date.
LastModifyDate			The date the record was last modified by Emerald.
LastModifyUser	varchar(32)		The user to last modify this record by Emerald.

AllowedHosts

Column	Datatype	Null Option	Description
AllowedHostID	int	IDENTITY
OperatorGroupID
IPAddress	varchar(32)	NO
Null	varchar(100)

Applications

Column	Datatype	Null Option	Description
ApplicationID	int	NO	Application ID number.
AppName	varchar(32)		Application name

AuditLogs

Column	Datatype	Null Option	Description
LogMsgID	int	NO
LogDate	datetime
LogMsg	varchar(63)
Operator	varchar(32)
CustomerID	int
AccountID

BankTrans

Column	Datatype	Null Option	Description
CustomerID	int	NO	The MBR this Bank information is for.
AuthName	varchar(50)		The credit card authorization name, if the customer is paying via credit card.
BankName	varchar(32)		Bank Name.
ABANumber	varchar(16)		Bank routing number.
AccountNumber			Bank account number.
TransType			Transaction type ’Savings’ or ’Checking’
LastModifyDate	datetime	NO	The date the record was last modified by Emerald.
LastModifyUser	varchar(32)		The user to last modify this record by Emerald.

BannedCustomers

Column	Datatype	Null Option	Description
BannedCustomerID	int	IDENTITY
CallerID	varchar(30)	NO
IPAddress
Null	varchar(100)
Reason	varchar(255)

BillingCycles

Column	Datatype	Null Option	Description
BillingCycleID	int	IDENTITY	IDENTITY / AutoNumber
Null	varchar(45)	NO	Null Option of the billing cycle.
CycleType	smallint		Cycle Type 0=Anniversary, 1=Monthly, 2=Calendar
AutoBill			Whether accounts will be billed Balance forward (1) or Renewal (0).
BillDay	int		Day of month to align billing if CycleType is monthly.
IBillDays		NO	How many days in advance to bill postal Invoice customers.
EBillDays			How many days in advance to bill email Invoice customers.
SortOrder	smallint
GroupID	int		The GroupID of the associated Group (Or NULL for global)

Calls

Column	Datatype	Null Option	Description
NASIdentifier	varchar(16)	NO	Identifier for the NAS. This is typically the IP Address of the NAS.
AcctSessionID			NAS generated unique ID for the call
AcctStatusType	tinyint
NASPort	varchar(10)		NAS Port the call came in on
Username	varchar(40)		Username of the caller
CallDate	smalldatetime		Date of the Call
AcctDelayTime	int		The number of seconds representing how long the record was held in the NAS before being recorded to the database.
AcctSessionTime			The number of seconds the session was active.
FramedAddress	varchar(16)		The IP Address the user was assigned, if the service type was a framed service.
AcctInputOctets	numeric(10,0)		The number of octets (bytes) the user sent (from the user to the NAS).
AcctOutputOctets			The number of octets (bytes) the user received (from the NAS to the user).
AcctTerminateCause	smallint		Session termination reason: 1 - User Request 2 - Lost Carrier 3 - Lost Service 4 - Idle Timeout 5 - Session Timeout 6 - Admin Reset 7 - Admin Reboot 8 - Port Error 9 - NAS Error 10 - NAS Request 11 - NAS Reboot 12 - Port Unneeded 13 - Port Preempted 14 - Port Suspended 15 - Service Unavailable 16 - Callback 17 - User Error 18 - Host Request
NASPortType	tinyint		Indicates the type of the physical port of the NAS which is authenticating the user. 0 - Async 1 - Sync 2 - ISDN Sync 3 - ISDN Async V.120 4 - ISDN Async V.110 5 - Virtual 6 - PIAFS 7 - HDLC Clear Channel 8 - X.25 9 - X.75 10 - G.3 Fax 11 - SDSL - Symmetric DSL 12 - ADSL-CAP - Asymmetric DSL, Carrierless Amplitude Phase Modulation 13 - ADSL-DMT - Asymmetric DSL, Discrete Multi-Tone 14 - IDSL - ISDN Digital Subscriber Line 15 - Ethernet 16 - xDSL - Digital Subscriber Line of unknown type 17 - Cable 18 - Wireless - Other 19 - Wireless - IEEE 802.11
NASPortDNIS	varchar(10)		Phone number call was placed to
CallerID	varchar(15)		Phone number call was placed from
ConnectInfo	varchar(32)		The connect string detailing the connection, if the NAS is digital.
ServerID	int		Radius server the authentication request was received by.  NULL if unknown.
AccountID			AccountID of the Sub Account the authentication request was authenticated against.  NULL if unknown.

CancelReasons

Column	Datatype	Null Option	Description
CancelReasonID	int	IDENTITY
CancelNull Option	varchar(100)	NO
SortOrder	smallint

CCImportColumns

Column	Datatype	Null Option	Description
CCImportColumnID	int	IDENTITY	IDENTITY / AutoNumber
ExportID		NO	Export format this import column is associated with.
FieldType	smallint		Maps external data to it’s corrosponding internal field. 0 - Sink 1 - TransID 2 - Status 7 - Approve Code 8 - Response 9 - Required
SortOrder			Sort order is used to order deleimited records.  Sort order does has no effect on Fixed-width records.
Position			If a record is delimited Position refers to the starting offset in bytes within the delimited field. If a record is fixed with position is the absolute offset in bytes from the start of the record.
Null	varchar(64)		Null Option is a friendly Null Option of what each column stands for and generally has no effect on imported data.  The only exception may be if Exports.Filter is NOT NULL. If filter is ’urlencoded’ Null Option is a urlencoded variable ’$variable’ which make up this field.
Quoted	smallint	NO	Will imported data be quoted?
FieldSize			If the record is fixed length, this is the size in bytes of the field within this record.
ApproveValue	varchar(50)		If the transaction is approved the contents of this field are used as the data for this column.
DeclineValue			If the transaction is declined the contents of this field are used as the data for this column.
ErrorValue			If there is an error the contents of this field are used as the data for this column.
Line	int		Line number of current record the data for this field is on.

Charges

Column	Datatype	Null Option	Description
ChargeID	int	IDENTITY	IDENTITY / AutoNumber
CustomerID		NO	The MBR this charge is for.
Null	varchar(50)		A Null Option for this charge.
StartDate	datetime
EndDate
AccountID	int		The service this charge is for.
Login	varchar(40)	NO	Service login associated with this charge
AccountTypeID	int		Service type associated with this charge
Amount	numeric(18,4)	NO	Charge amount
Quantity	int
TaxID			If this item is taxable, this is the tax type for it (from the Taxes table).
ChargeTypeID			Type of charge from the ChargeTypes table.
ItemID			When the charge is applied to an invoice, this is the invoice item representing the charge.
Operator	varchar(32)	NO	The Operator who created this charge
LastModifyDate	datetime		The date therecord was last modified by Emerald.
LastModifyUser	varchar(32)		The user to last modify this record by Emerald.

ChargeTypes

Column	Datatype	Null Option	Description
ChargeTypeID	int	IDENTITY	IDENTITY / AutoNumber
Null	varchar(45)	NO	Null Option of the charge that will put on the Invoice line item.
Amount	numeric(18,4)		The amount of the charge.
TaxID	int		The TaxID of the associated Tax (or NULL for none).
GroupID			The GroupID of the associated Group (or NULL for global).
GLCodeID			Reserved
SortOrder	smallint	NO

ConfigGroups

Column	Datatype	Null Option	Description
ConfigGroupID	int	IDENTITY
Name	varchar(32)	NO
SortOrder	int

Configs

Column	Datatype	Null Option	Description
ConfigID	int	NO
ConfigGroupID
ConfigType	smallint
Value	int
Data	varchar(100)
Null	varchar(64)	NO
Template	varchar(32)
SortOrder	smallint
Edition	int
Feature

ConfigValues

Column	Datatype	Null Option	Description
ConfigID	int	NO	Related ID from Configs table.
Value			Value Number
ValueName	varchar(64)		Value Name

CreditCards

Column	Datatype	Null Option	Description
CustomerID	int	NO	MBR Associated with this credit card information.
CardHolder	varchar(50)		The credit card authorization name.
CardNumber	varchar(20)		The credit card number.
ExpirationDate	datetime		The credit card expire.
BillToAddress1	varchar(50)		The credit card holders address 1.
BillToAddress2			The credit card holders address 2.
BillToCity	varchar(40)		The credit card holders city.
BillToState			The credit card holders state.
BillToProvince			The credit card holders province.
BillToCountry			The credit card holders country.
BillToZip	varchar(15)		The credit card holders zip.
SecurityCode	varchar(16)		The credit card holders CVV2 security code.
Bank	varchar(50)		Name of bank issuing this card.
BankPhone	varchar(32)		Card issuers telephone number.
LastModifyUser			The user to last modify this record by Emerald.
LastModifyDate	datetime		The date the record was last modified by Emerald.

DataAttributeMapDetail

Column	Datatype	Null Option	Description
DataAttributeMapID	int	NO	Table used to trap the attribute mapping from data source to Emerald storage and/or other access
ColumnName	varchar(100)		The indiginous field/column name we are referring to
MappedAttribute			The external attribute which maps to the named ColumnName
StartPosition	int		Optional start position for parsing
EndPosition			Optional end position for parsing
Length			Optional field length for parsing
Required

DataAttributeMaps

Column	Datatype	Null Option	Description
DataAttributeMapID	int	IDENTITY	Table used to trap the attribute mapping from data source to Emerald storage and/or other access
Null	varchar(120)	NO
DataMapType	int		Import mapping: 0 Export mapping: 1 Import will generally denote mappings that are used for import into the Emerald system (storage/file parsing), Output will generally denote mappings that are used for export out of the Emerald system (file gen/array fills).

DataCollectors

Column	Datatype	Null Option	Description
DataCollectorID	int	IDENTITY	Table used to track data collection sources -- primarily for informational/labeling purposes only at this point.   Later this config may also have to track connection info the system may use to integrate with external systems (module name, ORB name, location, type, etc.)
DataCollectorName	varchar(40)	NO
Null	varchar(120)		Data collection Null Option -- such as: Emerald/RADIUS Emerald/NetFlow Livingston/RADIUS Free form to best describe source/location/type so that can be separated -- say like they want to define two IEA RADIUS streams, one local, one remote -- that they may want stored in different Emerald tables, or rated differently (down the road).
Vendor	varchar(40)	NO	Who created (for example, IEA Software)
Location	varchar(500)
Active	smallint

DataSourceConfigs

Column	Datatype	Null Option	Description
DataSourceConfigID	int	IDENTITY	This table stores the configuration data necessary to track and manage data source input/output into our system.
DataSourceName	varchar(40)	NO
Null	varchar(120)
DataSourceTypeID	int		What type of data
DataCollectorID			What is doing the collecting of this data
ExternalStoreFlag			Data stored internally within Emerald db: 0 Data stored Externally outside of Emerald db: 1
StoreMedium			Database: 0 Delimited File: 1 Formatted File: 2 etc.
DataLocation	varchar(256)	NO	Data access information -- where the data for this source is stored for Emerald billing use.  Use by defining a structure that can be parsed for required and/or optional information such as  "server*path*database name or server*file path.
SourceTableName	varchar(64)		If Emerald database, what is the
BillingTableName			Emerald table -- if defined, the system will store each individual record plus the returned charge in this separate table (for cases when the data being rated is stored externally and temporary rate auditing)
AccountIDColumn
DateColumn
TimeColumn
DataColumn
FilterString	varchar(256)
PreProcess	varchar(64)
LastProcessDate	datetime
LastProcessMarker	varchar(40)	NO
DataAttributeMapID	int		Identifies the mapping of Emerald info to externally generated data attributes
Active	smallint

DataSourceTypes

Column	Datatype	Null Option	Description
DataSourceTypeID	int	IDENTITY	This table to denotes the types of Data Sources that Emerald currently supports.  For example, VoIP, NetFlow, RADIUS, etc.
DataSourceType	varchar(40)	NO
Null	varchar(120)		General Data Types that we will support, such as: RADIUS VoIP Netflow etc.

Discounts

Column	Datatype	Null Option	Description
DiscountID	int	IDENTITY	IDENTITY / AutoNumber
Discount	varchar(20)	NO	Display name of the Discount.
Amount	numeric(18,4)		Discount amount
AmountType	smallint		Discount amount type.  0=percentage, 1=static
GroupID	int		The GroupID of the associated Group (Or NULL for global)
SortOrder	smallint	NO

DNISGroups

Column	Datatype	Null Option	Description
DNISGroupID	int	IDENTITY	Group identifier (IDENTITY)
DNISGroup	varchar(25)	NO	Name of the DNIS Group
Null	varchar(45)		Full Null Option of the DNIS Group.

DNISNumbers

Column	Datatype	Null Option	Description
DNISGroupID	int	NO	Related DNIS group identifier (foriegn key from DNISGroup table)
DNISNumber	varchar(10)		The DNIS number as reported by the Radius client

DomainData

Column	Datatype	Null Option	Description
DomainDataID	int	IDENTITY	Reserved
DomainID
DataTypeID		NO
Data	varchar(255)

DomainDataTypes

Column	Datatype	Null Option	Description
DataTypeID	int	IDENTITY	Reserved
Null	varchar(64)	NO

Domains

Column	Datatype	Null Option	Description
DomainID	int	IDENTITY	IDENTITY / AutoNumber
Domain	varchar(40)	NO	The display name of the domain.
MailDomain			The fully qualified internet domain.
GroupID	int		Billing group this domain applies to.  Set NULL to apply globally.
Null	varchar(45)	NO	A Null Option of the domain.
SortOrder	smallint

ExportColumns

Column	Datatype	Null Option	Description
ExportColumnID	int	IDENTITY	IDENTITY / AutoNumber
ExportID		NO	Export format this import column is associated with.
SortOrder	smallint		Exported columns are presented in order starting with the lowest sort order.
FieldName	varchar(32)		Name of field to export
Null	varchar(64)		Informational field Null Option.
Quoted	smallint	NO	Weather to quote the contents of this field.  The quote character is determined by the value of  Exports.QuoteString.
MinSize			Min size of the data in this field.  If the export data is less than this padding characters are added to make up the difference.
MaxSize			Max size of data in this field.  If the data is longer than this field it’s truncated to match MaxSize.
Align			When padding data this determins which direction to pad from: 0 Left 1 Right
Format			How to format data from this field.  Note that all FieldNames must exist in the export stored procedure except if the Format is configured to be constant. 0 = Constant 1 = String 2 = Number 3 = Currency 4 = Currancy * 100 no ’.’ 7 = AVS Numbers
Padding	char(1)		Padding character
ExportValue	varchar(100)		After the initial row data is set based on the formatting parameters their values can be changed by setting ExportValue and using $FieldName to substite for values in other fields.
Include	smallint		Weather to include this column in the export. 1 = Yes 0 = No

Exports

Column	Datatype	Null Option	Description
ExportID	int	NO	Assigned ExportID
Null	varchar(64)		Export Null Option.
Separator	varchar(6)		When exporting formats using delimiters such as ’tab’ or ’,’ this sets the delimiter.  Tabs can be specified by setting seperator to ’\t’
QuoteString			If an exported or imported column is quoted.  This sets the quote character.
SQLString	varchar(255)		The name of the query to retreive a result set from the database to export.
HeaderRow	smallint		If the export format supports sending a column header.  This field determins weather or not one should be sent. 0 = No 1 = Yes
ApproveString	varchar(100)		For CC/ACH processing.  The string to match in the status field to determine the transaction is approved.  Multiple approve strings can be specified by seperating them with a space.
ApproveMatch	smallint		ApproveString match type: 0 = Complete 1 = From Start 2 = Substring 3 = From End 4 = No Complete Match 7 = Equal
DeclineString	varchar(100)	NO	For CC/ACH processing.  The string to match in the status field to determine the transaction is declined.  Multiple decline strings can be specified by seperating them with a space. If a decline string is specified and a transaction does not match ApproveString or DeclineString it’s concidered an Error.  If DeclineString is not specified anything not matching ApproveString is concidered declined.
DeclineMatch	smallint		DeclineString match type: 0 = Complete 1 = From Start 2 = Substring 3 = From End 4 = No Complete Match 7 = Equal
ExportType		NO	Type of export: 1 = Credit Card No AVS 2 = Credit Card AVS 3 = External System 4 = EFT 5 = Credit Card Realtime
Filter	varchar(32)		Some export formats can’t be configured via the ExportColumns table.  Filter is the name of an internal filter to pass the exported data through.  Currently the only filter avaliable is ’urlencoded’ which exports a URL encoded field/value pair.
Lines	smallint		Number of lines per record in the inport format.
FormatDefault	int		If the application can’t request an export type this field contains the default type to use.  Possible values are: 1 = File 2 = HTTP 3 = XML 4 = Table 5 = TCP

ExternalConfigs

Column	Datatype	Null Option	Description
ExternalConfigID	int	IDENTITY	IDENTITY / AutoNumber
Application	varchar(64)	NO	Name of application configuration is associated with
Service			The name of the service within an application this configuration applies to.
Attribute			The ’key’ value of the attribute associated with the application/service.
Null	varchar(255)		Null Option of this setting.
Type	int		Data type of attribute.  Datatypes are defined by the application storing it’s data here.  It’s tradition to stick with the attribute Types used by Radius where possible.
Value			Numeric value of attribute
Data	varchar(255)	NO	String value of attribute

ExternalSystems

Column	Datatype	Null Option	Description
ExternalSystemID	int	IDENTITY	IDENTITY / AutoNumber
Name	varchar(25)	NO	Name of external system
ExternalSystemType	int		External system type.  Currently known external system types are: 0 = External (LDAP/EmerAuth..etc) -1 = MailSite Datacenter -2 = Imail 1 = * File 2 = * HTTP 3 = * XML 1000 = IIS Virtual Web
ExportID			Export configuration assoicated with this external system.  (Any export with an ExportType of 3 from the Exports table)
Null	varchar(50)		Null
MailHost	varchar(40)		Ususally used with mail systems to specify the DNS name of the mail server responsible for accounts under this external system.
Login			The login field is used with external systems that require credentials.  For example http posts to a password protected web page.
Password	varchar(16)		Password associated with the login field.
LogFile	varchar(100)		Depending on the external system this is the location of the export file to write or the url to connect.
LastSyncDate	datetime		Start time of last successful sync.
FileMode	smallint	NO	Currently only used with external system types of File or XML.  When writing to a log file which already exists.  File mode determines weather to overwrite or append that file. 1 = Append 2 = Overwrite
FormID	varchar(40)		This was used with the Post.Office email sync.
Accounts			???

ExternalTrans

Column	Datatype	Null Option	Description
ExternalTransID	int	IDENTITY
CustomerID		NO
InvoiceID
TransType		NO
ReqDate	datetime
ProcDate
Amount	numeric(18,4)	NO
PaymentID	int
ApproveCode	varchar(12)
Response	varchar(50)
LastModifyDate	datetime	NO
LastModifyUser	varchar(32)
AccountNumber
AccountExpire	datetime
AuthName	varchar(50)
AuthAddress		NO
AuthZip	varchar(12)
AuthSecurityCode	varchar(10)
BankName	varchar(32)
BankNumber	varchar(16)
BankTransType	varchar(32)
RebatchTries	smallint
LastRebatchTry	datetime	NO

FileTransfers

Column	Datatype	Null Option	Description
TransferDate	datetime	NO
AccountID	int
FileName	varchar(100)
NumBytes	int
Direction	smallint
Processed

FilterTypes

Column	Datatype	Null Option	Description
FilterTypeID	int	IDENTITY	Reserved
FilterType	varchar(30)	NO
Null	varchar(64)
Checked	int
SortOrder	smallint

FlowFilterGroups

Column	Datatype	Null Option	Description
FlowFilterGroupID	int	IDENTITY	IDENTITY / AutoNumber
FlowFilterGroup	varchar(32)	NO	Filter group name
Null	varchar(64)		Null Option of this filter group
SortOrder	int
AccountID			Service responsible for this filter
SummaryMode			Reserved
CacheExpire

FlowFilters

Column	Datatype	Null Option	Description
FlowFilterID	int	IDENTITY	IDENTITY / AutoNumber
FlowFilterGroupID		NO	Filter group this flow filter is a part of
FilterType			Filter Type.  0=Ignore, 1=Include
SortOrder
SrcIP	varchar(16)		Source IP Address
SrcMask	varchar(32)		Source netmask
SrcPort	int		Source IP Port
DestIP	varchar(16)		Destination IP Address
DestMask	varchar(32)		Destination netmask
DestPort	int		Destination IP Port
Prot			IP protocol type
CacheExpire			Reserved
SummaryMode

Flows

Column	Datatype	Null Option	Description
FlowDate	datetime	NO	Date flow record was recorded
SrcIP	int		Flow record source address.
DestIP			Flow record destination address.
SrcPort			Flow record source ip port.
DestPort			Flow record destination ip port.
Prot			Flow record IP protocol used
Packets			Total number of IP packets counted in this flow
Octets			Total number of bytes counted in this flow
AccountID			Service this flow record is associated with.
FlowFilterID			Flow filter triggering this flow to be recorded.

Forwards

Column	Datatype	Null Option	Description
ForwardID	int	IDENTITY
Forward	varchar(64)	NO
AccountID	int
CreateDate	datetime
LastModifyDate
LastModifyUser	varchar(32)

GLCodes

Column	Datatype	Null Option	Description
GLCodeID	int	IDENTITY	Reserved
GLCode	varchar(32)	NO
Null
DetailFlag	smallint

Groups

Column	Datatype	Null Option	Description
GroupID	int	IDENTITY	IDENTITY / AutoNumber
DomainID		NO	The DomainID of the domain the group is associated it.
GroupName	varchar(25)		The display name of the group.
Null	varchar(45)		The Null Option of the group.
InvoiceReport			The Invoice report for this group to override the global report.
InvoiceTextReport			The Invoice text report for this group to override the global report.
InvoiceHTMLReport		NO	The Invoice html report for this group to override the global report.
StatementReport			The statement report for this group to override the global report.
StatementTextReport			The statement text report for this group to override the global report.
StatementHTMLReport			The statement html report for this group to override the global report.
UniqueLogins	smallint		Whether the group will have unique logins, excluding other groups.
GlobalOptions			Whether the group allows global objects or only those specific to it.
SortOrder			The sort order when displayed in a list (lowest displayed first).
FTPDir	varchar(64)		Default FTP directory for the group (overrides the global directory).
BillingOption	smallint		Reserved
StatementRunDay
SendCredits
PartialPmtExtOption
PartialPmtExtDays
OverLimit	numeric(18,4)
BillName	varchar(50)	NO	Billing Group company details - Name
BillAddress1			Billing Group company details - Address1
BillAddress2			Billing Group company details - Address2
BillCity		NO	Billing Group company details - City
BillState			Billing Group company details - State
BillProvince			Billing Group company details - Province
BillZip	varchar(32)		Billing Group company details - Zip
BillCountry	varchar(50)		Billing Group company details - Country
EmailAccounts	varchar(64)	NO	Default from address of email messages sent for accounts in this group
EmailSubject			Default subject of email messages sent for accounts in this group

Incidents

Column	Datatype	Null Option	Description
IncidentID	int	IDENTITY
AccountID		NO
CreateDate	datetime
IncidentStateID	smallint
Operator	varchar(15)
Null	text
Hide	int	NO
LastModifyDate	datetime
LastModifyUser	varchar(32)
IncidentTypeID	int
LMD_TS	timestamp

IncidentStates

Column	Datatype	Null Option	Description
IncidentStateID	smallint	NO
IncidentState	varchar(20)
Null	varchar(64)
SortOrder	smallint

IncidentTypes

Column	Datatype	Null Option	Description
IncidentTypeID	int	IDENTITY
IncidentType	varchar(20)	NO
Null	varchar(64)
Email	varchar(40)
SortOrder	smallint	NO

InvoiceItems

Column	Datatype	Null Option	Description
ItemID	int	IDENTITY	IDENTITY / AutoNumber
InvoiceID		NO	Invoice this invoice item is for.
ItemType			Invoice Item type: 0 = ITEM_POS 1 = ITEM_CREDIT 2 = ITEM_CHARGE 3 = ITEM_RECURR 254 = ITEM_TAX 255 = ITEM_TAX2 999 = ITEM_COMMENT
Login	varchar(40)		The login (service) this item is for.
Null	varchar(50)		The Null Option of the service this item is for.
StartDate	datetime	NO
EndDate
AccountID	int		AccountID the line item is for.  This allows for updating the service when this line item is paid.
Amount	numeric(18,4)	NO	The item cost of the service.
Quantity	int		The quantity (number of months, etc) of the item.
TaxID			If this item is taxable, this is the tax type for it (from the Taxes table).
PaidAmount	numeric(18,4)	NO	How much of the line item has been paid.  This allows for true balance forward operation without making a payment to a specific invoice.
AccountTypeID	int		AccountTypeID the line item is for.  This is added for simplified Account Type revenue summary.
ChargeTypeID			Added charge type tracking to support GL mapping
GLState	smallint		Indicates state for the data export to external accounting package

InvoiceNotes

Column	Datatype	Null Option	Description
InvoiceID	int	NO
NoteID

Invoices

Column	Datatype	Null Option	Description
InvoiceID	int	IDENTITY	IDENTITY / AutoNumber
CustomerID		NO	The MBR this invoice is for (from the MasterAccounts table).
InvoiceTypeID			Invoice type (See InvoiceTypes table)
Amount	numeric(18,4)		Total invoice amount
CreateDate	datetime		Date this invoice was created
LastModifyDate			The date the record was last modified by Emerald.
LastModifyUser	varchar(32)		The user to last modify this record by Emerald.
SentDate	datetime		When the Invoice was sent.  If NULL the invoice has not yet been marked as sent.
Tax	numeric(18,4)	NO	The total primary tax on the invoice (the Amount includes this field).
StatementID	int		Associated statement from the Statements table.  NULL StatementID indiciates the invoice has not yet been applied to a statement.
Comments	varchar(100)		The primary Null Option of the invoice. Typically this is the same as the first invoice line item, and is not used specifically by Emerald.

InvoiceTypes

Column	Datatype	Null Option	Description
InvoiceTypeID	int	NO
InvoiceType	varchar(15)
GroupID	int

IPClasses

Column	Datatype	Null Option	Description
IPClassID	int	NO	Reserved
IPClass	varchar(32)
Null	varchar(64)
LastModifyDate	datetime	NO
LastModifyUser	varchar(32)

IPs

Column	Datatype	Null Option	Description
IPID	int	NO	Reserved
IP	varchar(16)
IPClassID	int
LastModifyDate	datetime
LastModifyUser	varchar(32)

Languages

Column	Datatype	Null Option	Description
LanguageID	int	NO
Null	varchar(30)
Active	smallint
CurrentInUse
BaseURL	varchar(128)
SortOrder	smallint

LateNoticeNotes

Column	Datatype	Null Option	Description
LateNoticeID	int	NO
NoteID

LateNotices

Column	Datatype	Null Option	Description
LateNoticeID	int	IDENTITY
CustomerID		NO
LateNoticeTypeID
NoticeDate	datetime
SentDate
Balance	numeric(18,4)

LateNoticeTypes

Column	Datatype	Null Option	Description
LateNoticeTypeID	int	IDENTITY	IDENTITY / AutoNumber
Null	varchar(50)	NO	Text describing late notice
GroupID	int		The GroupID of the associated Group (Or NULL for global)
MinAmount	numeric(18,4)	NO	Minimum balance after which this notice can be sent.
MinDays	int		Minimum amount of days without payment before this type of notice can be sent.
Amount	numeric(18,4)		Late fee cost
AmountType	smallint		Cost type:  0=Percentage, 1=Static
TaxID	int		Tax associated with late fees
Active	smallint	NO	Weather this notice type is enabled.  1=Yes, 0=No.
SortOrder

LDAPServers

Column	Datatype	Null Option	Description
LDAPServer	varchar(50)	NO	LDAP Server name defined in Emerdap.
LastModifyDate	datetime		Last time this column was updated
LastSyncRead			Last time data was read for a partial sync
LastSyncWrite			Last time data was successfully synced via a successful partial sync.
Calls	int		Rougly the number of times a sync has run.

Licenses

Column	Datatype	Null Option	Description
LicenseID	varchar(50)	NO
Company	varchar(80)

LogMsgs

Column	Datatype	Null Option	Description
LogMsgID	int	NO
Null	varchar(50)
Severity	int

Logs

Column	Datatype	Null Option	Description
LogMsgID	int	NO
LogDate	smalldatetime
LogMsg	varchar(63)
Operator	varchar(32)
CustomerID	int
AccountID

MailItems

Column	Datatype	Null Option	Description
MailItem	varchar(15)	NO
GroupID	int

MailLogs

Column	Datatype	Null Option	Description
LogMsgID	int	NO
LogDate	datetime
CustomerID	int
LogNull Option	varchar(100)

MapAttributes

Column	Datatype	Null Option	Description
MapAttribute	varchar(32)	NO	Identifier of external attribute
MapType	int		Groups sets of attributes by their type.  (ex LDAP,Tacacs, External auth)
RadAttributeID			The Radius attribute mapped to
RadVendorID			Radius Vendor ID of mapped radius attribute
RadVendorType			If radius attribute is vendor-specific (RadAttributeID = 26), then this denotes a vendor type, otherwise the value should be NULL or 0.

MapValues

Column	Datatype	Null Option	Description
MapAttribute	varchar(32)	NO	Identifier of external attribute
MapType	int		Identifier of type of external attribute
Value	varchar(32)		Attribute value (number)
RadValue	int		Radius attribute value (number)
Null	varchar(255)		Null Option of external attribute value

MasterAccountData

Column	Datatype	Null Option	Description
CustomerID	int	NO	Customer data is associated with
DataTypeID			Type of data
Data	varchar(64)		Data

MasterAccountDataTypes

Column	Datatype	Null Option	Description
DataTypeID	int	IDENTITY	IDENTITY / AutoNumber
Null	varchar(64)	NO	Data type Null Option
GroupID	int		The GroupID of the associated Group (Or NULL for global)

MasterAccounts

Column	Datatype	Null Option	Description
CustomerID	int	IDENTITY	IDENTITY / AutoNumber
LastStatementID			StatementID of the latest statement for this MBR.
FirstName	varchar(25)	NO	The users first name
MiddleName			The users middle name
LastName			The users last name
Company	varchar(60)		The company the user is associated to.
Address1	varchar(50)		The first address line.
Address2			The second address line.
City	varchar(40)		The users City
State			The users State
Zip	varchar(15)		The users zip code
Province	varchar(32)	NO	The users province
Country	varchar(40)		The users country
PhoneHome	varchar(20)		The users home phone number
PhoneWork			The users work phone number
Gender	varchar(1)	NO	The users gender
Salutation	varchar(4)		The users Salutation
PhoneFax	varchar(20)		The users fax number
RegionID	int	NO	The primary region the user is located in.
ReferredBy	varchar(25)		Who the user was referred by.
SalesPersonID	int		The sale’s person assigned to the user.
CreateDate	datetime	NO	The creation date of the user’s account.
StartDate			The date the user’s account will start.
Active	smallint		If this field is 0, the account will not authenticate.
PayPeriodID	int		The default pay period.
PayMethodID			The method the user will pay their bill.
LastReceived	datetime		The last date the user made a payment on.
Comments	text		Misc comments about this users account
Status	smallint	NO	Change status?
GroupID	int		The GroupID of the associated Group
OverDue	smallint		An extension, in days, to the Expiration date.
SendMethodID		NO	Bill send method from the SendMethods table
OverLimit	numeric(18,4)		If the account Balance is less than this field, the account will NOT be authenticated.
Taxable	smallint	NO	Do taxes apply to taxable charges made to this MBR?
BillingCycleID	int		MBR Billing Cycle from the BillingCycles table.
BillDay	smallint		Anniversary day of month used to determine when to do invoicing for this MBR
CancelDate	datetime		Date the account was cancelled by the service provider.
CancelReasonID	int		Reason why the account was cancelled by the service provider.
Balance	numeric(18,4)	NO	Current balance as of last billing run.
Over30Count	smallint		Number times in over 30 category
Over60Count			Number times in over 60 category
Over90Count			Number times in over 90 category
Over120Count			Number times in over 120 category
AgedDate	datetime		Last date aging calculated
PendingCredit	numeric(18,4)	NO	Credits since last statement/billing run
PendingDebit			Debits since last statement/billing run
NoticeDate	datetime		Last overdue notice date
PayInfo	varchar(50)	NO	A comment field about the user’s billing habits or problems.
Operator	varchar(32)		The Operator who created this account.
LastModifyDate	datetime		The date the record was last modified by Emerald.
LastModifyUser	varchar(32)		The user to last modify this record by Emerald.
LMD_TS	timestamp

MetaColumns

Column	Datatype	Null Option	Description
MetaColumnID	int	IDENTITY	IDENTITY / AutoNumber
MetaTableID			Table this column is a part of.
ColumnName	varchar(32)	NO	Database column label
Null	varchar(255)		Column Null Option
DepTable	varchar(32)		The name of a table if any that defines possible values of this column.
DepColumn			Name of the column in DepTable which stores possible values for this column.
DepGroup	int		Determins weather group security should be taken into account when checking dependancies.
MetaRuleID			Special validation rule the value of this column must pass before being saved.   METARULE_NONE=0 METARULE_CREDITCARD=1 METARULE_PASSWORD=2 METARULE_UNIQUELOGIN=3 METARULE_IPADDRESS=4 METARULE_BIT=5 METARULE_EMAIL=6 METARULE_DATETIME=7 METARULE_DNSADDRESS=8 METARULE_PASSWORD_ENCRYPT=9 METARULE_NUMERIC=10
Required	smallint		Weather the user is required to enter a value into this column before the record will be saved.
Nullable			Weather null values are permitted for this record. If the value of the data being entered is ’’ and nulls are permitted then the data is entered as NULL.  If nulls are not permitted the data is entered as ’’.  To require non-blank input set MinSize.
Quoted			Weather the data should be quoted or not.  Generally numeric data isn’t quoted and everything else is.
MinSize	int		Minimum allowed length in bytes of values allowed in column.
MaxSize			Maximum allowed length in bytes of values allowed in column.
IsKey	smallint		Is this column part of the tables primary key?  (true=1, false=0)  If a table has an auto-number column that column’s IsKey should be true and all others set false.
ColumnType	int		Not used?

MetaRules

Column	Datatype	Null Option	Description
MetaRuleID	int	NO
Null	varchar(255)

MetaTableGroups

Column	Datatype	Null Option	Description
MetaTableGroupID	int	NO
GroupName	varchar(32)
Null	varchar(255)

MetaTables

Column	Datatype	Null Option	Description
MetaTableID	int	IDENTITY
TableName	varchar(32)	NO
Null	varchar(255)
GroupMetaTableID	int
MetaTableGroupID
SortOrder
Edition
Feature

ModemSpeeds

Column	Datatype	Null Option	Description
ModemSpeedID	int	IDENTITY
ModemSpeed	varchar(5)	NO
SortOrder	smallint

NetSoftwares

Column	Datatype	Null Option	Description
NetSoftwareID	int	IDENTITY
NetSoftware	varchar(15)	NO
Map	smallint
SortOrder

Notes

Column	Datatype	Null Option	Description
NoteID	int	IDENTITY
NoteType	smallint	NO
Active
StartDate	datetime
EndDate
GroupID	int
BalanceCriteria	varchar(15)	NO
AcctTypeCriteria
PayDateCriteria	smallint
Priority
Note	varchar(255)	NO
RegionID	int

ObjectGroups

Column	Datatype	Null Option	Description
ObjectGroupID	int	NO	ID of  Object Group.
ReportID			???
ObjectGroup	varchar(32)	NO	Name of Object Group

Objects

Column	Datatype	Null Option	Description
ObjectID	int	IDENTITY	IDENTITY / AutoNumber
ObjectGroupID		NO	Object group this object is part of.
ObjectTypeID			Type of object: 1 = file 2 = table 3 = column
Value			Integer value describing object
Data	varchar(80)	NO	Textual value describing object
Null	varchar(32)		Object Null Option

OnlineSignups

Column	Datatype	Null Option	Description
OnlineSignupID	int	IDENTITY
FirstName	varchar(30)	NO
LastName
Address1	varchar(100)
Address2
City	varchar(50)
State
Province
Zip	varchar(32)
Country	varchar(50)
ReferredBy	varchar(25)
Region	varchar(15)
AccountTypeID	int
Login	varchar(40)
Password	varchar(32)
MaidenName	varchar(25)
NetSoftware	varchar(15)
SystemType
ModemSpeed
CallerID	varchar(16)
FramedAddress
CallDate	datetime
SignUpDate
Status	varchar(32)
PaymentOption	varchar(15)
CardNumber	varchar(32)
CardExpiration	varchar(20)
SignUpComment	varchar(255)

OperatorCookies

Column	Datatype	Null Option	Description
Operator	varchar(16)	NO
Cookie	varchar(64)
LastUsed	datetime

OperatorGroupAccess

Column	Datatype	Null Option	Description
OperatorGroupID	int	NO	The Operator Group.
GroupID			The Group. This can be 1 to mean all Groups.
ObjectGroupID			The Object Group. This can be 1 to mean all Object Groups.
AccessType	smallint		Bitmask of Access rights for the Operator Group to an Object Group. 1 = Read 2 = Add 4 = Modify 8 = Delete

OperatorGroups

Column	Datatype	Null Option	Description
OperatorGroupID	int	IDENTITY	IDENTITY / AutoNumber
OperatorGroup	varchar(32)	NO	The name of the Operator Group
Null	varchar(64)		The Null Option of the Operator Group

Operators

Column	Datatype	Null Option	Description
OperatorID	int	IDENTITY	IDENTITY / AutoNumber
PrintTypeID			Default report print type from the PrintTypes table.
OperatorGroupID		NO	The Operator Group the Operator belongs to.
Operator	varchar(15)		The name of the Operator.
Password	varchar(32)		The Operator’s password for Web access.
Email			The Operator’s E-Mail address.
WebObjectAccess	int		The Operator’s Web access.
LanguageID			Language option pre-configured for Operator
Active	smallint	NO	Controls weather the operator can login to Emerald. 1 = Yes 0 = No

Payments

Column	Datatype	Null Option	Description
PaymentID	int	IDENTITY
CustomerID		NO
Amount	numeric(18,4)
CreateDate	datetime
PaymentTypeID	int
CheckNumber	varchar(32)
PayInfo	varchar(15)
StatementID	int
LastModifyDate	datetime	NO
LastModifyUser	varchar(32)
GLState	smallint

PaymentTypes

Column	Datatype	Null Option	Description
PaymentTypeID	int	IDENTITY
PaymentType	varchar(16)	NO
GroupID	int
SortOrder	smallint	NO
GLCodeID	int
Amount	numeric(18,4)	NO
AmountType	smallint

PayMethods

Column	Datatype	Null Option	Description
PayMethodID	int	IDENTITY	IDENTITY / AutoNumber
PayMethod	varchar(15)	NO	Display name of the Payment Method.
GroupID	int		The GroupID of the associated Group (Or NULL for global)
SortOrder	smallint	NO
Amount	numeric(18,4)		Reserved
AmountType	smallint

PayPeriods

Column	Datatype	Null Option	Description
PayPeriodID	int	IDENTITY	IDENTITY / AutoNumber
AccountTypeID			Which Account Type this Pay Period applies.  (Set NULL for all)
PayPeriod	varchar(12)	NO	The name of the pay period.
Period	smallint		The number of months in the pay period.
Amount	numeric(18,4)		Discount amount
AmountType	smallint		Discount amount type 0=percentage, 1=static
SetupCharge			Weather to charge a setup fee.  1=yes, 0=no
GroupID	int		Billing group this Pay Period applies.  Set NULL for all groups.
SortOrder	smallint	NO

PrintTypes

Column	Datatype	Null Option	Description
PrintTypeID	int	IDENTITY	IDENTITY / AutoNumber
PrintType	varchar(32)	NO	Print format. ex ’PDF’
Data	varchar(128)		Mime type associated with print format.
CRData			Internal command to set print format.

RadATConfigs

Column	Datatype	Null Option	Description
RadATConfigID	int	IDENTITY	RadATConfig identifier (IDENTITY)
AccountTypeID		NO	Associated Account type (related AccountTypeID from AccountType table).
RadAttributeID			Associated Radius attribute (related RadAttributeID from RadAttributes table).
RadVendorID			If this attribute is a vendor specific attribute (radattribute = 26) then this denotes a vendor id.  Otherwise the value should be NULL or 0
RadVendorType			If this attribute is a vendor specific attribute (radattribute = 26) then this denotes a vendor type.  Otherwise the value should be NULL or 0
Data	varchar(100)		Used for string, IP address, or date attribute types
Value	int		Used for integer attribute types.
RadCheck	smallint	NO	attribute type 0 denotes a normal reply attribute non-zero denotes this is a check attribute
Tag			For attributes supporting it tag values allow grouping multiple attributes within a single radius response.

RadAttributes

Column	Datatype	Null Option	Description
RadAttributeID	int	NO	Radius Attribute identifier (IDENTITY)
RadVendorID			If this attribute is a vendor specific attribute (radattribute = 26) then this denotes a vendor id.  Otherwise the value should be NULL or 0.
RadVendorType			If this attribute is a vendor specific attribute (radattribute = 26) then this denotes a vendor type.  Otherwise the value should be NULL or 0.
Name	varchar(32)		Name of the radius attribute
RadAttributeType	int		RADIUS attribute type 0  string 1  32-bit integer 2  IP address 3  Date 4  Ascend Binary 10 Tag String 11 Tag 32-bit integer 12 Tag IP address 13 Tag Date
ReplyType	smallint		Reply type 0 Accounting Only 1 Reply only 2 Check only 3 Check & Reply
AliasAttributeID	int		Radius attribute (from RadAttributes table) of similar Radius attribute for logging of accounting data.
AliasVendorID			VendorID of AliasAttributeID

RadConfigs

Column	Datatype	Null Option	Description
RadConfigID	int	IDENTITY	RadConfig identifier (IDENTITY)
AccountID		NO	Associated subaccount (related AccountID from SubAccounts table).
RadAttributeID			Associated Radius attribute (RadiusAttributeID from RadAtributes table).
RadVendorID			If this attribute is a vendor specific attribute (radattribute = 26) then this denotes a vendor id.  Otherwise the value should be NULL or 0.
RadVendorType			If this attribute is a vendor specific attribute (radattribute = 26) then this denotes a vendor type.  Otherwise the value should be NULL or 0.
Data	varchar(100)		Used for string, ip address or date attribute types
Value	int		Used for integer attribute types
RadCheck	smallint	NO	attribute type 0 denotes a normal reply attribute non-zero denotes this is a check attribute
Tag			For attributes supporting it tag values allow grouping multiple attributes within a single radius response.

RadIPAccountTypes

Column	Datatype	Null Option	Description
RadIPAccountTypeID	int	IDENTITY	RadIPAccountType identifier (IDENTITY)
ServerGroupID		NO	Associated Server Group (related ServerGroupID from ServerGroup table).
AccountTypeID			Associated with Account type (related AccountTypeID from AccountType table).
RadIPGroupID			Associated RadIP Group (related RadIPGroup from RadIPGroup table).
Priority		NO

RadIPAddresses

Column	Datatype	Null Option	Description
IPAddress	varchar(16)	NO	Associated IPAddress
RadIPGroupID	int		Associated Radius IP Group (related RadIPGroupID from RadIPGroup table).
NASIdentifier	varchar(32)		NAS Identifier associated with server
NASPort	VARCHAR(10)		NAS Port associated with server/port.
State	INT	NO	Address checkout state 0 Avaliable 1 Auth In Use 2 Acct In Use
LastUsed	datetime		Date this IP last checked out

RadIPGroups

Column	Datatype	Null Option	Description
RadIPGroupID	int	IDENTITY	RadIPGroup Identifier (IDENTITY)
RadIPGroup	varchar(32)	NO	Name of RadIPGroup

RadLogMsgs

Column	Datatype	Null Option	Description
RadLogMsgID	int	NO	Log Message Identifier (see ODBC logging)
Null	varchar(50)		Null Option of Log Message
Severity	int		Error severity of associated Log Message

RadLogs

Column	Datatype	Null Option	Description
RadLogMsgID	int	NO	Log Message Identifier (type of log message)
LogDate	smalldatetime		The log message date
Username	varchar(40)		The associated username (if exists) of log entry
Data	varchar(50)		Additional data, dependent on the Log Message ID.  Note:  The ServerPorts table contains information about each port avaialble for a NAS.  This is required for concurrency control and for monitoring who is online.
NASIdentifier	varchar(16)		The associated NAS Identifier of log entry
NASPort	varchar(10)		The associated NAS Port of log entry
CallerID	varchar(15)		The associated Caller ID of log entry

RadProxyAttributeGroups

Column	Datatype	Null Option	Description
RadProxyAttributeGroupID	int	IDENTITY	IDENTITY / AutoNumber
RadRoamServerID		NO	Server to proxy matching requests
Priority			It is possible that more than one attribute group can match a single request.  Since a request can be proxied to only one server, this determines how salient a particular group is over another.  Please note that the lowest priority group takes precendence.
Null	varchar(255)		Null Option of the proxy group

RadProxyAttributes

Column	Datatype	Null Option	Description
RadProxyAttributeGroupID	int	NO	Associates a group of attributes to a proxy server (foreign key to RadProxyAttributeGroups table)
SearchType	tinyint		type of rules used in searching for matching attribute/values 1 string, 2 substring, 3 equal, 4 less than, 5 greater than
String	varchar(253)		Value to search on
RadAttributeID	int		Related Radius attribute (RadAttributeID from RadAttribute table)
RadVendorType			If attribute is vendor-specific (RadAttributeID = 26), then this denotes a vendor type, otherwise the value should be NULL or 0.
RadVendorID			Related Radius attribute vendor
RadProxyAttributeID		IDENTITY	Identifer of proxied attribute (IDENTITY)

RadRejects

Column	Datatype	Null Option	Description
RadRejectID	int	IDENTITY	RadReject identifier (IDENTITY)
RadAttributeID		NO	Associated Radius attribute (related RadAttributeID from RadAttribute table).
RadVendorID			If this attribute is a vendor specific attribute (radattribute = 26) then this denotes a vendor id.  Otherwise the value should be NULL or 0
RadVendorType			If this attribute is a vendor specific attribute (radattribute = 26) then this denotes a vendor type.  Otherwise the value should be NULL or 0
Data	varchar(100)		Used for string, IP address or date attribute types.
Value	int		Used for integer attribute types.

RadRoamDomains

Column	Datatype	Null Option	Description
RadRoamDomainID	int	IDENTITY	Rad Roam Domain identifier (IDENTITY)
RadRoamServerID		NO	Which Rad Roam Server this entry is associated with (RadRoamServerID from RadRoamServer table).
Domain	varchar(32)		Roam domain in the login (user@domain).
Priority	int		The Roam Server’s priority in this domain
CostPerMinute			Reserved
AccountTypeID			If this option is NULL, then RadiusNT/X will ignore the attributes returned in the proxy reply and return the set of attributes associated to the account type.

RadRoamPorts

Column	Datatype	Null Option	Description
NASIdentifier	varchar(16)	NO	Reserved
Port	varchar(10)
AcctSessionID	varchar(16)
Username	varchar(40)
AcctStatusType	smallint
CallDate	datetime	NO
FramedAddress	varchar(16)
CallerID	varchar(15)
ConnectInfo	varchar(32)

RadRoamServers

Column	Datatype	Null Option	Description
RadRoamServerID	int	IDENTITY
IPAddress	varchar(16)	NO
Server	varchar(32)
Secret	varchar(16)
Timeout	int
Retries
TreatAsLocal	smallint
StripDomain
AuthPort	int
AcctPort
RateTarget
RateMax

RadTriggers

Column	Datatype	Null Option	Description
RadTriggerID	int	IDENTITY	IDENTITY / AutoNumber
AccountID		NO	Related AccountID from SubAccounts.
TriggerType			Type of trigger (currently not used)
FileName	varchar(64)		Executable program or file to run
Parameters			Parameter for the program or file
Directory	varchar(128)		Working directory for the program or file

RadValues

Column	Datatype	Null Option	Description
RadAttributeID	int	NO	Associated radius attribute (RadAttributeID from RadAttributes table).
RadVendorID			If this attribute is a vendor specific attribute (radattribute = 26) then this denotes a vendor id.  Otherwise the value should be NULL or 0
RadVendorType			If this attribute is a vendor specific attribute (radattribute = 26) then this denotes a vendor type.  Otherwise the value should be NULL or 0
Value			Attribute value number
Name	varchar(25)		Attribute value name

RadVendors

Column	Datatype	Null Option	Description
RadVendorID	int	NO	RadVendor identifier (industry/vendor specified)
Name	varchar(32)		Name of vendor

RateHistory

Column	Datatype	Null Option	Description
AccountID	int	NO	The service this is for.
StartDate	datetime		The Start date of the summary
HistoryType	smallint		?? 0 = normal, 1 = primary, 2=toll, 3=free
RateID	int		The RateID of the associated Rate
TotalCount		NO	Number of records in the summary
TotalTime			Total amount of time for all records in the summary
TotalData			Total amount of data from all records in the summary
TotalRate	numeric(18,4)		Reserved
ChargeID	int		If a charge was created based on the usage, this will be the charge id from the charges table.

Rates

Column	Datatype	Null Option	Description
RateID	int	IDENTITY
Null	varchar(30)	NO
MinCharge	numeric(18,4)
MaxCharge
DefaultType	int	NO
DefaultInterval
DefaultCharge	numeric(18,4)
TaxID	int
PrimaryStart
PrimaryStop
PrimaryType
PrimaryInterval
PrimaryCharge	numeric(18,4)

RateSchedules

Column	Datatype	Null Option	Description
RateScheduleID	int	IDENTITY
RateID		NO
HistoryType	smallint
ScheduleType
Offset	int
Interval
Charge	numeric(18,4)
SortOrder	int

RateTables

Column	Datatype	Null Option	Description
RateTableID	int	IDENTITY
RateTable	varchar(32)	NO
Null	varchar(50)
TableName	varchar(64)
AccountIDColumn
DateColumn
DataColumn
TimeColumn
FilterString	varchar(200)
PreProcess	varchar(64)	NO
LastProcDate	datetime

RatingOptions

Column	Datatype	Null Option	Description
RatingOptionID	int	IDENTITY	This table is to manage the rating options available within Emerald.  For example, Emerald GBE, rate-rec, other integrated external rating engines.  One entry per rating engine available The table contains the rating engine identification, as well as the configuration of the communication between the two systems. It may be a possibility down the road to allow more than one processing configuration per Rating Engine -- processing would then be determined by the data record Account Type instead of just the associated Rating Engine.
RatingOption	varchar(40)	NO	Name of Rating Option, ie. Emerald, rate-rec
Null	varchar(120)
Vendor	varchar(40)		Vendor of rating engine.  For example, IEA Software, am-beo, etc.
Mode	int		Indicator of how the data is transferred between Emerald and the rating system: real-time batch polling/push - we push up polling/pull -- they pull up
AuditChargesFlag	smallint
MinRecordThreshold	int
MaxRecordThreshold
TimeOutThreshold
IntegrationInfo	varchar(256)	NO	Later, may possibly need info on each rating component added, such as: module name, location, CORBA server, etc....
Active	smallint

ReferredBys

Column	Datatype	Null Option	Description
ReferredBy	varchar(25)	NO	Name of referer.
SortOrder	smallint

Regions

Column	Datatype	Null Option	Description
RegionID	int	IDENTITY	IDENTITY / AutoNumber
Region	varchar(15)	NO	Display name of the region.
GroupID	int		The GroupID of the associated Group (Or NULL for global)
Taxable	smallint		The taxable option of an MBR is set to this value when you create an MBR in this region.
SortOrder		NO

Reports

Column	Datatype	Null Option	Description
ReportID	int	IDENTITY	IDENTITY / AutoNumber
Report	varchar(35)	NO	Null Option of report
FileName	varchar(15)		Report filename
OperatorGroupID	int		Operator group this report is avaliable to.  (Or NULL for global)
GroupID			The GroupID of the associated Group (Or NULL for global)

SalesPersons

Column	Datatype	Null Option	Description
SalesPersonID	int	IDENTITY	IDENTITY / AutoNumber
SalesPerson	varchar(25)	NO	Display name of the Sales Person
SortOrder	smallint
Active
CommissionType	int
CommissionValue

ScheduleIntervals

Column	Datatype	Null Option	Description
Interval	varchar(32)	NO
Seconds	int

ScheduleLogs

Column	Datatype	Null Option	Description
ScheduleID	int	NO
Status	tinyint
Null	varchar(255)
StartDate	datetime
EndDate

Schedules

Column	Datatype	Null Option	Description
ScheduleID	int	IDENTITY
Server	varchar(32)	NO
ScheduleServerID	int
TaskName	varchar(32)	NO
Parameter	varchar(255)
TargetSecs	int
MaxSecs
Status	smallint	NO
LastStatus	varchar(255)
LastStart	datetime
LastUpdate
Interval	int	NO
StartDate	datetime
Aligned	smallint	NO
Null	varchar(100)
LockServerID	int
Active	smallint	NO
MaxConcurrent	int
LastModifyDate	datetime
LastModifyUser	varchar(32)
Operator	varchar(15)

ScheduleServers

Column	Datatype	Null Option	Description
Server	varchar(32)	NO
ScheduleServerID	int	IDENTITY
IPAddress	varchar(32)	NO
Port	int
Null	varchar(100)

ScheduleTasks

Column	Datatype	Null Option	Description
TaskName	varchar(32)	NO
TaskType
Null	varchar(255)

SendMethods

Column	Datatype	Null Option	Description
SendMethodID	smallint	NO	IDENTITY / AutoNumber
Null	varchar(32)		Send method Null Option.
Amount	numeric(18,4)		Send method cost.
AmountType	smallint		The service type this send method can apply to.
SortOrder
TaxID	int		Associated tax for charging for this send method.

ServerAccess

Column	Datatype	Null Option	Description
ServerID	int	NO	What Server this entry is associated with (related ServerID from Servers table).
Port	varchar(10)		Which port that this record is associated with (related Port from ServerPorts table).
AccountTypeID	int	NO	What Account type this entry is associated with (related AccountTypeID from AccountType table).
StartTime			The start time allowed to login, in minuteas from midnight
StopTime			The stop time allowed to login, in minutes from midnight.
MaxSessionLength			The maximum session length allowed.

ServerGroups

Column	Datatype	Null Option	Description
ServerGroupID	int	IDENTITY	Server Group identifier (IDENTITY)
ServerGroup	varchar(32)	NO	Server Group name (Null Option).

ServerHistory

Column	Datatype	Null Option	Description
ServerID	int	NO
HistoryDate	datetime
Calls	int
Minutes
Bytes

ServerPorts

Column	Datatype	Null Option	Description
NASIdentifier	varchar(32)	NO
Port	varchar(10)
ServerID	int
IPAddress	varchar(16)
MaxSessionTime	int
CostPerMinute
Username	varchar(40)
AccountID	int
AcctSessionID	varchar(16)
AcctStatusType	smallint
CallDate	smalldatetime
FramedAddress	varchar(16)
CallerID	varchar(15)
ConnectInfo	varchar(32)
SNMPUser	varchar(64)

Servers

Column	Datatype	Null Option	Description
ServerID	int	IDENTITY
ServerGroupID		NO
Server	varchar(25)
ServerType	int
IPAddress	varchar(16)
Location	varchar(15)
Ports	smallint
Modems
PhoneNumber	varchar(12)
ContactName	varchar(25)
ContactPhone	varchar(12)
ContactPage
Comments	varchar(64)
DynamicStart	varchar(16)
DynamicEnd
Secret
Community
RadRoamServerID	int
CheckAccess

ServerTypes

Column	Datatype	Null Option	Description
ServerType	int	NO
Vendor	varchar(32)
Model
SNMPType	int
SNMPUser	varchar(64)	NO

StatementNotes

Column	Datatype	Null Option	Description
StatementID	int	NO
NoteID

Statements

Column	Datatype	Null Option	Description
StatementID	int	IDENTITY
CustomerID		NO
StatementDate	datetime
SentDate
PrevBal	numeric(18,4)	NO
NewCharges
NewCredits
EndBal
End0Bal
End30Bal
End60Bal
End90Bal
End120Bal

Strings

Column	Datatype	Null Option	Description
StringID	int	NO
LanguageID
Version
ApplicationID
Data	varchar(255)
URL	varchar(60)

SubAccountCredits

Column	Datatype	Null Option	Description
SubAccountCreditID	int	IDENTITY
AccountID		NO
RefAccountID
CreditType	smallint	NO
Amount	numeric(18,4)
AmountType	smallint
StartDate	datetime
EndDate

SubAccountData

Column	Datatype	Null Option	Description
AccountID	int	NO
DataTypeID
Data	varchar(64)

SubAccountDataTypes

Column	Datatype	Null Option	Description
DataTypeID	int	IDENTITY
Null	varchar(64)	NO
AccountTypeID	int
ExternalSystemType
Hidden		NO

SubAccounts

Column	Datatype	Null Option	Description
AccountID	int	IDENTITY
CustomerID		NO
Login	varchar(40)
Email
FirstName	varchar(25)
MiddleName		NO
LastName
PhoneHome	varchar(20)
PhoneWork
DiffCost	smallint	NO
AccountTypeID	int
PayPeriodID
Cost	numeric(18,4)
DiscountID	int	NO
Comments	text
Password	varchar(32)
MaidenName	varchar(25)
CreateDate	datetime	NO
StartDate
BilledThru
ExpireDate
Extension	int	NO
Active	smallint
SystemTypeID	int
ModemSpeedID
NetSoftwareID
SalesPersonID
Operator	varchar(32)	NO
LastModifyDate	datetime
LastModifyUser	varchar(32)
Preferred	smallint
Status
TimeLeft	int
HomeDir	varchar(100)
HomeDirLimit	int
SendBill	smallint	NO
RemoteAccess
LoginLimit
LastUsed	datetime
DomainID	int	NO
Gender	varchar(1)
Salutation	char(4)
LMD_TS	timestamp	NO

SysLogDefs

Column	Datatype	Null Option	Description
DefID	smallint	NO
Version
Data	varchar(255)

SysLogFilters

Column	Datatype	Null Option	Description
SysLogFilterID	int	IDENTITY
IPAddress	varchar(16)	NO
Severity	smallint
SubString	varchar(255)

SysLogs

Column	Datatype	Null Option	Description
DefID	smallint	NO
Version
LogDate	datetime
Severity	smallint
Facility
IPAddress	int

SystemTypes

Column	Datatype	Null Option	Description
SystemTypeID	int	IDENTITY
SystemType	varchar(15)	NO
SortOrder	smallint

Taxes

Column	Datatype	Null Option	Description
TaxID	int	IDENTITY	IDENTITY / AutoNumber
Null	varchar(32)	NO	Null Option of the tax
Null Option2			Null Option of tax2
Tax	numeric(18,4)		The first tax value for a single, two, or piggy back tax options.
Tax2			The second tax value for a two or piggy back tax options.
TaxType	smallint		The type of tax. 1=single, 2=dual, 3=piggyback.
Limit	numeric(18,4)		Tax amount limit
Limit2			Tax2 amount limit
LimitType	smallint		Tax limit type. 1=floor, 2=ceiling
CumulativeType			Cumulative tax limit type. 1=floor, 2=ceiling
GLCodeID	int		Reserved

WebAccess

Column	Datatype	Null Option	Description
WebAccess	int	NO
Name	varchar(32)

WebConfigs

Column	Datatype	Null Option	Description
WebConfigID	int	IDENTITY
Name	varchar(32)	NO
Value	varchar(64)
Cache	int

WebLinks

Column	Datatype	Null Option	Description
WebLinkID	int	IDENTITY
LinkType		NO
SortOrder
Title	varchar(24)
URL	varchar(128)