Reporting Data Dictionary

Table of contents¶

In this topic:

Section 2
Section 3
Section 4
Section 5
Reporting Database
Section 7
Tables
Section 9
Section 10
Section 11
Section 12
Section 13
Section 14
[cache].[Session]
[cache].[Store]
[dbo].[AutoOrderChangeLog]
[dbo].[AutoOrderChargeLog]
[dbo].[AutoOrderDetails]
Indexes
[dbo].[AutoOrderPaymentTypes]
[dbo].[AutoOrderProcessTypes]
[dbo].[AutoOrders]
Section 24
Indexes
[dbo].[AutoOrderSchedules]
[dbo].[AutoOrderStatusChangeLog]
[dbo].[AutoOrderStatuses]
[dbo].[Bills]
Indexes
[dbo].[BillStatuses]
[dbo].[BillTypes]
[dbo].[BinaryPlacementTypes]
[dbo].[BinaryTree]
[dbo].[Bonuses]
[dbo].[Broadcasts]
[dbo].[BroadcastTypes]
[dbo].[CodedRanks]
[dbo].[CodingTypes]
[dbo].[CommandLog]
[dbo].[CommissionBinaryTree]
[dbo].[CommissionBonuses]
[dbo].[CommissionCurrentExchangeRates]
[dbo].[CommissionCustomers]
[dbo].[CommissionDetails]
Indexes
[dbo].[CommissionEnrollerTree]
[dbo].[CommissionExchangeRates]
[dbo].[CommissionOverrides]
[dbo].[CommissionRankGroups]
[dbo].[CommissionRuns]
[dbo].[CommissionRunStatuses]
[dbo].[Commissions]
[dbo].[CommissionUniLevelTree]
[dbo].[CommissionVolumes]
[dbo].[CommissionVolumeTypes]
[dbo].[Company]
[dbo].[CompanyNews]
[dbo].[CompanyNewsDepartments]
[dbo].[Countries]
[dbo].[CountryRegions]
[dbo].[CreditCardTypes]
[dbo].[Currencies]
[dbo].[CustomerAccountChangeLog]
[dbo].[CustomerAccounts]
Section 66
Section 67
Indexes
[dbo].[CustomerAdjustments]
[dbo].[CustomerChangeLog]
[dbo].[CustomerContacts]
Section 72
[dbo].[CustomerEventHistory]
[dbo].[CustomerEvents]
[dbo].[CustomerExtendedChangeLogs]
[dbo].[CustomerExtendedDetails]
Indexes
[dbo].[CustomerExtendedGroups]
Indexes
[dbo].[CustomerFieldNames]
Section 81
[dbo].[CustomerInquiries]
[dbo].[CustomerInquiryCategories]
[dbo].[CustomerInquiryStatuses]
[dbo].[CustomerInquiryTypes]
[dbo].[CustomerLeads]
Indexes
[dbo].[CustomerOverrides]
[dbo].[CustomerPayoutSettings]
[dbo].[CustomerPointAccounts]
[dbo].[CustomerRankChangeLog]
[dbo].[Customers]
Section 93
Section 94
Section 95
Indexes
[dbo].[CustomerSiteChangeLog]
[dbo].[CustomerSites]
Section 99
Indexes
[dbo].[CustomerSocialNetworks]
[dbo].[CustomerStatusChangeLog]
[dbo].[CustomerStatuses]
[dbo].[CustomerSubscriptions]
[dbo].[CustomerTempAuthRequests]
[dbo].[CustomerTerminationReasons]
[dbo].[CustomerTransactionTypes]
[dbo].[CustomerTypeChangeLog]
[dbo].[CustomerTypes]
[dbo].[CustomerWall]
[dbo].[Departments]
[dbo].[EmailOutLog]
[dbo].[EnrollerTree]
[dbo].[EnrollerTreeHistory]
[dbo].[ExpectedPaymentFieldNames]
[dbo].[ExpectedPayments]
Indexes
[dbo].[ExpectedPaymentStatusTypes]
[dbo].[ExpectedRetailPayments]
Indexes
[dbo].[FrequencyTypes]
[dbo].[Guests]
Section 123
Indexes
[dbo].[GuestStatusTypes]
[dbo].[ImageFiles]
[dbo].[ItemChangeLog]
[dbo].[ItemCountryRegions]
[dbo].[ItemDynamicKitCategories]
[dbo].[ItemDynamicKitCategoryItemMembers]
[dbo].[ItemDynamicKitCategoryMembers]
[dbo].[ItemFieldNames]
Section 133
[dbo].[ItemGroupMembers]
[dbo].[ItemImages]
[dbo].[ItemLanguages]
[dbo].[ItemPointAccounts]
[dbo].[ItemPrices]
Indexes
[dbo].[Items]
Section 141
Indexes
[dbo].[ItemStaticKitMembers]
[dbo].[ItemSubscriptions]
[dbo].[ItemTypes]
[dbo].[ItemWarehouses]
[dbo].[Languages]
[dbo].[MerchantDeclineLog]
Indexes
[dbo].[MerchantDeclineReasons]
[dbo].[MerchantTypes]
[dbo].[OrderBatches]
[dbo].[OrderChangeLog]
[dbo].[OrderDetails]
Section 155
Indexes
[dbo].[OrderFieldNames]
Section 158
[dbo].[Orders]
Section 160
Section 161
Section 162
Indexes
[dbo].[OrderStatusChangeLog]
[dbo].[OrderStatuses]
[dbo].[OrderStatusOrderSubStatuses]
[dbo].[OrderSubStatusTypes]
[dbo].[OrderTypes]
[dbo].[Overrides]
[dbo].[Parties]
Indexes
[dbo].[PartyGuests]
[dbo].[PartyStatuses]
[dbo].[PartyTypes]
[dbo].[PayableTypes]
[dbo].[PaymentCards]
[dbo].[PaymentCardTypes]
[dbo].[Payments]
Indexes
[dbo].[PaymentTypes]
[dbo].[PayoutBills]
[dbo].[Payouts]
Indexes
[dbo].[PayoutTypes]
[dbo].[PeriodRankScores]
[dbo].[Periods]
[dbo].[PeriodTypes]
[dbo].[PeriodVolumes]
Section 189
Section 190
Section 191
Section 192
Section 193
Section 194
Section 195
Indexes
[dbo].[PointAccounts]
[dbo].[PointTransactions]
[dbo].[PointTransactionTypes]
[dbo].[PriceTypes]
[dbo].[Ranks]
[dbo].[ReplacementCategories]
[dbo].[ReturnCategories]
[dbo].[ShipCarriers]
[dbo].[ShipMethods]
[dbo].[ShipRates]
[dbo].[SmsMessages]
[dbo].[SmsStatuses]
[dbo].[SocialNetworks]
[dbo].[Subscriptions]
[dbo].[TaxAddressTypes]
[dbo].[TaxCodeTypes]
[dbo].[TaxNameTypes]
[dbo].[UniLevelTree]
[dbo].[UnilevelTreeHistory]
[dbo].[Users]
[dbo].[VolumePushCycleLogs]
[dbo].[WalletTypes]
[dbo].[WarehouseCurrencies]
[dbo].[Warehouses]
[dbo].[WebCategories]
[dbo].[WebCategoryItems]
Views
[dbo].[BinaryDownline]
[dbo].[BinaryUpline]
[dbo].[CustomerTransactions]
Section 227
[dbo].[EnrollerDownline]
[dbo].[EnrollerUpline]
[dbo].[UniLevelDownline]
[dbo].[UniLevelUpline]
Stored Procedures
[cache].[ExpireSessions]
[cache].[Get]
[cache].[GetSession]
[cache].[Purge]
[cache].[Set]
[cache].[SetSession]
[dbo].[AuthenticateCustomer]
Section 240
[dbo].[CommandExecute]
[dbo].[IndexOptimize]
[queue].[Refresh]
Section 244
Section 245
Section 246
Scalar-valued Functions
[dbo].[GetLocalDate]
[dbo].[VerifyCustomerTempAuthHash]

Section 2¶

[dbo].[CommissionUniLevelTree] ........................................................................................................................54

[dbo].[CommissionVolumes]................................................................................................................................55

[dbo].[CommissionVolumeTypes]........................................................................................................................56

[dbo].[Company]..................................................................................................................................................57

[dbo].[CompanyNews].........................................................................................................................................58

[dbo].[CompanyNewsDepartments] ....................................................................................................................59

[dbo].[Countries]..................................................................................................................................................60

[dbo].[CountryRegions]........................................................................................................................................61

[dbo].[CreditCardTypes] ......................................................................................................................................62

[dbo].[Currencies]................................................................................................................................................63

[dbo].[CustomerAccountChangeLog] ..................................................................................................................64

[dbo].[CustomerAccounts]...................................................................................................................................65

[dbo].[CustomerAdjustments]..............................................................................................................................69

[dbo].[CustomerChangeLog] ...............................................................................................................................70

[dbo].[CustomerContacts]....................................................................................................................................71

[dbo].[CustomerEventHistory] .............................................................................................................................73

[dbo].[CustomerEvents].......................................................................................................................................74

[dbo].[CustomerExtendedChangeLogs] ..............................................................................................................75

[dbo].[CustomerExtendedDetails]........................................................................................................................76

[dbo].[CustomerExtendedGroups].......................................................................................................................78

[dbo].[CustomerFieldNames]...............................................................................................................................80

[dbo].[CustomerInquiries] ....................................................................................................................................82

[dbo].[CustomerInquiryCategories]......................................................................................................................83

[dbo].[CustomerInquiryStatuses].........................................................................................................................84

[dbo].[CustomerInquiryTypes] .............................................................................................................................85

[dbo].[CustomerLeads]........................................................................................................................................86

[dbo].[CustomerOverrides] ..................................................................................................................................88

[dbo].[CustomerPayoutSettings]..........................................................................................................................89

[dbo].[CustomerPointAccounts]...........................................................................................................................90

[dbo].[CustomerRankChangeLog].......................................................................................................................91

[dbo].[Customers]................................................................................................................................................92

[dbo].[CustomerSiteChangeLog].........................................................................................................................97

[dbo].[CustomerSites]..........................................................................................................................................98

[dbo].[CustomerSocialNetworks].......................................................................................................................101

[dbo].[CustomerStatusChangeLog]...................................................................................................................102

[dbo].[CustomerStatuses]..................................................................................................................................103

[dbo].[CustomerSubscriptions] ..........................................................................................................................104

[dbo].[CustomerTempAuthRequests]................................................................................................................105

Section 3¶

[dbo].[CustomerTerminationReasons]...............................................................................................................106

[dbo].[CustomerTransactionTypes] ...................................................................................................................107

[dbo].[CustomerTypeChangeLog] .....................................................................................................................108

[dbo].[CustomerTypes]......................................................................................................................................109

[dbo].[CustomerWall].........................................................................................................................................110

[dbo].[Departments]...........................................................................................................................................111

[dbo].[EmailOutLog]...........................................................................................................................................112

[dbo].[EnrollerTree]............................................................................................................................................113

[dbo].[EnrollerTreeHistory] ................................................................................................................................114

[dbo].[ExpectedPaymentFieldNames]...............................................................................................................115

[dbo].[ExpectedPayments] ................................................................................................................................116

[dbo].[ExpectedPaymentStatusTypes] ..............................................................................................................118

[dbo].[ExpectedRetailPayments] .......................................................................................................................119

[dbo].[FrequencyTypes].....................................................................................................................................121

[dbo].[Guests]....................................................................................................................................................122

[dbo].[GuestStatusTypes]..................................................................................................................................125

[dbo].[ImageFiles]..............................................................................................................................................126

[dbo].[ItemChangeLog]......................................................................................................................................127

[dbo].[ItemCountryRegions]...............................................................................................................................128

[dbo].[ItemDynamicKitCategories].....................................................................................................................129

[dbo].[ItemDynamicKitCategoryItemMembers]..................................................................................................130

[dbo].[ItemDynamicKitCategoryMembers].........................................................................................................131

[dbo].[ItemFieldNames] .....................................................................................................................................132

[dbo].[ItemGroupMembers] ...............................................................................................................................134

[dbo].[ItemImages].............................................................................................................................................135

[dbo].[ItemLanguages].......................................................................................................................................136

[dbo].[ItemPointAccounts] .................................................................................................................................137

[dbo].[ItemPrices] ..............................................................................................................................................138

[dbo].[Items].......................................................................................................................................................140

[dbo].[ItemStaticKitMembers] ............................................................................................................................143

[dbo].[ItemSubscriptions]...................................................................................................................................144

[dbo].[ItemTypes]...............................................................................................................................................145

[dbo].[ItemWarehouses] ....................................................................................................................................146

[dbo].[Languages]..............................................................................................................................................147

[dbo].[MerchantDeclineLog] ..............................................................................................................................148

[dbo].[MerchantDeclineReasons] ......................................................................................................................150

[dbo].[MerchantTypes].......................................................................................................................................151

[dbo].[OrderBatches] .........................................................................................................................................152

Section 4¶

[dbo].[OrderChangeLog]....................................................................................................................................153

[dbo].[OrderDetails] ...........................................................................................................................................154

[dbo].[OrderFieldNames]...................................................................................................................................157

[dbo].[Orders] ....................................................................................................................................................159

[dbo].[OrderStatusChangeLog] .........................................................................................................................164

[dbo].[OrderStatuses] ........................................................................................................................................165

[dbo].[OrderStatusOrderSubStatuses] ..............................................................................................................166

[dbo].[OrderSubStatusTypes]............................................................................................................................167

[dbo].[OrderTypes] ............................................................................................................................................168

[dbo].[Overrides]................................................................................................................................................169

[dbo].[Parties] ....................................................................................................................................................170

[dbo].[PartyGuests]............................................................................................................................................172

[dbo].[PartyStatuses].........................................................................................................................................173

[dbo].[PartyTypes] .............................................................................................................................................174

[dbo].[PayableTypes].........................................................................................................................................175

[dbo].[PaymentCards]........................................................................................................................................176

[dbo].[PaymentCardTypes]................................................................................................................................177

[dbo].[Payments] ...............................................................................................................................................178

[dbo].[PaymentTypes] .......................................................................................................................................180

[dbo].[PayoutBills]..............................................................................................................................................181

[dbo].[Payouts] ..................................................................................................................................................182

[dbo].[PayoutTypes] ..........................................................................................................................................184

[dbo].[PeriodRankScores] .................................................................................................................................185

[dbo].[Periods] ...................................................................................................................................................186

[dbo].[PeriodTypes] ...........................................................................................................................................187

[dbo].[PeriodVolumes].......................................................................................................................................188

[dbo].[PointAccounts] ........................................................................................................................................197

[dbo].[PointTransactions]...................................................................................................................................198

[dbo].[PointTransactionTypes]...........................................................................................................................199

[dbo].[PriceTypes] .............................................................................................................................................200

[dbo].[Ranks] .....................................................................................................................................................201

[dbo].[ReplacementCategories].........................................................................................................................202

[dbo].[ReturnCategories] ...................................................................................................................................203

[dbo].[ShipCarriers] ...........................................................................................................................................204

[dbo].[ShipMethods] ..........................................................................................................................................205

[dbo].[ShipRates]...............................................................................................................................................206

[dbo].[SmsMessages]........................................................................................................................................207

[dbo].[SmsStatuses] ..........................................................................................................................................208

Section 5¶

[dbo].[SocialNetworks].......................................................................................................................................209

[dbo].[Subscriptions]..........................................................................................................................................210

[dbo].[TaxAddressTypes] ..................................................................................................................................211

[dbo].[TaxCodeTypes].......................................................................................................................................212

[dbo].[TaxNameTypes]......................................................................................................................................213

[dbo].[UniLevelTree]..........................................................................................................................................214

[dbo].[UnilevelTreeHistory]................................................................................................................................215

[dbo].[Users]......................................................................................................................................................216

[dbo].[VolumePushCycleLogs] ..........................................................................................................................217

[dbo].[WalletTypes]............................................................................................................................................218

[dbo].[WarehouseCurrencies]............................................................................................................................219

[dbo].[Warehouses] ...........................................................................................................................................220

[dbo].[WebCategories].......................................................................................................................................221

[dbo].[WebCategoryItems].................................................................................................................................222

Views ......................................................................................................................................................................223

[dbo].[BinaryDownline] ......................................................................................................................................224

[dbo].[BinaryUpline]...........................................................................................................................................225

[dbo].[CustomerTransactions] ...........................................................................................................................226

[dbo].[EnrollerDownline] ....................................................................................................................................228

[dbo].[EnrollerUpline].........................................................................................................................................229

[dbo].[UniLevelDownline]...................................................................................................................................230

[dbo].[UniLevelUpline] .......................................................................................................................................231

Stored Procedures..................................................................................................................................................232

[cache].[ExpireSessions]...................................................................................................................................233

[cache].[Get]......................................................................................................................................................234

[cache].[GetSession] .........................................................................................................................................235

[cache].[Purge] ..................................................................................................................................................236

[cache].[Set] ......................................................................................................................................................237

[cache].[SetSession]..........................................................................................................................................238

[dbo].[AuthenticateCustomer]............................................................................................................................239

[dbo].[CommandExecute]..................................................................................................................................241

[dbo].[IndexOptimize] ........................................................................................................................................242

[queue].[Refresh]...............................................................................................................................................243

Scalar-valued Functions .........................................................................................................................................247

[dbo].[GetLocalDate] .........................................................................................................................................248

[dbo].[VerifyCustomerTempAuthHash]..............................................................................................................249

Reporting Database¶

Reporting

Author: Jacob Golden

Page 6 of 249

Database Properties

Database Options

Property

Value

SQL Server Version

Azure Sql Database

Compatibility Level

Max

Creation date

12/19/2024 9:36:19 PM

Property

Value

Compatibility Level

Database collation

SQL_Latin1_General_CP1_CI_AS

Restrict access

MULTI_USER

Is read-only

False

Auto close

False

Auto shrink

False

Database status

ONLINE

In standby

False

Cleanly shutdown

False

Supplemental logging enabled

False

Snapshot isolation state

ON

Read committed snapshot on

True

Recovery model

FULL

Page verify option

CHECKSUM

Auto create statistics

True

Auto update statistics

True

Auto update statistics asynchronously

False

ANSI NULL default

False

ANSI NULL enabled

False

ANSI padding enabled

False

ANSI warnings enabled

False

Arithmetic abort enabled

False

Concatenating NULL yields NULL

False

Numeric roundabort enabled

False

Quoted Identifier On

False

Recursive triggers enabled

False

Close cursors on commit

False

Section 7¶

Reporting

Author: Jacob Golden

Page 7 of 249

Local cursors by default

False

Fulltext enabled

True

Trustworthy

False

Database chaining

False

Forced parameterization

False

Master key encrypted by server

False

Published

False

Subscribed

False

Merge published

False

Is distribution database

False

Sync with backup

False

Service broker GUID

31e7c3cf-8dec-4b7c-b01a-e9270c4bf7d1

Service broker enabled

False

Date correlation

False

CDC enabled

False

Encrypted

True

Honor broker priority

False

Containment

NONE

Target recovery time

Tables¶

Reporting > Tables

Author: Jacob Golden

Page 8 of 249

Objects

Name

cache.Session

Web session caching

cache.Store

Web shopping cart caching

dbo.AutoOrderChangeLog

Auto Order Template Change History

dbo.AutoOrderChargeLog

Payments Against Auto Orders

dbo.AutoOrderDetails

Auto Order Template Details

dbo.AutoOrderPaymentTypes

Auto Order Payment Type Reference Table

dbo.AutoOrderProcessTypes

Auto Order Process Type Reference Table

dbo.AutoOrders

Auto Order Templates

dbo.AutoOrderSchedules

Auto Order Schedules

dbo.AutoOrderStatusChangeLog

Auto Order Status Change History

dbo.AutoOrderStatuses

Auto Order Statuses Reference Table

dbo.Bills

Bills To Pay (Typically Commissions Bills)

dbo.BillStatuses

Bill Statuses Reference Table

dbo.BillTypes

Bill Types Reference Table

dbo.BinaryPlacementTypes

Binary Placement Types Reference Table

dbo.BinaryTree

Binary Tree (Memory Optimized Table)

dbo.Bonuses

Bonuses Reference Table

dbo.Broadcasts

Broadcasts (Emails or SMS)

dbo.BroadcastTypes

Broadcast Types Reference Table

dbo.CodedRanks

One Time Commissions Events

dbo.CodingTypes

Coded Ranks Coding Types

dbo.CommandLog

Section 9¶

Reporting > Tables

Author: Jacob Golden

Page 9 of 249

Log Table for Maintenance Jobs

dbo.CommissionBinaryTree

Commission Binary Tree (Recommended To Use dbo.BinaryDownline or dbo.BinaryUpline Views Instead)

dbo.CommissionBonuses

Commission Bonuses

dbo.CommissionCurrentExchangeRates

Exchange Rate Defined By Client For Currency Codes Used In Paying Commissions

dbo.CommissionCustomers

Customer Rank Stats Per Accepted Commission Run

dbo.CommissionDetails

Details Per Commission Run

dbo.CommissionEnrollerTree

Commissions Enroller Tree

dbo.CommissionExchangeRates

Commission Exchange Rates History By Commission Run

dbo.CommissionOverrides

Commission Overrides

dbo.CommissionRankGroups

Rank Groups for Commissions

dbo.CommissionRuns

Commission Run Reference Table

dbo.CommissionRunStatuses

Commission Run Statuses Reference Table

dbo.Commissions

Commissions Calculated

dbo.CommissionUniLevelTree

Commissions Unilevel Tree

dbo.CommissionVolumes

Commission Volumes

dbo.CommissionVolumeTypes

Commission Volume Types Reference Table

dbo.Company

Your Company Key With Exigo

dbo.CompanyNews

Company News

dbo.CompanyNewsDepartments

Company News Departments

dbo.Countries

Countries

dbo.CountryRegions

Country Regions

dbo.CreditCardTypes

Credit Card Types

dbo.Currencies

Currencies (Allows Custom Currencies)

dbo.CustomerAccountChangeLog

Change Log for the dbo.CustomerAccounts Records

dbo.CustomerAccounts

Customer Payment Accounts

dbo.CustomerAdjustments

Customer Adjustments

Section 10¶

Reporting > Tables

Author: Jacob Golden

Page 10 of 249

dbo.CustomerChangeLog

Log History For Changes in dbo.Customers

dbo.CustomerContacts

Customer Contact Info

dbo.CustomerEventHistory

Customer Event History

dbo.CustomerEvents

Reference Data Used For Auto Responders

dbo.CustomerExtendedChangeLogs

History of Customer Extended Group Changes

dbo.CustomerExtendedDetails

Fully Custom Fields For Customer Records

dbo.CustomerExtendedGroups

Fully Custom Fields For Customer Groups

dbo.CustomerFieldNames

Custom Display Names For Fields in dbo.Customers Table

dbo.CustomerInquiries

Customer Inquiries

dbo.CustomerInquiryCategories

Customer Inquiry Category Reference Table

dbo.CustomerInquiryStatuses

Customer Inquiry Status Reference Table

dbo.CustomerInquiryTypes

Customer Inquiry Type Reference Table

dbo.CustomerLeads

Customer Sales Leads

dbo.CustomerOverrides

Commissions Overrides Per Customer

dbo.CustomerPayoutSettings

Customer Payout Settings

dbo.CustomerPointAccounts

Customer Point Accounts

dbo.CustomerRankChangeLog

Customer Rank Change History

dbo.Customers

Customers

dbo.CustomerSiteChangeLog

dbo.CustomerSites

Replicated Site Population Data

dbo.CustomerSocialNetworks

Customer Social Networks

dbo.CustomerStatusChangeLog

History of Customer Status Changes

dbo.CustomerStatuses

Customer Status Reference Table

dbo.CustomerSubscriptions

Customer Subscriptions

dbo.CustomerTempAuthRequests

Local Authentication Caching Table

dbo.CustomerTerminationReasons

Customer Termination Reasons Reference Table

Section 11¶

Reporting > Tables

Author: Jacob Golden

Page 11 of 249

dbo.CustomerTransactionTypes

Customer Transaction Types Reference Table

dbo.CustomerTypeChangeLog

History of Customer Type Changes

dbo.CustomerTypes

Customer Types Reference Table

dbo.CustomerWall

Downline Events

dbo.Departments

User Departments

dbo.EmailOutLog

Email Sent Logs

dbo.EnrollerTree

Enroller Tree (Memory Optimized Table)

dbo.EnrollerTreeHistory

History of Changes in Enroller Tree

dbo.ExpectedPaymentFieldNames

Expected Payment Field Names

dbo.ExpectedPayments

Expected Payments

dbo.ExpectedPaymentStatusTypes

Expected Payment Status Types

dbo.ExpectedRetailPayments

Expected Retail Payments

dbo.FrequencyTypes

Frequency Types

dbo.Guests

Party Guests

dbo.GuestStatusTypes

Guest Status Types

dbo.ImageFiles

Image Files (Binary)

dbo.ItemChangeLog

History of Item Changes

dbo.ItemCountryRegions

Tax Override by Item Per Country and Region

dbo.ItemDynamicKitCategories

Categories for Dynamic Item Kits

dbo.ItemDynamicKitCategoryItemMembers

Item Members in Dynamic Kit Categories

dbo.ItemDynamicKitCategoryMembers

Layout of Items and Quantity in Dynamic Kit

dbo.ItemFieldNames

Display Names for Customer Fields in dbo.Items Table

dbo.ItemGroupMembers

dbo.ItemImages

Image Mapping for Items

dbo.ItemLanguages

Item Descriptions in Other Languages

dbo.ItemPointAccounts

Item Point Accounts

Section 12¶

Reporting > Tables

Author: Jacob Golden

Page 12 of 249

dbo.ItemPrices

Item Prices

dbo.Items

Items

dbo.ItemStaticKitMembers

Static Kit Members

dbo.ItemSubscriptions

Item Subscriptions

dbo.ItemTypes

Item Types Reference Table

dbo.ItemWarehouses

Item Warehouses

dbo.Languages

Languages

dbo.MerchantDeclineLog

Merchant Decline Log

dbo.MerchantDeclineReasons

Merchant Decline Reasons Reference Table

dbo.MerchantTypes

Merchant Types

dbo.OrderBatches

Order Batches

dbo.OrderChangeLog

History of Order Changes

dbo.OrderDetails

Order Details

dbo.OrderFieldNames

Display Names for Custom Fields in Orders Table

dbo.Orders

Orders

dbo.OrderStatusChangeLog

History of Order Status Changes

dbo.OrderStatuses

Order Status Reference Table

dbo.OrderStatusOrderSubStatuses

Order Status Order Sub Status Mapping Table

dbo.OrderSubStatusTypes

Order Sub Status Types

dbo.OrderTypes

Order Types Reference Table

dbo.Overrides

Overrides

dbo.Parties

Parties

dbo.PartyGuests

Party Guests

dbo.PartyStatuses

Party Statuses Reference Table

dbo.PartyTypes

Party Types Reference Table

dbo.PayableTypes

Payable Types

Section 13¶

Reporting > Tables

Author: Jacob Golden

Page 13 of 249

dbo.PaymentCards

Payment Cards

dbo.PaymentCardTypes

Payment Card Types Reference Table

dbo.Payments

Payments

dbo.PaymentTypes

Payment Types

dbo.PayoutBills

Bills to be Paid Out

dbo.Payouts

Payouts

dbo.PayoutTypes

Payout Types

dbo.PeriodRankScores

Period Rank Scores

dbo.Periods

Periods

dbo.PeriodTypes

Period Types

dbo.PeriodVolumes

Period Volumes

dbo.PointAccounts

Point Accounts

dbo.PointTransactions

Point Transactions

dbo.PointTransactionTypes

Point Transaction Types

dbo.PriceTypes

Price Types

dbo.Ranks

Ranks

dbo.ReplacementCategories

Replacement Categories

dbo.ReturnCategories

Return Categories

dbo.ShipCarriers

Ship Carriers

dbo.ShipMethods

Ship Methods

dbo.ShipRates

Ship Rates

dbo.SmsMessages

SMS Messages

dbo.SmsStatuses

SMS Statuses Reference Table

dbo.SocialNetworks

Social Networks Reference Table

dbo.Subscriptions

Subscriptions Reference Table

dbo.TaxAddressTypes

Tax Address Types Reference Table

Section 14¶

Reporting > Tables

Author: Jacob Golden

Page 14 of 249

dbo.TaxCodeTypes

Tax Code Types Reference Table

dbo.TaxNameTypes

Tax Name Types Reference Table

dbo.UniLevelTree

Unilevel Tree (Memory Optimized Table)

dbo.UnilevelTreeHistory

History of Unilievel Tree Changes

dbo.Users

Users

dbo.VolumePushCycleLogs

Volume Push Cycle Logs

dbo.WalletTypes

Wallet Types

dbo.WarehouseCurrencies

Warehouse Currencies

dbo.Warehouses

Warehouses

dbo.WebCategories

Web Categories

dbo.WebCategoryItems

Web Category Items

[cache].[Session]¶

Reporting > Tables > cache.Session

Author: Jacob Golden

Page 15 of 249

MS_Description

Web session caching

Columns

Indexes

Uses

[cache]

Used By

[cache].[ExpireSessions]

[cache].[GetSession]

[cache].[SetSession]

Key

Name

Data Type

Max Length (Bytes)

Nullability

SessionID

Unique ID

uniqueidentifier

NOT NULL

ModifiedDate

Record Modified Date

datetime

NOT NULL

Data

Session Data

nvarchar(max)

max

NOT NULL

Key

Name

Key Columns

Unique

Compression

Session_PK

SessionID

True

PAGE

[cache].[Store]¶

Reporting > Tables > cache.Store

Author: Jacob Golden

Page 16 of 249

MS_Description

Web shopping cart caching

Columns

Indexes

Uses

[cache]

Used By

[cache].[Get]

[cache].[Purge]

[cache].[Set]

Key

Name

Data Type

Max Length (Bytes)

Nullability

ID

Unique ID

nvarchar(850)

1700

NOT NULL

Data

Shopping Cart Data

nvarchar(max)

max

NULL allowed

CreatedDate

Record Create Date

datetime

NOT NULL

SecondsToLive

Data Retention

bigint

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CacheStore

ID

True

PAGE

[dbo].[AutoOrderChangeLog]¶

Reporting > Tables > dbo.AutoOrderChangeLog

Author: Jacob Golden

Page 17 of 249

MS_Description

Auto Order Template Change History

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

AutoOrderChangeLogID

Unique ID - Not typically used anywhere

int

NOT NULL

AutoOrderID

AutoOrderID - Map back to dbo.AutoOrders

int

NOT NULL

ModifiedDate

Change Date

datetime

NOT NULL

ModifiedBy

Who Changed It

nvarchar(50)

NOT NULL

Detail

From and To Values

nvarchar(max)

max

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_AutoOrderChangeLog

AutoOrderChangeLog-

ID

True

PAGE

IX_AutoOrderChangeLog_AutoOrderID

AutoOrderID

PAGE

[dbo].[AutoOrderChargeLog]¶

Reporting > Tables > dbo.AutoOrderChargeLog

Author: Jacob Golden

Page 18 of 249

MS_Description

Payments Against Auto Orders

Columns

Indexes

Key

Name

Data Type

Max Length

(Bytes)

Nullability

AutoOrderChargeLogID

Unique ID - Not typically used anywhere

int

NOT NULL

PaymentTypeID

Payment Type - Map to dbo.PaymentTypes

int

NOT NULL

WarehouseID

Warehouse - Map to dbo.Warehouses

int

NOT NULL

CurrencyCode

Currency Code of the Payment - Map to dbo.Currencies

nvarchar(3)

NOT NULL

OrderID

Order Reference - Map to dbo.Orders

int

NOT NULL

Amount

Payment Amount in Native Currency Code

money

NOT NULL

EntryDate

Payment Date

datetime

NOT NULL

BatchID

Payment Batch

int

NOT NULL

IsAuthorized

Authorization Boolean

bit

NOT NULL

ServerResponse

Vendor Server Response

nvarchar(1000)

2000

NOT NULL

PaymentID

Payment ID - Map to dbo.Payments

int

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_AutoOrderChargeLog

AutoOrderChargeLogID

True

PAGE

[dbo].[AutoOrderDetails]¶

Reporting > Tables > dbo.AutoOrderDetails

Author: Jacob Golden

Page 19 of 249

MS_Description

Auto Order Template Details

Columns

Key

Name

Data Type

Max Length (Bytes)

Nullability

AutoOrderID

Auto Order ID - Map to dbo.AutoOrders

int

NOT NULL

OrderLine

Order Line - Map to dbo.OrderDetails

int

NOT NULL

AutoOrderDetailID

Unique ID

uniqueidentifier

NULL allowed

ParentAutoOrderDetailID

Unique ID for Parent

uniqueidentifier

NULL allowed

ItemID

Item ID - Map to dbo.Items

int

NOT NULL

ItemCode

Item Code - Map to dbo.Items

nvarchar(max)

max

NULL allowed

ItemDescription

Item Description

nvarchar(255)

NOT NULL

Quantity

Quantity on Auto Order

money

NOT NULL

PriceEach

Price Each for Item

money

NOT NULL

PriceTotal

Quantity * PriceEach

money

NOT NULL

BusinessVolumeEach

BV Each for Item

money

NOT NULL

BusinessVolume

Total BV

money

NOT NULL

CommissionableVolumeEach

CV Each for Item

money

NOT NULL

CommissionableVolume

Total CV

money

NOT NULL

PriceEachOverride

Price Each Override for Item

money

NULL allowed

TaxableEachOverride

Taxable Override for Item

money

NULL allowed

ShippingPriceEachOverride

Shipping Override for Item

money

NULL allowed

BusinessVolumeEachOverride

BV Override for Item

money

NULL allowed

CommissionableVolumeEachOverride

CV Override for Item

money

NULL allowed

ParentItemID

int

NULL allowed

Indexes¶

Reporting > Tables > dbo.AutoOrderDetails

Author: Jacob Golden

Page 20 of 249

Parent Item ID - Map to dbo.Items

Reference1

Custom Field

nvarchar(100)

NOT NULL

DetailStartDate

datetime

NULL allowed

DetailEndDate

datetime

NULL allowed

DetailInterval

int

NULL allowed

DetailNextRunDate

datetime

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_AutoOrderDetails

AutoOrderID, OrderLine

True

PAGE

[dbo].[AutoOrderPaymentTypes]¶

Reporting > Tables > dbo.AutoOrderPaymentTypes

Author: Jacob Golden

Page 21 of 249

MS_Description

Auto Order Payment Type Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

AutoOrderPaymentTypeID

Unique ID - Not Typically Used Anywhere

int

NOT NULL

AutoOrderPaymentTypeDescription

Payment Type

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_AutoOrderPaymentTypes

AutoOrderPaymentTypeID

True

PAGE

[dbo].[AutoOrderProcessTypes]¶

Reporting > Tables > dbo.AutoOrderProcessTypes

Author: Jacob Golden

Page 22 of 249

MS_Description

Auto Order Process Type Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

AutoOrderProcessTypeID

Unique ID - Typically Not Used Anywhere

int

NOT NULL

AutoOrderProcessTypeDescription

Process Type

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_AutoOrderProcessTypes

AutoOrderProcessTypeID

True

PAGE

[dbo].[AutoOrders]¶

Reporting > Tables > dbo.AutoOrders

Author: Jacob Golden

Page 23 of 249

MS_Description

Auto Order Templates

Columns

Key

Name

Data Type

Max Length

(Bytes)

Nullability

AutoOrderID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

AutoOrderStatusID

Auto Order Status ID - Map to dbo.AutoOrderStatuses

int

NOT NULL

FrequencyTypeID

Frequency Type ID - Map to dbo.FrequencyTypes

int

NOT NULL

StartDate

Auto Order Start Date

datetime

NOT NULL

StopDate

Auto Order Stop Date

datetime

NULL allowed

LastRunDate

Last Auto Order Run Date

datetime

NULL allowed

NextRunDate

Next Auto Order Run Date

datetime

NULL allowed

CancelledDate

Cancel Date

datetime

NULL allowed

CurrencyCode

Currency Code of Auto Order - Map to dbo.Currencies

nvarchar(3)

NOT NULL

WarehouseID

Warehouse ID - Map to dbo.Warehouses

int

NOT NULL

ShipMethodID

Ship Method ID - Map to dbo.ShipMethods

int

NOT NULL

AutoOrderPaymentTypeID

Auto Order Payment Type ID - Map to dbo.AutoOrder-

PaymentTypes

int

NOT NULL

AutoOrderProcessTypeID

Auto Order Process Type ID - Map to dbo.AutoOrderProcess-

Types

int

NOT NULL

FirstName

Customer First Name

nvarchar(50)

NOT NULL

MiddleName

Customer Middle Name

nvarchar(50)

NOT NULL

LastName

Customer Last Name

nvarchar(50)

NOT NULL

NameSuffix

Customer Name Suffix

nvarchar(50)

NOT NULL

Company

nvarchar(100)

NOT NULL

Section 24¶

Reporting > Tables > dbo.AutoOrders

Author: Jacob Golden

Page 24 of 249

Company Name

Address1

Order Address Line 1

nvarchar(100)

NOT NULL

Address2

Order Address Line 2

nvarchar(100)

NOT NULL

Address3

Order Address Line 3

nvarchar(100)

NOT NULL

City

Order City

nvarchar(50)

NOT NULL

State

Order State

nvarchar(50)

NOT NULL

Zip

Order Zip Code

nvarchar(50)

NOT NULL

Country

Order Country

nvarchar(50)

NOT NULL

County

Order County

nvarchar(50)

NOT NULL

Email

Customer Order Email

nvarchar(200)

NOT NULL

Phone

Customer Order Phone

nvarchar(50)

NOT NULL

Notes

Order Notes

nvarchar(500)

1000

NOT NULL

Total

Order Total in Native Currency Code

money

NOT NULL

SubTotal

Order SubTotal in Native Currency Code

money

NOT NULL

TaxTotal

Order TaxTotal in Native Currency Code

money

NOT NULL

ShippingTotal

Order Shipping in Native Currency Code

money

NOT NULL

DiscountTotal

Order Discount Total in Native Currency Code

money

NOT NULL

BusinessVolumeTotal

Order BV in Native Currency Code

money

NOT NULL

CommissionableVolumeTotal

Order CV in Native Currency Code

money

NOT NULL

AutoOrderDescription

Order Description

nvarchar(100)

NOT NULL

Other11

Custom Field

nvarchar(400)

NULL allowed

Other12

Custom Field

nvarchar(400)

NULL allowed

Other13

Custom Field

nvarchar(400)

NULL allowed

Other14

Custom Field

nvarchar(400)

NULL allowed

Other15

Custom Field

nvarchar(400)

NULL allowed

Other16

Custom Field

nvarchar(400)

NULL allowed

Indexes¶

Reporting > Tables > dbo.AutoOrders

Author: Jacob Golden

Page 25 of 249

Other17

Custom Field

nvarchar(400)

NULL allowed

Other18

Custom Field

nvarchar(400)

NULL allowed

Other19

Custom Field

nvarchar(400)

NULL allowed

Other20

Custom Field

nvarchar(400)

NULL allowed

CreatedDate

Auto Order Created Date

datetime

NOT NULL

ModifiedDate

Auto Order Last Modified Date

datetime

NOT NULL

CreatedBy

Auto Order Created By Name

nvarchar(30)

NOT NULL

ModifiedBy

Auto Order Last Modified By Name

nvarchar(30)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_AutoOrders

AutoOrderID

True

PAGE

IX_AutoOrders_CustomerID

CustomerID

PAGE

[dbo].[AutoOrderSchedules]¶

Reporting > Tables > dbo.AutoOrderSchedules

Author: Jacob Golden

Page 26 of 249

MS_Description

Auto Order Schedules

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

AutoOrderID

Auto Order ID - Map to dbo.AutoOrders

int

NOT NULL

ScheduledDate

Auto Order Schedule Date

datetime

NOT NULL

IsEnabled

Is Schedule Enabled?

bit

NOT NULL

ProcessedDate

Processed Date for Scheduled Date

datetime

NULL allowed

OrderID

OrderID of Auto Order - Map to dbo.Orders

int

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_AutoOrderSchedules

AutoOrderID, Scheduled-

Date

True

PAGE

[dbo].[AutoOrderStatusChangeLog]¶

Reporting > Tables > dbo.AutoOrderStatusChangeLog

Author: Jacob Golden

Page 27 of 249

MS_Description

Auto Order Status Change History

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

AutoOrderStatusChangeLogID

Unique ID - Typically not used anywhere

int

NOT NULL

AutoOrderID

Auto Order ID - Map to dbo.AutoOrders

int

NOT NULL

AutoOrderStatusID

Auto Order Status ID - Map to dbo.AutoOrderStatuses

int

NOT NULL

ModifiedDate

Last Status Modified Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_AutoOrderStatusChangeLog

AutoOrderStatus-

ChangeLogID

True

PAGE

IX_AutoOrderStatusChangeLog_AutoOrderID

AutoOrderID

PAGE

[dbo].[AutoOrderStatuses]¶

Reporting > Tables > dbo.AutoOrderStatuses

Author: Jacob Golden

Page 28 of 249

MS_Description

Auto Order Statuses Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

AutoOrderStatusID

Unique ID

int

NOT NULL

AutoOrderStatusDescription

Status Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_AutoOrderStatuses

AutoOrderStatusID

True

PAGE

[dbo].[Bills]¶

Reporting > Tables > dbo.Bills

Author: Jacob Golden

Page 29 of 249

MS_Description

Bills To Pay (Typically Commissions Bills)

Columns

Key

Name

Data Type

Max Length

(Bytes)

Nullability

BillID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

BillStatusID

Bill Status ID - Map to dbo.BillStatuses

int

NOT NULL

DueDate

Bill Due Date

datetime

NOT NULL

BillTypeID

Bill Type ID - Map to dbo.BillTypes

int

NOT NULL

CurrencyCode

Currency Code of Bill To Pay

nvarchar(3)

NOT NULL

Amount

Total Amount in Native Currency Type

money

NOT NULL

CommissionRunID

Commission Run ID for Bill - Map to dbo.Commissions

int

NULL allowed

IsOtherIncome

Is Other Income?

bit

NOT NULL

Reference

Custom Reference Text

nvarchar(50)

NULL allowed

PayableTypeIDOverride

Payable Type Override - Map to dbo.PayableTypes

int

NULL allowed

Notes

Custom Notes Text

nvarchar(500)

1000

NOT NULL

ModifiedDate

Last Modified Date of Record

datetime

NOT NULL

CreatedBy

Record Created By Name

nvarchar(30)

NOT NULL

ModifiedBy

Record Last Modified By Name

nvarchar(30)

NOT NULL

TaxablePeriodTypeID

Taxable Period Type - Map to dbo.PeriodTypes

int

NULL allowed

TaxablePeriodID

Taxable Period ID - Map to dbo.Periods

int

NULL allowed

Indexes¶

Reporting > Tables > dbo.Bills

Author: Jacob Golden

Page 30 of 249

Key

Name

Key Columns

Unique

Compression

PK_Bills

BillID

True

PAGE

[dbo].[BillStatuses]¶

Reporting > Tables > dbo.BillStatuses

Author: Jacob Golden

Page 31 of 249

MS_Description

Bill Statuses Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

BillStatusID

Unique ID

int

NOT NULL

BillStatusDescription

Status Description

nvarchar(20)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_BillStatuses

BillStatusID

True

PAGE

[dbo].[BillTypes]¶

Reporting > Tables > dbo.BillTypes

Author: Jacob Golden

Page 32 of 249

MS_Description

Bill Types Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

BillTypeID

Unique ID

int

NOT NULL

BillTypeDescription

Bill Type Descriptions

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_BillTypes

BillTypeID

True

PAGE

[dbo].[BinaryPlacementTypes]¶

Reporting > Tables > dbo.BinaryPlacementTypes

Author: Jacob Golden

Page 33 of 249

MS_Description

Binary Placement Types Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

BinaryPlacementTypeID

Unique ID

int

NOT NULL

BinaryPlacementDescription

Binary Placement Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_BinaryPlacementTypes

BinaryPlacementTypeID

True

PAGE

[dbo].[BinaryTree]¶

Reporting > Tables > dbo.BinaryTree

Author: Jacob Golden

Page 34 of 249

MS_Description

Binary Tree (Memory Optimized Table)

Columns

Indexes

Used By

[dbo].[BinaryDownline]

[dbo].[BinaryUpline]

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

ParentID

Parent ID - Map to dbo.Customers

int

NOT NULL

NestedLevel

Nested Level

int

NOT NULL

Placement

Placement

int

NOT NULL

Lft

Index Left Boundary

int

NOT NULL

Rgt

Index Right Boundary

int

NOT NULL

Key

Name

Key Columns

Unique

PK_BinaryTree

CustomerID

True

[dbo].[Bonuses]¶

Reporting > Tables > dbo.Bonuses

Author: Jacob Golden

Page 35 of 249

MS_Description

Bonuses Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

BonusID

Unique ID

int

NOT NULL

BonusDescription

Bonus Description

nvarchar(100)

NOT NULL

PeriodTypeID

Period Type ID - Map to dbo.PeriodTypes

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_Bonuses

BonusID

True

PAGE

[dbo].[Broadcasts]¶

Reporting > Tables > dbo.Broadcasts

Author: Jacob Golden

Page 36 of 249

MS_Description

Broadcasts (Emails or SMS)

Columns

Indexes

Key

Name

Data Type

Max Length

(Bytes)

Nullability

BroadCastID

Unique ID

int

NOT NULL

BroadcastTypeID

Broadcast Type ID - Map to dbo.BroadcastTypes

int

NOT NULL

Subject

Email or SMS Subject Text

nvarchar(255)

NOT NULL

SendEmail

Send Email? Boolean

bit

NOT NULL

EmailContent

Email Content (HTML Enabled)

nvarchar(max)

max

NOT NULL

SendSms

Send SMS? Boolean

bit

NOT NULL

SmsContent

SMS Content

nvarchar(max)

max

NOT NULL

Notes

Broadcast Notes

nvarchar(max)

max

NOT NULL

IsEnabled

Is Enabled?

bit

NOT NULL

CreatedDate

Broadcast Created Date

datetime

NOT NULL

ModifiedDate

Broadcast Last Modified Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_Broadcasts

BroadCastID

True

PAGE

[dbo].[BroadcastTypes]¶

Reporting > Tables > dbo.BroadcastTypes

Author: Jacob Golden

Page 37 of 249

MS_Description

Broadcast Types Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

BroadcastTypeID

Unique ID

int

NOT NULL

BroadcastTypeDescription

Type Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_BroadcastTypes

BroadcastTypeID

True

PAGE

[dbo].[CodedRanks]¶

Reporting > Tables > dbo.CodedRanks

Author: Jacob Golden

Page 38 of 249

MS_Description

One Time Commissions Events

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CodedRankEntryID

Unique ID

int

NOT NULL

CodingTypeID

Coding Type ID - Map to dbo.CodingTypes

int

NOT NULL

CustomerID

Customer iD - Map to dbo.Customers

int

NOT NULL

RankID

Rank ID - Map to dbo.Ranks

int

NOT NULL

CodedDate

Coded Date

datetime

NOT NULL

CodedToCustomerID

Customer Record Applies To - Map to dbo.Customers

int

NOT NULL

EntryDate

Record Create Date

datetime

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CodedRanks

CodedRankEntryID

True

PAGE

IX_CodedRanks_CodingTypeID

CodingTypeID

PAGE

IX_CodedRanks_CustomerID

CustomerID

PAGE

[dbo].[CodingTypes]¶

Reporting > Tables > dbo.CodingTypes

Author: Jacob Golden

Page 39 of 249

MS_Description

Coded Ranks Coding Types

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CodingTypeID

Unique ID

int

NOT NULL

CodingTypeDescription

Coding Description

nvarchar(100)

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_CodingTypes

CodingTypeID

True

PAGE

[dbo].[CommandLog]¶

Reporting > Tables > dbo.CommandLog

Author: Jacob Golden

Page 40 of 249

MS_Description

Log Table for Maintenance Jobs

Columns

Indexes

Used By

[dbo].[CommandExecute]

Key

Name

Data Type

Max Length (Bytes)

Nullability

Identity

ID

int

NOT NULL

1 - 1

DatabaseName

[sys].[sysname]

NULL allowed

SchemaName

[sys].[sysname]

NULL allowed

ObjectName

[sys].[sysname]

NULL allowed

ObjectType

char(2)

NULL allowed

IndexName

[sys].[sysname]

NULL allowed

IndexType

tinyint

NULL allowed

StatisticsName

[sys].[sysname]

NULL allowed

PartitionNumber

int

NULL allowed

ExtendedInfo

xml

max

NULL allowed

Command

nvarchar(max)

max

NOT NULL

CommandType

nvarchar(60)

NOT NULL

StartTime

datetime2

NOT NULL

EndTime

datetime2

NULL allowed

ErrorNumber

int

NULL allowed

ErrorMessage

nvarchar(max)

max

NULL allowed

Key

Name

Key Columns

Unique

PK_CommandLog

ID

True

[dbo].[CommissionBinaryTree]¶

Reporting > Tables > dbo.CommissionBinaryTree

Author: Jacob Golden

Page 41 of 249

MS_Description

Commission Binary Tree (Recommended To Use dbo.BinaryDownline or dbo.BinaryUpline Views Instead)

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CommissionRunID

Commission Run ID - Map to dbo.CommissionRuns

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

ParentID

Parent ID - Map to dbo.Customers

int

NOT NULL

NestedLevel

Nested Level

int

NOT NULL

Placement

Placement In Tree

int

NOT NULL

Lft

Left Index Boundary

int

NOT NULL

Rgt

Right Index Boundary

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CommissionBinaryTree

CommissionRunID,

CustomerID

True

PAGE

[dbo].[CommissionBonuses]¶

Reporting > Tables > dbo.CommissionBonuses

Author: Jacob Golden

Page 42 of 249

MS_Description

Commission Bonuses

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CommissionRunID

Commission Run ID - Map to dbo.CommissionRuns

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

BonusID

Bonus ID - Map to dbo.Bonuses

int

NOT NULL

Amount

Dollar Amount in Currency Code of the dbo.Commissions Table by

CustomerID and CommissionsRunID

money

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CommissionBonuses

CommissionRunID,

CustomerID, BonusID

True

PAGE

[dbo].[CommissionCurrentExchangeRates]¶

Reporting > Tables > dbo.CommissionCurrentExchangeRates

Author: Jacob Golden

Page 43 of 249

MS_Description

Exchange Rate Defined By Client For Currency Codes Used In Paying Commissions

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CurrencyCode

Currency Code

nvarchar(3)

NOT NULL

Rate

Rate (Customer Defined)

money

NOT NULL

Fee

Fee

money

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CommissionCurrentExchangeRates

CurrencyCode

True

PAGE

[dbo].[CommissionCustomers]¶

Reporting > Tables > dbo.CommissionCustomers

Author: Jacob Golden

Page 44 of 249

MS_Description

Customer Rank Stats Per Accepted Commission Run

Columns

Indexes

Key

Name

Data Type

Max Length

(Bytes)

Nullability

CommissionRunID

Commission Run ID - Map to dbo.CommissionRuns

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

CustomerTypeID

Customer Type ID - Map to dbo.CustomerTypes

int

NOT NULL

CustomerStatusID

Customer Status ID - Map to dbo.CustomerStatuses

int

NOT NULL

RankID

Highest Rank ID Ever Achieved - Map to dbo.Ranks

int

NULL allowed

NewRankID

New Highest Rank ID - RankID Gets Updated Next Run With

This Value - Map to dbo.Ranks

int

NULL allowed

PaidRankID

Paid Rank ID For Current Commission Run - Map to dbo.Ranks

int

NULL allowed

Country

Country of Customer Record

nvarchar(50)

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_CommissionCustomers

CommissionRunID,

CustomerID

True

PAGE

[dbo].[CommissionDetails]¶

Reporting > Tables > dbo.CommissionDetails

Author: Jacob Golden

Page 45 of 249

MS_Description

Details Per Commission Run

Columns

Key

Name

Data Type

Max Length

(Bytes)

Nullability

CommissionRunID

Commission Run ID - Map to dbo.CommissionRuns

int

NOT NULL

CommissionDetailID

Unique ID

bigint

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

BonusID

Bonus ID - Map to dbo.Bonuses

int

NOT NULL

FromCustomerID

Downline Customer Commission Is From

int

NULL allowed

OrderID

Downline Customer Order Commission Is From

int

NULL allowed

SourceAmount

Order Dollar Amount Converted to CurrencyCode

money

NOT NULL

Percentage

Commission Percent

money

NOT NULL

CommissionAmount

Calculated Commission Amount Converted to CurrencyCode

money

NOT NULL

Level

Levels Up From Source Customer

int

NULL allowed

PaidLevel

Levels Up From Source Customer - Bonus Specific

int

NULL allowed

CurrencyCode

Currency Code For SourceAmount & CommissionAmount

nvarchar(3)

NOT NULL

EntryCurrencyCode

Currency Code For Order

nvarchar(3)

NOT NULL

EntrySourceAmount

Order Amount Converted to USD From EntryCurrencyCode

money

NOT NULL

EntryCommissionAmount

Commission Amount Converted to USD From EntryCurrency-

Code

money

NOT NULL

ToRankID

Prior Rank ID

int

NULL allowed

FromRankID

New Rank ID

int

NULL allowed

Indexes¶

Reporting > Tables > dbo.CommissionDetails

Author: Jacob Golden

Page 46 of 249

Key

Name

Key Columns

Unique

Compression

PK_CommissionDetails

CommissionRunID,

CommissionDetailID

True

PAGE

[dbo].[CommissionEnrollerTree]¶

Reporting > Tables > dbo.CommissionEnrollerTree

Author: Jacob Golden

Page 47 of 249

MS_Description

Commissions Enroller Tree

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CommissionRunID

Commission Run ID - Map to dbo.CommissionRuns

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

EnrollerID

Enroller ID - Map to dbo.Customers

int

NOT NULL

NestedLevel

Nested Level

int

NOT NULL

Lft

Left Index Boundary

int

NOT NULL

Rgt

Right Index Boundary

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CommissionEnrollerTree

CommissionRunID,

CustomerID

True

PAGE

[dbo].[CommissionExchangeRates]¶

Reporting > Tables > dbo.CommissionExchangeRates

Author: Jacob Golden

Page 48 of 249

MS_Description

Commission Exchange Rates History By Commission Run

Columns

Indexes

Key

Name

Data Type

Max Length

(Bytes)

Nullability

CommissionRunID

Commission Run ID - Map to dbo.CommissionRuns

int

NOT NULL

CurrencyCode

Currency Code

nvarchar(3)

NOT NULL

Rate

Customer Defined Rate

money

NOT NULL

Fee

Fee

money

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CommissionExchangeRates

CommissionRunID,

CurrencyCode

True

PAGE

[dbo].[CommissionOverrides]¶

Reporting > Tables > dbo.CommissionOverrides

Author: Jacob Golden

Page 49 of 249

MS_Description

Commission Overrides

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CommissionRunID

Commission Run ID - Map to dbo.CommissionRuns

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

OverrideID

Override ID - Map to dbo.Overrides

int

NOT NULL

Qualifies

Qualifies? Boolean

bit

NOT NULL

PeriodTypeID

Period Type ID - Map to dbo.Periods

int

NULL allowed

StartPeriodID

Period Start ID - Map to dbo.Periods

int

NULL allowed

EndPeriodID

Period End ID - Map to dbo.Periods

int

NULL allowed

Amount

Amount of Override (Raw number that can be exchanged

downstream)

money

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CommissionOverrides

CommissionRunID,

CustomerID, OverrideID

True

PAGE

[dbo].[CommissionRankGroups]¶

Reporting > Tables > dbo.CommissionRankGroups

Author: Jacob Golden

Page 50 of 249

MS_Description

Rank Groups for Commissions

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CommissionRunID

Commission Run ID - Map to dbo.CommissionRuns

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

RankGroupID

Unique ID

int

NOT NULL

RankID

Highest Rank ID Ever Achieved

int

NOT NULL

PaidRankID

Paid Rank ID For Current Commission Run

int

NOT NULL

LegRankID

Highest Paid Rank ID in Leg

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CommissionRankGroups

CommissionRunID,

CustomerID, RankGroup-

ID

True

PAGE

[dbo].[CommissionRuns]¶

Reporting > Tables > dbo.CommissionRuns

Author: Jacob Golden

Page 51 of 249

MS_Description

Commission Run Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length

(Bytes)

Nullability

CommissionRunID

Unique ID

int

NOT NULL

CommissionRunDescription

Description for the Run

nvarchar(100)

NOT NULL

PeriodTypeID

Period Type ID - Map to dbo.PeriodTypes

int

NOT NULL

PeriodID

Period ID - Map to dbo.Periods

int

NOT NULL

RunDate

Run Date of Commission Run

datetime

NULL allowed

AcceptedDate

Acceptance Date of Commission Run

datetime

NULL allowed

CommissionRunStatusID

Commission Run Status - Map to dbo.CommissionRun-

Statuses

int

NOT NULL

HideFromWeb

Hide From Web? Boolean

bit

NOT NULL

PlanID

Plan Project ID

int

NULL allowed

ArchivedDate

Archive Date of Commission Run

datetime

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_CommissionRuns

CommissionRunID

True

PAGE

[dbo].[CommissionRunStatuses]¶

Reporting > Tables > dbo.CommissionRunStatuses

Author: Jacob Golden

Page 52 of 249

MS_Description

Commission Run Statuses Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CommissionRunStatusID

Unique ID

int

NOT NULL

CommissionRunStatusDescription

Status Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CommissionRunStatuses

CommissionRunStatusID

True

PAGE

[dbo].[Commissions]¶

Reporting > Tables > dbo.Commissions

Author: Jacob Golden

Page 53 of 249

MS_Description

Commissions Calculated

Columns

Indexes

Key

Name

Data Type

Max Length

(Bytes)

Nullability

CommissionRunID

Commission Run ID - Map to dbo.CommissionRuns

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

CurrencyCode

Currency Code of Commission Paid

nvarchar(3)

NOT NULL

Earnings

Amount in Currency Code

money

NOT NULL

PreviousBalance

Unused

money

NOT NULL

BalanceForward

Unused

money

NOT NULL

Fee

Fee

money

NOT NULL

Total

Earnings Minus Fee

money

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_Commissions

CommissionRunID, Customer-

ID

True

PAGE

[dbo].[CommissionUniLevelTree]¶

Reporting > Tables > dbo.CommissionUniLevelTree

Author: Jacob Golden

Page 54 of 249

MS_Description

Commissions Unilevel Tree

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CommissionRunID

Commission Run ID - Map to dbo.CommissionRuns

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

SponsorID

Sponsor ID - Map to dbo.Customers

int

NOT NULL

NestedLevel

Nested Level

int

NOT NULL

Lft

Index Left Boundary

int

NOT NULL

Rgt

Index Right Boundary

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CommissionUniLevelTree

CommissionRunID,

CustomerID

True

PAGE

[dbo].[CommissionVolumes]¶

Reporting > Tables > dbo.CommissionVolumes

Author: Jacob Golden

Page 55 of 249

MS_Description

Commission Volumes

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CommissionRunID

Commission Run ID - Map to dbo.CommissionRuns

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

VolumeTypeID

Volume Type ID - Map to dbo.CommissionVolumeTypes

int

NOT NULL

Volume

Volume

money

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CommissionVolumes

CommissionRunID,

CustomerID, VolumeTypeID

True

PAGE

[dbo].[CommissionVolumeTypes]¶

Reporting > Tables > dbo.CommissionVolumeTypes

Author: Jacob Golden

Page 56 of 249

MS_Description

Commission Volume Types Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length

(Bytes)

Nullability

CommissionRunID

Commission Run ID - Map to dbo.CommissionRuns

int

NOT NULL

VolumeTypeID

Unique ID

int

NOT NULL

VolumeTypeDescription

Description

nvarchar(500)

1000

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CommissionVolumeTypes

CommissionRunID,

VolumeTypeID

True

PAGE

[dbo].[Company]¶

Reporting > Tables > dbo.Company

Author: Jacob Golden

Page 57 of 249

MS_Description

Your Company Key With Exigo

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CompanyKey

Company Key

nvarchar(15)

NOT NULL

Name

Company Name

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_Company

CompanyKey

True

PAGE

[dbo].[CompanyNews]¶

Reporting > Tables > dbo.CompanyNews

Author: Jacob Golden

Page 58 of 249

MS_Description

Company News

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CompanyNewsID

Unique ID

int

NOT NULL

Title

News Title

nvarchar(225)

NOT NULL

Content

News Body Content

nvarchar(max)

max

NOT NULL

IsCompanyWide

Is Company Wide? Boolean

bit

NOT NULL

AvailableOnWeb

Available On Web? Boolean

bit

NOT NULL

CreatedDate

Created Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CompanyNews

CompanyNewsID

True

PAGE

[dbo].[CompanyNewsDepartments]¶

Reporting > Tables > dbo.CompanyNewsDepartments

Author: Jacob Golden

Page 59 of 249

MS_Description

Company News Departments

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

NewsID

Company News ID - Map to dbo.CompanyNews

int

NOT NULL

DepartmentID

Department ID - Map to dbo.Departments

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CompanyNewsDepartments

NewsID, DepartmentID

True

PAGE

[dbo].[Countries]¶

Reporting > Tables > dbo.Countries

Author: Jacob Golden

Page 60 of 249

MS_Description

Countries

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CountryCode

Country Code

nvarchar(2)

NOT NULL

CountryDescription

Country Description

nvarchar(50)

NULL allowed

Priority

Sorting Order

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_Countries

CountryCode

True

PAGE

[dbo].[CountryRegions]¶

Reporting > Tables > dbo.CountryRegions

Author: Jacob Golden

Page 61 of 249

MS_Description

Country Regions

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CountryCode

Country Code - Map to dbo.Countries

nvarchar(2)

NOT NULL

RegionCode

Region Code

nvarchar(50)

NOT NULL

RegionDescription

Region Description

nvarchar(50)

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_CountryRegions

CountryCode, RegionCode

True

PAGE

[dbo].[CreditCardTypes]¶

Reporting > Tables > dbo.CreditCardTypes

Author: Jacob Golden

Page 62 of 249

MS_Description

Credit Card Types

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CreditCardTypeID

Unique ID

int

NOT NULL

CreditCardTypeDescription

Description

nvarchar(100)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CreditCardTypes

CreditCardTypeID

True

PAGE

[dbo].[Currencies]¶

Reporting > Tables > dbo.Currencies

Author: Jacob Golden

Page 63 of 249

MS_Description

Currencies (Allows Custom Currencies)

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CurrencyCode

Currency Code

nvarchar(3)

NOT NULL

CurrencyDescription

Currency Description

nvarchar(50)

NULL allowed

CurrencySymbol

Currency Symbol

nvarchar(10)

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_Currencies

CurrencyCode

True

PAGE

[dbo].[CustomerAccountChangeLog]¶

Reporting > Tables > dbo.CustomerAccountChangeLog

Author: Jacob Golden

Page 64 of 249

MS_Description

Change Log for the dbo.CustomerAccounts Records

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerAccountChangeLogID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.CustomerAccounts

int

NOT NULL

ModifiedDate

Record Modified Date

datetime

NOT NULL

ModifiedBy

Record Modified By User

nvarchar(50)

NOT NULL

Detail

Detail of Change From and To

nvarchar(max)

max

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerAccountChangeLog

CustomerAccount-

ChangeLogID

True

PAGE

IX_CustomerAccountChangeLog_CustomerID

CustomerID

PAGE

[dbo].[CustomerAccounts]¶

Reporting > Tables > dbo.CustomerAccounts

Author: Jacob Golden

Page 65 of 249

MS_Description

Customer Payment Accounts

Columns

Key

Name

Data Type

Max Length

(Bytes)

Nullability

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

PrimaryCreditCardDisplay

Primary CC Display - Masked

nvarchar(50)

NULL allowed

PrimaryExpirationMonth

Primary CC Expire Month

int

NULL allowed

PrimaryExpirationYear

Primary CC Expire Year

int

NULL allowed

PrimaryCreditCardTypeID

Primary CC Type ID - Map to dbo.PaymentCardTypes

int

NOT NULL

PrimaryBillingName

Primary Billing Person Name

nvarchar(50)

NOT NULL

PrimaryBillingAddress

Primary Billing Address Field 1

nvarchar(100)

NULL allowed

PrimaryBillingAddress2

Primary Billing Address Field 2

nvarchar(100)

NULL allowed

PrimaryBillingCity

Primary Billing City

nvarchar(50)

NULL allowed

PrimaryBillingState

Primary Billing State

nvarchar(50)

NULL allowed

PrimaryBillingZip

Primary Billing Zip

nvarchar(50)

NULL allowed

PrimaryBillingCountry

Primary Billing Country

nvarchar(50)

NULL allowed

SecondaryCreditCardDisplay

Secondary CC Display - Masked

nvarchar(50)

NULL allowed

SecondaryExpirationMonth

Secondary CC Expire Month

int

NULL allowed

SecondaryExpirationYear

Secondary CC Expire Month

int

NULL allowed

SecondaryCreditCardTypeID

Secondary CC Type ID - Map to dbo.PaymentCardTypes

int

NOT NULL

SecondaryBillingName

Secondary Billing Person Name

nvarchar(50)

NOT NULL

SecondaryBillingAddress

Secondary Billing Address Field 1

nvarchar(100)

NULL allowed

SecondaryBillingAddress2

Secondary Billing Address Field 2

nvarchar(100)

NULL allowed

Section 66¶

Reporting > Tables > dbo.CustomerAccounts

Author: Jacob Golden

Page 66 of 249

SecondaryBillingCity

Secondary Billing City

nvarchar(50)

NULL allowed

SecondaryBillingState

Secondary Billing State

nvarchar(50)

NULL allowed

SecondaryBillingZip

Secondary Billing Zip

nvarchar(50)

NULL allowed

SecondaryBillingCountry

Secondary Billing Country

nvarchar(50)

NULL allowed

BankAccountNumber

Bank Account Number - Masked

nvarchar(50)

NULL allowed

BankRoutingNumber

Bank Routing Number

nvarchar(50)

NOT NULL

BankNameOnAccount

Name on Bank Account

nvarchar(50)

NOT NULL

BankAccountAddress

Bank Account Address

nvarchar(100)

NULL allowed

BankAccountCity

Bank Account City

nvarchar(50)

NULL allowed

BankAccountState

Bank Account State

nvarchar(50)

NULL allowed

BankAccountZip

Bank Account Zip

nvarchar(50)

NULL allowed

BankAccountCountry

Bank Account Country

nvarchar(50)

NULL allowed

DriversLicenseNumber

Drivers License Number

nvarchar(50)

NOT NULL

DepositNameOnAcount

Deposit Name on Account

nvarchar(50)

NOT NULL

DepositAccountNumber

Deposit Account Number - Masked

nvarchar(50)

NOT NULL

DepositRoutingNumber

Deposit Routing Number

nvarchar(50)

NOT NULL

Iban

IBAN

nvarchar(50)

NOT NULL

SwiftCode

Swift Code

nvarchar(50)

NOT NULL

CheckIban

Check IBAN

nvarchar(50)

NOT NULL

CheckSwiftCode

Check Swift Code

nvarchar(50)

NOT NULL

DepositBankName

Deposit Bank Name

nvarchar(100)

NOT NULL

DepositBankAddress

Deposit Bank Address

nvarchar(250)

NOT NULL

DepositBankCity

Deposit Bank City

nvarchar(50)

NOT NULL

DepositBankState

Deposit Bank State

nvarchar(50)

NOT NULL

DepositBankZip

Deposit Bank Zip

nvarchar(50)

NOT NULL

DepositBankCountry

Deposit Bank Country

nvarchar(50)

NOT NULL

Section 67¶

Reporting > Tables > dbo.CustomerAccounts

Author: Jacob Golden

Page 67 of 249

PrimaryWalletTypeID

Primary Wallet Type ID - Map to dbo.WalletTypes

int

NOT NULL

PrimaryWalletAccount

Primary Wallet Account - Masked

nvarchar(max)

max

NULL allowed

PrimaryWalletOther1

Primary Wallet Other 1 (Custom)

nvarchar(200)

NULL allowed

PrimaryWalletOther2

Primary Wallet Other 2 (Custom)

nvarchar(200)

NULL allowed

PrimaryWalletOther3

Primary Wallet Other 3 (Custom)

nvarchar(200)

NULL allowed

SecondaryWalletTypeID

Secondary Wallet Type ID - Map to dbo.WalletTypes

int

NOT NULL

SecondaryWalletAccount

Secondary Wallet Account - Masked

nvarchar(max)

max

NULL allowed

SecondaryWalletOther1

Secondary Wallet Other 1 (Custom)

nvarchar(200)

NULL allowed

SecondaryWalletOther2

Secondary Wallet Other 2 (Custom)

nvarchar(200)

NULL allowed

SecondaryWalletOther3

Secondary Wallet Other 3 (Custom)

nvarchar(200)

NULL allowed

TertiaryWalletTypeID

Tertiary Wallet Type ID - Map to dbo.WalletTypes

int

NOT NULL

TertiaryWalletAccount

Tertiary Wallet Account - Masked

nvarchar(max)

max

NULL allowed

TertiaryWalletOther1

Tertiary Wallet Other 1 (Custom)

nvarchar(200)

NULL allowed

TertiaryWalletOther2

Tertiary Wallet Other 2 (Custom)

nvarchar(200)

NULL allowed

TertiaryWalletOther3

Tertiary Wallet Other 3 (Custom)

nvarchar(200)

NULL allowed

QuaternaryWalletTypeID

Quaternary Wallet Type ID - Map to dbo.WalletTypes

int

NOT NULL

QuaternaryWalletAccount

Quaternary Wallet Account - Masked

nvarchar(max)

max

NULL allowed

QuaternaryWalletOther1

Quaternary Wallet Other 1 (Custom)

nvarchar(200)

NULL allowed

QuaternaryWalletOther2

Quaternary Wallet Other 2 (Custom)

nvarchar(200)

NULL allowed

QuaternaryWalletOther3

Quaternary Wallet Other 3 (Custom)

nvarchar(200)

NULL allowed

QuinaryWalletTypeID

Quinary Wallet Type ID - Map to dbo.WalletTypes

int

NOT NULL

QuinaryWalletAccount

Quinary Wallet Account - Masked

nvarchar(max)

max

NULL allowed

QuinaryWalletOther1

Quinary Wallet Other 1 (Custom)

nvarchar(200)

NULL allowed

QuinaryWalletOther2

Quinary Wallet Other 2 (Custom)

nvarchar(200)

NULL allowed

QuinaryWalletOther3

Quinary Wallet Other 3 (Custom)

nvarchar(200)

NULL allowed

CreditCardTokenType

Credit Card Token Type - (Needs reference table created)

int

NULL allowed

Indexes¶

Reporting > Tables > dbo.CustomerAccounts

Author: Jacob Golden

Page 68 of 249

Used By

[queue].[Refresh]

CreditCardToken

Credit Card Token

nvarchar(50)

NULL allowed

CreditCardTokenType2

Secondary Credit Card Token Type - (Needs reference table

created)

int

NULL allowed

CreditCardToken2

Secondary Credit Card Token

nvarchar(50)

NULL allowed

ModifiedDate

Last Record Modified Date

datetime

NOT NULL

ModifiedBy

Last Record Modified By User

nvarchar(50)

NOT NULL

BankAccountType

Bank Account Type - (Needs reference table created)

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerAccounts

CustomerID

True

PAGE

[dbo].[CustomerAdjustments]¶

Reporting > Tables > dbo.CustomerAdjustments

Author: Jacob Golden

Page 69 of 249

MS_Description

Customer Adjustments

Columns

Indexes

Used By

[dbo].[CustomerTransactions]

Key

Name

Data Type

Max Length

(Bytes)

Nullability

TransactionID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

CustomerTransactionTypeID

Customer Transaction Type ID - Map to dbo.Customer-

TransactionTypes

int

NOT NULL

TransactionDate

Transaction Date

datetime

NOT NULL

Amount

Amount in Specified Currency Code

money

NOT NULL

CurrencyCode

Currency Code - Map to dbo.Currencies

nvarchar(3)

NOT NULL

ModifiedBy

Record Last Modified By User

nvarchar(30)

NOT NULL

Notes

Notes

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerAdjustments

TransactionID

True

PAGE

IX_CustomerAdjustments_CustomerID

CustomerID

PAGE

[dbo].[CustomerChangeLog]¶

Reporting > Tables > dbo.CustomerChangeLog

Author: Jacob Golden

Page 70 of 249

MS_Description

Log History For Changes in dbo.Customers

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerChangeLogID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

ModifiedDate

Last Record Modified Date

datetime

NOT NULL

ModifiedBy

Last Record Modified By User

nvarchar(100)

NOT NULL

Detail

Detail From and To Values

nvarchar(4000)

8000

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerChangeLog

CustomerChangeLogID

True

PAGE

IX_CustomerChangeLog_CustomerID

CustomerID

PAGE

[dbo].[CustomerContacts]¶

Reporting > Tables > dbo.CustomerContacts

Author: Jacob Golden

Page 71 of 249

MS_Description

Customer Contact Info

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerContactID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

FirstName

Customer First Name

nvarchar(50)

NOT NULL

LastName

Customer Last Name

nvarchar(50)

NOT NULL

Email

Customer Email

nvarchar(50)

NOT NULL

Phone

Customer Phone

nvarchar(20)

NOT NULL

Phone2

Customer Phone 2

nvarchar(20)

NOT NULL

MobilePhone

Customer Mobile Phone

nvarchar(20)

NOT NULL

Fax

Customer Fax

nvarchar(20)

NOT NULL

Address1

Customer Address Field 1

nvarchar(50)

NOT NULL

Address2

Customer Address Field 2

nvarchar(50)

NOT NULL

City

Customer City

nvarchar(50)

NOT NULL

State

Customer State

nvarchar(20)

NOT NULL

Zip

Customer Zip

nvarchar(20)

NOT NULL

Country

Customer Country

nvarchar(10)

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerContacts

CustomerContactID

True

PAGE

Section 72¶

Reporting > Tables > dbo.CustomerContacts

Author: Jacob Golden

Page 72 of 249

IX_CustomerContacts_CustomerID

CustomerID

PAGE

[dbo].[CustomerEventHistory]¶

Reporting > Tables > dbo.CustomerEventHistory

Author: Jacob Golden

Page 73 of 249

MS_Description

Customer Event History

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerEventID

Customer Event ID - Map to dbo.CustomerEvents

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

Key1

Custom Values

int

NOT NULL

Key2

Custom Values

int

NOT NULL

Key3

Custom Values

int

NOT NULL

EntryDate

Record Insert Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerEventHistory

CustomerEventID,

CustomerID, Key1, Key2,

Key3

True

PAGE

[dbo].[CustomerEvents]¶

Reporting > Tables > dbo.CustomerEvents

Author: Jacob Golden

Page 74 of 249

MS_Description

Reference Data Used For Auto Responders

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerEventID

Unique ID

int

NOT NULL

CustomerEventDescription

Description

nvarchar(50)

NOT NULL

Key1Description

Custom Values

nvarchar(50)

NOT NULL

Key2Description

Custom Values

nvarchar(50)

NOT NULL

Key3Description

Custom Values

nvarchar(50)

NOT NULL

Notes

Notes

nvarchar(max)

max

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerEvents

CustomerEventID

True

PAGE

[dbo].[CustomerExtendedChangeLogs]¶

Reporting > Tables > dbo.CustomerExtendedChangeLogs

Author: Jacob Golden

Page 75 of 249

MS_Description

History of Customer Extended Group Changes

Columns

Indexes

Key

Name

Data Type

Max Length

(Bytes)

Nullability

CustomerExtendedChangeLogId

Unique ID

int

NOT NULL

CustomerExtendedId

Customer Extended Detail ID - Map to dbo.CustomerExtended-

Details

int

NOT NULL

ExtendedGroupId

Customer Extended Group ID - Map to dbo.CustomerExtended-

Groups

int

NOT NULL

CustomerId

Customer ID - Map to dbo.Customers

int

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

ModifiedBy

Record Last Modified By User

nvarchar(100)

NOT NULL

Detail

Detail From and To Values

nvarchar(4000)

8000

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerExtendedChangeLogs

Customer-

ExtendedChange-

LogId

True

PAGE

IX_CustomerExtendedChangeLogs_CustomerId

CustomerId

PAGE

[dbo].[CustomerExtendedDetails]¶

Reporting > Tables > dbo.CustomerExtendedDetails

Author: Jacob Golden

Page 76 of 249

MS_Description

Fully Custom Fields For Customer Records

Columns

Key

Name

Data Type

Max Length

(Bytes)

Nullability

CustomerExtendedDetailID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

CustomerExtendedGroupID

Customer Extended Group ID - Map to dbo.CustomerExtended-

Groups

int

NOT NULL

Field1

Custom Field

nvarchar(200)

NOT NULL

Field2

Custom Field

nvarchar(200)

NOT NULL

Field3

Custom Field

nvarchar(200)

NOT NULL

Field4

Custom Field

nvarchar(200)

NOT NULL

Field5

Custom Field

nvarchar(200)

NOT NULL

Field6

Custom Field

nvarchar(200)

NOT NULL

Field7

Custom Field

nvarchar(200)

NOT NULL

Field8

Custom Field

nvarchar(200)

NOT NULL

Field9

Custom Field

nvarchar(200)

NOT NULL

Field10

Custom Field

nvarchar(200)

NOT NULL

Field11

Custom Field

nvarchar(200)

NOT NULL

Field12

Custom Field

nvarchar(200)

NOT NULL

Field13

Custom Field

nvarchar(200)

NOT NULL

Field14

Custom Field

nvarchar(200)

NOT NULL

Field15

Custom Field

nvarchar(200)

NOT NULL

Field16

Custom Field

nvarchar(200)

NOT NULL

Indexes¶

Reporting > Tables > dbo.CustomerExtendedDetails

Author: Jacob Golden

Page 77 of 249

Field17

Custom Field

nvarchar(200)

NOT NULL

Field18

Custom Field

nvarchar(200)

NOT NULL

Field19

Custom Field

nvarchar(200)

NOT NULL

Field20

Custom Field

nvarchar(200)

NOT NULL

Field21

Custom Field

nvarchar(200)

NOT NULL

Field22

Custom Field

nvarchar(200)

NOT NULL

Field23

Custom Field

nvarchar(200)

NOT NULL

Field24

Custom Field

nvarchar(200)

NOT NULL

Field25

Custom Field

nvarchar(200)

NOT NULL

Field26

Custom Field

nvarchar(200)

NOT NULL

Field27

Custom Field

nvarchar(200)

NOT NULL

Field28

Custom Field

nvarchar(200)

NOT NULL

Field29

Custom Field

nvarchar(200)

NOT NULL

Field30

Custom Field

nvarchar(200)

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerExtendedDetails

CustomerExtended-

DetailID

True

PAGE

IX_CustomerExtendedDetails_CustomerID

CustomerID

PAGE

[dbo].[CustomerExtendedGroups]¶

Reporting > Tables > dbo.CustomerExtendedGroups

Author: Jacob Golden

Page 78 of 249

MS_Description

Fully Custom Fields For Customer Groups

Columns

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerExtendedGroupID

Unique ID

int

NOT NULL

CustomerExtendedGroupDescription

Group Description

nvarchar(100)

NOT NULL

Field1Name

Custom Field

nvarchar(100)

NOT NULL

Field2Name

Custom Field

nvarchar(100)

NOT NULL

Field3Name

Custom Field

nvarchar(100)

NOT NULL

Field4Name

Custom Field

nvarchar(100)

NOT NULL

Field5Name

Custom Field

nvarchar(100)

NOT NULL

Field6Name

Custom Field

nvarchar(100)

NOT NULL

Field7Name

Custom Field

nvarchar(100)

NOT NULL

Field8Name

Custom Field

nvarchar(100)

NOT NULL

Field9Name

Custom Field

nvarchar(100)

NOT NULL

Field10Name

Custom Field

nvarchar(100)

NOT NULL

Field11Name

Custom Field

nvarchar(100)

NOT NULL

Field12Name

Custom Field

nvarchar(100)

NOT NULL

Field13Name

Custom Field

nvarchar(100)

NOT NULL

Field14Name

Custom Field

nvarchar(100)

NOT NULL

Field15Name

Custom Field

nvarchar(100)

NOT NULL

Field16Name

Custom Field

nvarchar(100)

NOT NULL

Field17Name

Custom Field

nvarchar(100)

NOT NULL

Field18Name

nvarchar(100)

NOT NULL

Indexes¶

Reporting > Tables > dbo.CustomerExtendedGroups

Author: Jacob Golden

Page 79 of 249

Custom Field

Field19Name

Custom Field

nvarchar(100)

NOT NULL

Field20Name

Custom Field

nvarchar(100)

NOT NULL

Field21Name

Custom Field

nvarchar(100)

NOT NULL

Field22Name

Custom Field

nvarchar(100)

NOT NULL

Field23Name

Custom Field

nvarchar(100)

NOT NULL

Field24Name

Custom Field

nvarchar(100)

NOT NULL

Field25Name

Custom Field

nvarchar(100)

NOT NULL

Field26Name

Custom Field

nvarchar(100)

NOT NULL

Field27Name

Custom Field

nvarchar(100)

NOT NULL

Field28Name

Custom Field

nvarchar(100)

NOT NULL

Field29Name

Custom Field

nvarchar(100)

NOT NULL

Field30Name

Custom Field

nvarchar(100)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerExtendedGroups

CustomerExtendedGroup-

ID

True

PAGE

[dbo].[CustomerFieldNames]¶

Reporting > Tables > dbo.CustomerFieldNames

Author: Jacob Golden

Page 80 of 249

MS_Description

Custom Display Names For Fields in dbo.Customers Table

Columns

Name

Data Type

Max Length (Bytes)

Nullability

Field1DisplayName

Custom Display Name for dbo.Customers.Field1

nvarchar(50)

NOT NULL

Field2DisplayName

Custom Display Name for dbo.Customers.Field2

nvarchar(50)

NOT NULL

Field3DisplayName

Custom Display Name for dbo.Customers.Field3

nvarchar(50)

NOT NULL

Field4DisplayName

Custom Display Name for dbo.Customers.Field4

nvarchar(50)

NOT NULL

Field5DisplayName

Custom Display Name for dbo.Customers.Field5

nvarchar(50)

NOT NULL

Field6DisplayName

Custom Display Name for dbo.Customers.Field6

nvarchar(50)

NOT NULL

Field7DisplayName

Custom Display Name for dbo.Customers.Field7

nvarchar(50)

NOT NULL

Field8DisplayName

Custom Display Name for dbo.Customers.Field8

nvarchar(50)

NOT NULL

Field9DisplayName

Custom Display Name for dbo.Customers.Field9

nvarchar(50)

NOT NULL

Field10DisplayName

Custom Display Name for dbo.Customers.Field10

nvarchar(50)

NOT NULL

Field11DisplayName

Custom Display Name for dbo.Customers.Field11

nvarchar(50)

NOT NULL

Field12DisplayName

Custom Display Name for dbo.Customers.Field12

nvarchar(50)

NOT NULL

Field13DisplayName

Custom Display Name for dbo.Customers.Field13

nvarchar(50)

NOT NULL

Field14DisplayName

Custom Display Name for dbo.Customers.Field14

nvarchar(50)

NOT NULL

Field15DisplayName

Custom Display Name for dbo.Customers.Field15

nvarchar(50)

NOT NULL

Date1DisplayName

Custom Display Name for dbo.Customers.Date1

nvarchar(50)

NOT NULL

Date2DisplayName

Custom Display Name for dbo.Customers.Date2

nvarchar(50)

NOT NULL

Date3DisplayName

Custom Display Name for dbo.Customers.Date3

nvarchar(50)

NOT NULL

Date4DisplayName

Custom Display Name for dbo.Customers.Date4

nvarchar(50)

NOT NULL

Date5DisplayName

nvarchar(50)

NOT NULL

Section 81¶

Reporting > Tables > dbo.CustomerFieldNames

Author: Jacob Golden

Page 81 of 249

Custom Display Name for dbo.Customers.Date5

[dbo].[CustomerInquiries]¶

Reporting > Tables > dbo.CustomerInquiries

Author: Jacob Golden

Page 82 of 249

MS_Description

Customer Inquiries

Columns

Indexes

Key

Name

Data Type

Max Length

(Bytes)

Nullability

CustomerInquiryID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

CustomerInquiryTypeID

Customer Inquiry Type ID - Map to dbo.CustomerInquiryTypes

int

NOT NULL

CustomerInquiryCategoryID

Customer Inquiry Category ID - Map to dbo.CustomerInquiry-

Categories

int

NULL allowed

CustomerInquiryStatusID

Customer Inquiry Status ID - Map to dbo.CustomerInquiry-

Statuses

int

NOT NULL

ParentID

Parent ID - Map to dbo.Customers

int

NULL allowed

CreatedDate

Inquiry Record Created Date

datetime

NOT NULL

Description

Inquiry Description

nvarchar(200)

NOT NULL

Detail

Inquiry Details

nvarchar(max)

max

NOT NULL

CreatedBy

Inquiry Created By User

nvarchar(50)

NULL allowed

AssignedTo

Inquiry Assigned To User

nvarchar(50)

NULL allowed

ClosedDate

Inquiry Closed Date

datetime

NULL allowed

ClosedBy

Inquiry Closed By User

nvarchar(50)

NULL allowed

Reference

Reference Notes

nvarchar(200)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerInquiries

CustomerInquiryID

True

PAGE

IX_CustomerInquiries_CustomerID

CustomerID

PAGE

[dbo].[CustomerInquiryCategories]¶

Reporting > Tables > dbo.CustomerInquiryCategories

Author: Jacob Golden

Page 83 of 249

MS_Description

Customer Inquiry Category Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerInquiryCategoryID

Unique ID

int

NOT NULL

CustomerInquiryCategoryDescription

Description

nvarchar(100)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerInquiryCategories

CustomerInquiry-

CategoryID

True

PAGE

[dbo].[CustomerInquiryStatuses]¶

Reporting > Tables > dbo.CustomerInquiryStatuses

Author: Jacob Golden

Page 84 of 249

MS_Description

Customer Inquiry Status Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerInquiryStatusID

Unique ID

int

NOT NULL

CustomerInquiryStatusDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerInquiryStatuses

CustomerInquiryStatusID

True

PAGE

[dbo].[CustomerInquiryTypes]¶

Reporting > Tables > dbo.CustomerInquiryTypes

Author: Jacob Golden

Page 85 of 249

MS_Description

Customer Inquiry Type Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerInquiryTypeID

Unique ID

int

NOT NULL

CustomerInquiryTypeDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerInquiryTypes

CustomerInquiryTypeID

True

PAGE

[dbo].[CustomerLeads]¶

Reporting > Tables > dbo.CustomerLeads

Author: Jacob Golden

Page 86 of 249

MS_Description

Customer Sales Leads

Columns

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerLeadID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

FirstName

First Name

nvarchar(50)

NOT NULL

LastName

Last Name

nvarchar(50)

NOT NULL

Company

Company

nvarchar(50)

NOT NULL

Email

Email

nvarchar(50)

NOT NULL

Phone

Phone

nvarchar(50)

NOT NULL

Phone2

Phone 2

nvarchar(50)

NOT NULL

MobilePhone

Mobile Phone

nvarchar(50)

NOT NULL

Fax

Fax

nvarchar(50)

NOT NULL

Address1

Address Field 1

nvarchar(50)

NOT NULL

Address2

Address Field 2

nvarchar(50)

NOT NULL

City

City

nvarchar(50)

NOT NULL

State

State

nvarchar(50)

NOT NULL

Zip

Zip

nvarchar(50)

NOT NULL

Country

Country

nvarchar(50)

NOT NULL

BirthDate

DOB

datetime

NULL allowed

Notes

Notes

nvarchar(2000)

4000

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Indexes¶

Reporting > Tables > dbo.CustomerLeads

Author: Jacob Golden

Page 87 of 249

Key

Name

Key Columns

Unique

Compression

PK_CustomerLeads

CustomerLeadID

True

PAGE

IX_CustomerLeads_CustomerID

CustomerID

PAGE

[dbo].[CustomerOverrides]¶

Reporting > Tables > dbo.CustomerOverrides

Author: Jacob Golden

Page 88 of 249

MS_Description

Commissions Overrides Per Customer

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

PlanID

Plan ID - Map to dbo.CommissionRuns

int

NOT NULL

OverrideID

Override ID - Map to dbo.CommissionOverrides

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

Qualifies

Qualifies? Boolean

bit

NOT NULL

PeriodTypeID

Period Type ID - Map to dbo.PeriodTypes

int

NULL allowed

StartPeriodID

Period ID When Override Starts To Apply

int

NULL allowed

EndPeriodID

Period ID When Override Ends

int

NULL allowed

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Amount

Override Amount

money

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerOverrides

PlanID, OverrideID,

CustomerID

True

PAGE

[dbo].[CustomerPayoutSettings]¶

Reporting > Tables > dbo.CustomerPayoutSettings

Author: Jacob Golden

Page 89 of 249

MS_Description

Customer Payout Settings

Columns

Indexes

Key

Name

Data Type

Max Length

(Bytes)

Nullability

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

Produce1099

Produce 1099? Boolean

bit

NOT NULL

TaxAddressTypeID

Tax Address Type ID - Map to dbo.TaxAddressTypes

int

NOT NULL

TaxNameTypeID

Tax Name Type ID - Map to dbo.TaxNameTypes

int

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

ModifiedBy

Record Last Modified By User

nvarchar(30)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerPayoutSettings

CustomerID

True

PAGE

[dbo].[CustomerPointAccounts]¶

Reporting > Tables > dbo.CustomerPointAccounts

Author: Jacob Golden

Page 90 of 249

MS_Description

Customer Point Accounts

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

PointAccountID

Point Account ID - Map to dbo.PointAccounts

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

PointBalance

Point Balance Amount

money

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerPointAccounts

PointAccountID, Customer-

ID

True

PAGE

[dbo].[CustomerRankChangeLog]¶

Reporting > Tables > dbo.CustomerRankChangeLog

Author: Jacob Golden

Page 91 of 249

MS_Description

Customer Rank Change History

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerRankChangeLogID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

RankID

Rank ID - Map to dbo.Ranks

int

NULL allowed

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerRankChangeLog

CustomerRank-

ChangeLogID

True

PAGE

IX_CustomerRankChangeLog_CustomerID

CustomerID

PAGE

[dbo].[Customers]¶

Reporting > Tables > dbo.Customers

Author: Jacob Golden

Page 92 of 249

MS_Description

Customers

Columns

Key

Name

Data Type

Max Length

(Bytes)

Nullability

CustomerID

Unique ID

int

NOT NULL

FirstName

First Name

nvarchar(50)

NOT NULL

MiddleName

Middle Name

nvarchar(50)

NOT NULL

LastName

Last Name

nvarchar(50)

NOT NULL

NameSuffix

Name Suffix

nvarchar(50)

NOT NULL

Company

Company (Optional)

nvarchar(100)

NOT NULL

CustomerTypeID

Customer Type ID - Map to dbo.CustomerTypes

int

NOT NULL

CustomerStatusID

Customer Status ID - Map to dbo.CustomerStatuses

int

NOT NULL

Email

Email

nvarchar(50)

NOT NULL

Phone

Phone

nvarchar(20)

NOT NULL

Phone2

Phone 2

nvarchar(20)

NOT NULL

MobilePhone

Mobile Phone

nvarchar(20)

NOT NULL

Fax

Fax

nvarchar(20)

NOT NULL

MainAddress1

Main Address Line 1

nvarchar(100)

NOT NULL

MainAddress2

Main Address Line 2

nvarchar(100)

NULL allowed

MainAddress3

Main Address Line 3

nvarchar(100)

NOT NULL

MainCity

Main City

nvarchar(50)

NOT NULL

MainState

Main State

nvarchar(50)

NOT NULL

MainZip

Main Zip

nvarchar(50)

NOT NULL

Section 93¶

Reporting > Tables > dbo.Customers

Author: Jacob Golden

Page 93 of 249

MainCountry

Main Country

nvarchar(50)

NOT NULL

MainCounty

Main County

nvarchar(50)

NOT NULL

MainVerified

Main Verified? Boolean

bit

NOT NULL

MailAddress1

Mail Address Line 1

nvarchar(100)

NOT NULL

MailAddress2

Mail Address Line 2

nvarchar(100)

NULL allowed

MailAddress3

Mail Address Line 3

nvarchar(100)

NOT NULL

MailCity

Mail City

nvarchar(50)

NOT NULL

MailState

Mail State

nvarchar(50)

NOT NULL

MailZip

Mail Zip

nvarchar(50)

NOT NULL

MailCountry

Mail Country

nvarchar(50)

NOT NULL

MailCounty

Mail County

nvarchar(50)

NOT NULL

MailVerified

Mail Verified? Boolean

bit

NOT NULL

OtherAddress1

Other Address Line 1

nvarchar(100)

NOT NULL

OtherAddress2

Other Address Line 2

nvarchar(100)

NULL allowed

OtherAddress3

Other Address Line 3

nvarchar(100)

NOT NULL

OtherCity

Other City

nvarchar(50)

NOT NULL

OtherState

Other State

nvarchar(50)

NOT NULL

OtherZip

Other Zip

nvarchar(50)

NOT NULL

OtherCountry

Other Country

nvarchar(50)

NOT NULL

OtherCounty

Other County

nvarchar(50)

NOT NULL

OtherVerified

Other Verified? Boolean

bit

NOT NULL

CanLogin

Can Login? Boolean

bit

NOT NULL

LoginName

Login Name

nvarchar(100)

NULL allowed

PasswordHash

Password Hash - Hashed version of the customer password

varbinary(50)

NULL allowed

RankID

Highest Ever Rank ID as of Last Accepted Period - Map to

dbo.Ranks

int

NULL allowed

Section 94¶

Reporting > Tables > dbo.Customers

Author: Jacob Golden

Page 94 of 249

EnrollerID

Current Enroller

int

NULL allowed

SponsorID

Current Sponsor

int

NULL allowed

BirthDate

DOB

datetime

NULL allowed

CurrencyCode

Currency Code Used For Payouts

nvarchar(3)

NOT NULL

PayableToName

Name Used For Payouts

nvarchar(50)

NOT NULL

DefaultWarehouseID

Default Warehouse ID - Map to dbo.Warehouses

int

NULL allowed

PayableTypeID

Payable Type ID - Map to dbo.PayoutTypes

int

NOT NULL

CheckThreshold

Minimum Amount To Initiate a Payout

money

NOT NULL

LanguageID

Language ID - Map to dbo.Languages

int

NULL allowed

Gender

Gender (M/F)

nvarchar(1)

NOT NULL

TaxCode

Tax Code (SSN/TaxID/etc.) - Masked

nvarchar(50)

NULL allowed

TaxCodeTypeID

Tax Code Type ID - Map to dbo.TaxCodeTypes

int

NOT NULL

IsSalesTaxExempt

Is Sales Tax Exempt? Boolean

bit

NOT NULL

SalesTaxCode

Sales Tax Code - Masked

nvarchar(50)

NULL allowed

SalesTaxExemptExpireDate

Sales Tax Exemption Expire Date

datetime

NULL allowed

VatRegistration

VAT Registration

nvarchar(50)

NOT NULL

BinaryPlacementTypeID

Binary Placement Type ID - Map to dbo.BinaryPlacement-

Types

int

NOT NULL

UseBinaryHoldingTank

Use Binary Holding Tank? Boolean

bit

NOT NULL

IsInBinaryHoldingTank

Is In Binary Holding Tank? Boolean

bit

NOT NULL

IsEmailSubscribed

Is Email Subscribed? Boolean

bit

NULL allowed

EmailSubscribeIP

Email Subscribe IP Address

nvarchar(50)

NULL allowed

IsSMSSubscribed

Is SMS Subscribed? Boolean

bit

NULL allowed

Notes

Notes

nvarchar(max)

max

NULL allowed

Field1

Custom Field - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field2

Custom Field - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field3

nvarchar(100)

NOT NULL

Section 95¶

Reporting > Tables > dbo.Customers

Author: Jacob Golden

Page 95 of 249

Custom Field - Map to dbo.CustomerFieldNames

Field4

Custom Field - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field5

Custom Field - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field6

Custom Field - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field7

Custom Field - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field8

Custom Field - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field9

Custom Field - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field10

Custom Field - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field11

Custom Field - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field12

Custom Field - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field13

Custom Field - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field14

Custom Field - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field15

Custom Field - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Date1

Custom Field - Map to dbo.CustomerFieldNames

datetime

NULL allowed

Date2

Custom Field - Map to dbo.CustomerFieldNames

datetime

NULL allowed

Date3

Custom Field - Map to dbo.CustomerFieldNames

datetime

NULL allowed

Date4

Custom Field - Map to dbo.CustomerFieldNames

datetime

NULL allowed

Date5

Custom Field - Map to dbo.CustomerFieldNames

datetime

NULL allowed

CreatedDate

Record Created Date

datetime

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

CreatedBy

Record Created By User

nvarchar(30)

NOT NULL

ModifiedBy

Record Last Modified By User

nvarchar(30)

NOT NULL

EmailUnsubscribeDate

Email Unsubscribe Date

datetime

NULL allowed

EmailSubscribeDate

Email Subscribe Date

datetime

NULL allowed

SMSSubscribeDate

SMS Subscribe Date

datetime

NULL allowed

SMSUnsubscribeDate

SMS Unsubscribe Date

datetime

NULL allowed

Indexes¶

Reporting > Tables > dbo.Customers

Author: Jacob Golden

Page 96 of 249

Used By

[dbo].[AuthenticateCustomer]

[queue].[Refresh]

TerminationReason

Termination Reason ID - Map to dbo.CustomerTermination-

Reasons

int

NULL allowed

CustomerKey

Customer Key (Typically Blank Unless Used by Client)

nvarchar(50)

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_Customers

CustomerID

True

PAGE

IX_Customers_CustomerStatusID

CustomerStatusID

PAGE

IX_Customers_CustomerTypeID

CustomerTypeID

PAGE

IX_Customers_EnrollerID

EnrollerID

PAGE

IX_Customers_LoginName

LoginName

PAGE

[dbo].[CustomerSiteChangeLog]¶

Reporting > Tables > dbo.CustomerSiteChangeLog

Author: Jacob Golden

Page 97 of 249

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerSiteChangeLogID

int

NOT NULL

CustomerID

int

NOT NULL

ModifiedDate

datetime

NOT NULL

ModifiedBy

nvarchar(50)

NOT NULL

Detail

nvarchar(max)

max

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerSiteChangeLog

CustomerSite-

ChangeLogID

True

PAGE

IX_CustomerSiteChangeLog_CustomerID

CustomerID

PAGE

[dbo].[CustomerSites]¶

Reporting > Tables > dbo.CustomerSites

Author: Jacob Golden

Page 98 of 249

MS_Description

Replicated Site Population Data

Columns

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

WebAlias

Web Alias Name

nvarchar(100)

NOT NULL

FirstName

First Name

nvarchar(100)

NOT NULL

LastName

Last Name

nvarchar(100)

NOT NULL

Company

Company (Optional)

nvarchar(255)

NOT NULL

Email

Email

nvarchar(100)

NOT NULL

Phone

Phone

nvarchar(100)

NOT NULL

Phone2

Phone 2

nvarchar(100)

NOT NULL

Fax

Fax

nvarchar(100)

NOT NULL

Address1

Address Line 1

nvarchar(200)

NOT NULL

Address2

Address Line 2

nvarchar(200)

NOT NULL

City

City

nvarchar(100)

NOT NULL

State

State

nvarchar(100)

NOT NULL

Zip

Zip

nvarchar(100)

NOT NULL

Country

Country

nvarchar(50)

NOT NULL

Notes1

Notes 1

nvarchar(max)

max

NOT NULL

Notes2

Notes 2

nvarchar(max)

max

NOT NULL

Notes3

Notes 3

nvarchar(max)

max

NOT NULL

Notes4

Notes 4

nvarchar(max)

max

NOT NULL

Url1

nvarchar(200)

NOT NULL

Section 99¶

Reporting > Tables > dbo.CustomerSites

Author: Jacob Golden

Page 99 of 249

URL 1

Url2

URL 2

nvarchar(200)

NOT NULL

Url3

URL 3

nvarchar(200)

NOT NULL

Url4

URL 4

nvarchar(200)

NOT NULL

Url5

URL 5

nvarchar(200)

NOT NULL

Url6

URL 6

nvarchar(200)

NOT NULL

Url7

URL 7

nvarchar(200)

NOT NULL

Url8

URL 8

nvarchar(200)

NOT NULL

Url9

URL 9

nvarchar(200)

NOT NULL

Url10

URL 10

nvarchar(200)

NOT NULL

Url1Description

URL 1 Description

nvarchar(200)

NOT NULL

Url2Description

URL 2 Description

nvarchar(200)

NOT NULL

Url3Description

URL 3 Description

nvarchar(200)

NOT NULL

Url4Description

URL 4 Description

nvarchar(200)

NOT NULL

Url5Description

URL 5 Description

nvarchar(200)

NOT NULL

Url6Description

URL 6 Description

nvarchar(200)

NOT NULL

Url7Description

URL 7 Description

nvarchar(200)

NOT NULL

Url8Description

URL 8 Description

nvarchar(200)

NOT NULL

Url9Description

URL 9 Description

nvarchar(200)

NOT NULL

Url10Description

URL 10 Description

nvarchar(200)

NOT NULL

DataImage1

Data Image 1

varbinary(max)

max

NULL allowed

DataImageType1

Data Image Type 1

nvarchar(200)

NULL allowed

DataImage2

Data Image 2

varbinary(max)

max

NULL allowed

DataImageType2

Data Image Type 2

nvarchar(200)

NULL allowed

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Indexes¶

Reporting > Tables > dbo.CustomerSites

Author: Jacob Golden

Page 100 of 249

Used By

[queue].[Refresh]

Key

Name

Key Columns

Unique

Compression

PK_CustomerSites

CustomerID

True

PAGE

IX_CustomerSites_WebAlias

WebAlias

PAGE

[dbo].[CustomerSocialNetworks]¶

Reporting > Tables > dbo.CustomerSocialNetworks

Author: Jacob Golden

Page 101 of 249

MS_Description

Customer Social Networks

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

SocialNetworkID

Social Network ID - Map to dbo.SolcialNetworks

int

NOT NULL

Url

URL or Handle

nvarchar(500)

1000

NULL allowed

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerSocialNetworks

CustomerID, Social-

NetworkID

True

PAGE

[dbo].[CustomerStatusChangeLog]¶

Reporting > Tables > dbo.CustomerStatusChangeLog

Author: Jacob Golden

Page 102 of 249

MS_Description

History of Customer Status Changes

Columns

Indexes

Key

Name

Data Type

Max Length

(Bytes)

Nullability

CustomerStatusChangeLogID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

CustomerStatusID

Customer Status ID - Map to dbo.CustomerStatuses

int

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

ModifiedBy

Record Last Modified By User

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerStatusChangeLog

CustomerStatus-

ChangeLogID

True

PAGE

IX_CustomerStatusChangeLog_CustomerID

CustomerID

PAGE

[dbo].[CustomerStatuses]¶

Reporting > Tables > dbo.CustomerStatuses

Author: Jacob Golden

Page 103 of 249

MS_Description

Customer Status Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerStatusID

Unique ID

int

NOT NULL

CustomerStatusDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerStatuses

CustomerStatusID

True

PAGE

[dbo].[CustomerSubscriptions]¶

Reporting > Tables > dbo.CustomerSubscriptions

Author: Jacob Golden

Page 104 of 249

MS_Description

Customer Subscriptions

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

SubscriptionID

Subscription ID - Map to dbo.Subscriptions

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

IsActive

Is Active? Boolean

bit

NOT NULL

StartDate

Subscription Start Date

datetime

NOT NULL

ExpireDate

Subscription Expiration Date

datetime

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerSubscriptions

SubscriptionID, Customer-

ID

True

PAGE

[dbo].[CustomerTempAuthRequests]¶

Reporting > Tables > dbo.CustomerTempAuthRequests

Author: Jacob Golden

Page 105 of 249

MS_Description

Local Authentication Caching Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerTempAuthRequestId

Unique ID

int

NOT NULL

CustomerId

Customer ID - Map to dbo.Customers

int

NOT NULL

Token

Token

varbinary(255)

NOT NULL

RequestBy

Request By User ID

int

NOT NULL

RequestDate

Request Date

datetime

NOT NULL

RequestClientIp

Requesting Client IP Address

nvarchar(15)

NULL allowed

RequestSessionId

Request Session ID

uniqueidentifier

NULL allowed

ClaimDate

Claim Date

datetime

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_CustomerTempAuthRequests

CustomerTempAuth-

RequestId

True

PAGE

[dbo].[CustomerTerminationReasons]¶

Reporting > Tables > dbo.CustomerTerminationReasons

Author: Jacob Golden

Page 106 of 249

MS_Description

Customer Termination Reasons Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

ReasonID

Unique ID

int

NOT NULL

Reason

Reason Text

nvarchar(256)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerTerminationReasons

ReasonID

True

PAGE

[dbo].[CustomerTransactionTypes]¶

Reporting > Tables > dbo.CustomerTransactionTypes

Author: Jacob Golden

Page 107 of 249

MS_Description

Customer Transaction Types Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerTransactionTypeID

Unique ID

int

NOT NULL

CustomerTransactionTypeDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerTransactionTypes

CustomerTransaction-

TypeID

True

PAGE

[dbo].[CustomerTypeChangeLog]¶

Reporting > Tables > dbo.CustomerTypeChangeLog

Author: Jacob Golden

Page 108 of 249

MS_Description

History of Customer Type Changes

Columns

Indexes

Key

Name

Data Type

Max Length

(Bytes)

Nullability

CustomerTypeChangeLogID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

CustomerTypeID

Customer Type ID - Map to dbo.CustomerTypes

int

NOT NULL

PrevousCustomerTypeID

Previous Customer Type ID - Map to dbo.CustomerTypes

int

NULL allowed

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

ModifiedBy

Record Last Modified By User

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerTypeChangeLog

CustomerType-

ChangeLogID

True

PAGE

IX_CustomerTypeChangeLog_CustomerID

CustomerID

PAGE

[dbo].[CustomerTypes]¶

Reporting > Tables > dbo.CustomerTypes

Author: Jacob Golden

Page 109 of 249

MS_Description

Customer Types Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerTypeID

Unique ID

int

NOT NULL

CustomerTypeDescription

Description

nvarchar(50)

NOT NULL

PriceTypeID

Price Type ID - Map to dbo.PriceTypes

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_CustomerTypes

CustomerTypeID

True

PAGE

[dbo].[CustomerWall]¶

Reporting > Tables > dbo.CustomerWall

Author: Jacob Golden

Page 110 of 249

MS_Description

Downline Events

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerWallItemID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

EntryDate

Record Create Date

datetime

NOT NULL

Text

Event Detail

nvarchar(2000)

4000

NOT NULL

Field1

Custom Field 1

nvarchar(50)

NULL allowed

Field2

Custom Field 2

nvarchar(50)

NULL allowed

Field3

Custom Field 3

nvarchar(50)

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_CustomerWall

CustomerWallItemID

True

PAGE

IX_CustomerWall_CustomerID

CustomerID

PAGE

[dbo].[Departments]¶

Reporting > Tables > dbo.Departments

Author: Jacob Golden

Page 111 of 249

MS_Description

User Departments

Columns

Name

Data Type

Max Length (Bytes)

Nullability

DepartmentID

Unique ID

int

NOT NULL

DepartmentDescription

Description

nvarchar(50)

NOT NULL

[dbo].[EmailOutLog]¶

Reporting > Tables > dbo.EmailOutLog

Author: Jacob Golden

Page 112 of 249

MS_Description

Email Sent Logs

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

OutMailID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NULL allowed

SubmitDate

Email Submit Date

datetime

NOT NULL

MailTo

Email To Email Address

nvarchar(200)

NOT NULL

Subject

Email Subject

nvarchar(200)

NOT NULL

BroadCastID

Broadcast ID - Map to dbo.Broadcasts

int

NULL allowed

DeliveryStatus

Delivery Status

nvarchar(500)

1000

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_EmailOutLog

OutMailID

True

PAGE

IX_EmailOutLog_CustomerID

CustomerID

PAGE

[dbo].[EnrollerTree]¶

Reporting > Tables > dbo.EnrollerTree

Author: Jacob Golden

Page 113 of 249

MS_Description

Enroller Tree (Memory Optimized Table)

Columns

Indexes

Used By

[dbo].[EnrollerDownline]

[dbo].[EnrollerUpline]

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

EnrollerID

Enroller ID - Map to dbo.Customers

int

NOT NULL

NestedLevel

Nested Level

int

NOT NULL

Lft

Index Left Boundary

int

NOT NULL

Rgt

Index Right Boundary

int

NOT NULL

Key

Name

Key Columns

Unique

PK_EnrollerTree

CustomerID

True

[dbo].[EnrollerTreeHistory]¶

Reporting > Tables > dbo.EnrollerTreeHistory

Author: Jacob Golden

Page 114 of 249

MS_Description

History of Changes in Enroller Tree

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

EnrollerTreeHistoryID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

MoveDate

Date of Tree Move

datetime

NOT NULL

PreviousEnrollerID

Previous Enroller ID - Map to dbo.Customers

int

NOT NULL

PreviousNestedLevel

Previous Nested Level

int

NOT NULL

NewEnrollerID

New Enroller ID - Map to dbo.Customers

int

NOT NULL

NewNestedLevel

New Nested Level

int

NOT NULL

Reason

Reason for Move

nvarchar(1000)

2000

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_EnrollerTreeHistory

EnrollerTreeHistoryID,

CustomerID

True

PAGE

[dbo].[ExpectedPaymentFieldNames]¶

Reporting > Tables > dbo.ExpectedPaymentFieldNames

Author: Jacob Golden

Page 115 of 249

MS_Description

Expected Payment Field Names

Columns

Name

Data Type

Max Length (Bytes)

Nullability

BillingName

Billing Name

nvarchar(200)

NULL allowed

BankName

Bank Name

nvarchar(200)

NULL allowed

Memo

Memo

nvarchar(200)

NULL allowed

[dbo].[ExpectedPayments]¶

Reporting > Tables > dbo.ExpectedPayments

Author: Jacob Golden

Page 116 of 249

MS_Description

Expected Payments

Columns

Key

Name

Data Type

Max Length

(Bytes)

Nullability

ExpectedPaymentID

Unique ID

int

NOT NULL

OrderID

Order ID - Map to dbo.Orders

int

NULL allowed

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

PaymentType

Payment Type - Map to dbo.PaymentTypes

int

NOT NULL

PaymentDate

Payment Date

datetime

NOT NULL

Amount

Amount in Currency Code

money

NOT NULL

CurrencyCode

Currency Code - Map to dbo.Currencies

nvarchar(3)

NOT NULL

CreditNumber

Credit Number

nvarchar(50)

NULL allowed

CreditIssue

nvarchar(10)

NULL allowed

CreditExpiration

Credit Expiration

datetime

NULL allowed

CreditAuthorization

Credit Authorization

nvarchar(50)

NULL allowed

CheckNumber

Check Number

nvarchar(50)

NULL allowed

CheckAccountNumber

Check Account Number

nvarchar(50)

NULL allowed

CheckRoutingNumber

Check Routing Number

nvarchar(50)

NULL allowed

CheckDate

Check Date

datetime

NULL allowed

BillingName

Billing Name

nvarchar(50)

NULL allowed

BillingAddress1

Billing Address Line 1

nvarchar(50)

NULL allowed

BillingAddress2

Billing Address Line 2

nvarchar(50)

NULL allowed

BillingCity

Billing City

nvarchar(40)

NULL allowed

Indexes¶

Reporting > Tables > dbo.ExpectedPayments

Author: Jacob Golden

Page 117 of 249

BillingState

Billing State

nvarchar(10)

NULL allowed

BillingZip

Billing Zip

nvarchar(20)

NULL allowed

BillingCountry

Billing Country

nvarchar(2)

NULL allowed

BankName

Bank Name

nvarchar(50)

NULL allowed

BankAddress

Bank Address

nvarchar(200)

NULL allowed

BankCity

Bank City

nvarchar(40)

NULL allowed

BankState

Bank State

nvarchar(10)

NULL allowed

BankZip

Bank Zip

nvarchar(20)

NULL allowed

ExpectedPaymentStatusTy

Expected Payment Status Type - Map to dbo.Expected-

PaymentStatusTypes

int

NOT NULL

Memo

Memo

nvarchar(500)

1000

NULL allowed

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ExpectedPayments

ExpectedPaymentID

True

PAGE

[dbo].[ExpectedPaymentStatusTypes]¶

Reporting > Tables > dbo.ExpectedPaymentStatusTypes

Author: Jacob Golden

Page 118 of 249

MS_Description

Expected Payment Status Types

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

ExpectedPaymentStatusTypeID

Unique ID

int

NOT NULL

Description

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ExpectedPaymentStatusTypes

ExpectedPayment-

StatusTypeID

True

PAGE

[dbo].[ExpectedRetailPayments]¶

Reporting > Tables > dbo.ExpectedRetailPayments

Author: Jacob Golden

Page 119 of 249

MS_Description

Expected Retail Payments

Columns

Key

Name

Data Type

Max Length (Bytes)

Nullability

RetailOrderID

Order ID - Map to dbo.Orders

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

PaymentType

Payment Type - Map to dbo.PaymentTypes

int

NOT NULL

PaymentDate

Payment Date

datetime

NOT NULL

Amount

Amount in Currency Code

money

NOT NULL

CurrencyCode

Currency Code - Map to dbo.Currencies

nvarchar(3)

NOT NULL

CreditCardDescription

Credit Card Description

nvarchar(50)

NULL allowed

CreditCardNumber

Credit Card Number

nvarchar(50)

NULL allowed

CreditCardExpiration

Credit Card Expiration

datetime

NULL allowed

CreditCardAuthorization

Credit Card Authorization

nvarchar(50)

NULL allowed

CheckNumber

Check Number

nvarchar(50)

NULL allowed

CheckAccountNumber

Check Account Number

nvarchar(50)

NULL allowed

CheckRoutingNumber

Check Routing Number

nvarchar(50)

NULL allowed

CheckDate

Check Date

datetime

NULL allowed

BillingName

Billing Name

nvarchar(50)

NULL allowed

BillingAddress1

Billing Address Line 1

nvarchar(50)

NULL allowed

BillingAddress2

Billing Address Line 2

nvarchar(50)

NULL allowed

BillingCity

Billing City

nvarchar(40)

NULL allowed

BillingState

Billing State

nvarchar(10)

NULL allowed

BillingZip

nvarchar(20)

NULL allowed

Indexes¶

Reporting > Tables > dbo.ExpectedRetailPayments

Author: Jacob Golden

Page 120 of 249

Billing Zip

BillingCountry

Billing Country

nvarchar(20)

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_ExpectedRetailPayments

RetailOrderID

True

PAGE

[dbo].[FrequencyTypes]¶

Reporting > Tables > dbo.FrequencyTypes

Author: Jacob Golden

Page 121 of 249

MS_Description

Frequency Types

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

FrequencyTypeID

Unique ID

int

NOT NULL

FrequencyTypeDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_FrequencyTypes

FrequencyTypeID

True

PAGE

[dbo].[Guests]¶

Reporting > Tables > dbo.Guests

Author: Jacob Golden

Page 122 of 249

MS_Description

Party Guests

Columns

Key

Name

Data Type

Max Length

(Bytes)

Nullability

GuestID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NULL allowed

HostID

Host ID - Map to dbo.Customers.CustomerID

int

NOT NULL

FirstName

Guest First Name

nvarchar(50)

NOT NULL

MiddleName

Guest Middle Name

nvarchar(50)

NOT NULL

LastName

Guest Last Name

nvarchar(50)

NOT NULL

NameSuffix

Guest Name Suffix

nvarchar(50)

NOT NULL

Company

Guest Company (Optional)

nvarchar(50)

NOT NULL

Gender

Guest Gender (M/F)

nvarchar(1)

NOT NULL

GuestStatusTypeID

Guest Status Type ID - Map to dbo.GuestStatusTypes

int

NOT NULL

LanguageID

Guest Language ID - Map to dbo.Languages

int

NOT NULL

Address1

Guest Address Line 1

nvarchar(100)

NOT NULL

Address2

Guest Address Line 2

nvarchar(100)

NOT NULL

Address3

Guest Address Line 3

nvarchar(100)

NOT NULL

City

Guest City

nvarchar(50)

NOT NULL

County

Guest County

nvarchar(50)

NOT NULL

State

Guest State

nvarchar(50)

NOT NULL

Zip

Guest Zip

nvarchar(50)

NOT NULL

Country

Guest Country

nvarchar(50)

NOT NULL

Section 123¶

Reporting > Tables > dbo.Guests

Author: Jacob Golden

Page 123 of 249

Phone

Guest Phone

nvarchar(20)

NOT NULL

Phone2

Guest Phone 2

nvarchar(20)

NOT NULL

MobilePhone

Guest Mobile Phone

nvarchar(20)

NOT NULL

Email

Guest Email

nvarchar(50)

NOT NULL

Field1

Guest Customer Field 1 - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field2

Guest Customer Field 2 - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field3

Guest Customer Field 3 - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field4

Guest Customer Field 4 - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field5

Guest Customer Field 5 - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field6

Guest Customer Field 6 - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field7

Guest Customer Field 7 - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field8

Guest Customer Field 8 - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field9

Guest Customer Field 9 - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field10

Guest Customer Field 10 - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field11

Guest Customer Field 11 - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field12

Guest Customer Field 12 - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field13

Guest Customer Field 13 - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field14

Guest Customer Field 14 - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Field15

Guest Customer Field 15 - Map to dbo.CustomerFieldNames

nvarchar(100)

NOT NULL

Date1

Guest Customer Date 1 - Map to dbo.CustomerFieldNames

datetime

NULL allowed

Date2

Guest Customer Date 2 - Map to dbo.CustomerFieldNames

datetime

NULL allowed

Date3

Guest Customer Date 3 - Map to dbo.CustomerFieldNames

datetime

NULL allowed

Date4

Guest Customer Date 4 - Map to dbo.CustomerFieldNames

datetime

NULL allowed

Date5

Guest Customer Date 5 - Map to dbo.CustomerFieldNames

datetime

NULL allowed

Notes

Guest Notes

nvarchar(max)

max

NULL allowed

EntryDate

Record Create Date

datetime

NOT NULL

Indexes¶

Reporting > Tables > dbo.Guests

Author: Jacob Golden

Page 124 of 249

CreatedBy

Record Created By User

nvarchar(30)

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

ModifiedBy

Record Last Modified By User

nvarchar(30)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_Guests

GuestID

True

PAGE

IX_Guests_CustomerID

CustomerID

PAGE

IX_Guests_HostID

HostID

PAGE

[dbo].[GuestStatusTypes]¶

Reporting > Tables > dbo.GuestStatusTypes

Author: Jacob Golden

Page 125 of 249

MS_Description

Guest Status Types

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

GuestStatusTypeID

Unique ID

int

NOT NULL

GuestStatusTypeDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_GuestStatusTypes

GuestStatusTypeID

True

PAGE

[dbo].[ImageFiles]¶

Reporting > Tables > dbo.ImageFiles

Author: Jacob Golden

Page 126 of 249

MS_Description

Image Files (Binary)

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

Path

File Path

nvarchar(700)

1400

NOT NULL

Name

Image Name

nvarchar(100)

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Size

Image Size (KB)

int

NOT NULL

ImageData

Image Binary Data

varbinary(max)

max

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ImageFiles

Path, Name

True

PAGE

[dbo].[ItemChangeLog]¶

Reporting > Tables > dbo.ItemChangeLog

Author: Jacob Golden

Page 127 of 249

MS_Description

History of Item Changes

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

ItemChangeLogID

Unique ID

int

NOT NULL

ItemID

Item ID - Map to dbo.Items

int

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

ModifiedBy

Record Last Modified By User

nvarchar(50)

NOT NULL

Detail

Detail of From and To

nvarchar(max)

max

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ItemChangeLog

ItemChangeLogID

True

PAGE

IX_ItemChangeLog_ItemID

ItemID

PAGE

[dbo].[ItemCountryRegions]¶

Reporting > Tables > dbo.ItemCountryRegions

Author: Jacob Golden

Page 128 of 249

MS_Description

Tax Override by Item Per Country and Region

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

ItemID

Item ID - Map to dbo.Items

int

NOT NULL

CountryCode

Country Code - Map to dbo.Countries

nvarchar(2)

NOT NULL

RegionCode

Region Code - Map to dbo.CountryRegions

nvarchar(3)

NOT NULL

UseTaxOverride

Use Tax Override? Boolean

bit

NOT NULL

TaxOverridePercent

Tax Override Percent

money

NOT NULL

Taxed

Taxed? Boolean

int

NOT NULL

TaxedFederal

Taxed Federal? Boolean

int

NOT NULL

TaxedState

Taxed State? Boolean

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ItemCountryRegions

ItemID, CountryCode,

RegionCode

True

PAGE

[dbo].[ItemDynamicKitCategories]¶

Reporting > Tables > dbo.ItemDynamicKitCategories

Author: Jacob Golden

Page 129 of 249

MS_Description

Categories for Dynamic Item Kits

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

DynamicKitCategoryID

Unique ID

int

NOT NULL

DynamicKitCategoryDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ItemDynamicKitCategories

DynamicKitCategoryID

True

PAGE

[dbo].[ItemDynamicKitCategoryItemMembers]¶

Reporting > Tables > dbo.ItemDynamicKitCategoryItemMembers

Author: Jacob Golden

Page 130 of 249

MS_Description

Item Members in Dynamic Kit Categories

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

DynamicKitCategoryID

Dynamic Kit Category ID - Map to dbo.ItemDynamicKitCategories

int

NOT NULL

ItemID

Item ID - Map to dbo.Items

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ItemDynamicKitCategoryItemMembers

DynamicKitCategory-

ID, ItemID

True

PAGE

[dbo].[ItemDynamicKitCategoryMembers]¶

Reporting > Tables > dbo.ItemDynamicKitCategoryMembers

Author: Jacob Golden

Page 131 of 249

MS_Description

Layout of Items and Quantity in Dynamic Kit

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

MasterItemID

Master Item ID - Map to dbo.ItemGroupMembers

int

NOT NULL

DynamicKitCategoryID

Dynamic Kit Category ID - Map to dbo.ItemDynamicKitCategories

int

NOT NULL

Quantity

Quantity of Item in Kit

money

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ItemDynamicKitCategoryMembers

MasterItemID,

DynamicKitCategoryID

True

PAGE

[dbo].[ItemFieldNames]¶

Reporting > Tables > dbo.ItemFieldNames

Author: Jacob Golden

Page 132 of 249

MS_Description

Display Names for Customer Fields in dbo.Items Table

Columns

Name

Data Type

Max Length (Bytes)

Nullability

ShortDetailDisplayName

Custom Field Name

nvarchar(50)

NOT NULL

ShortDetail2DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

ShortDetail3DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

ShortDetail4DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

LongDetailDisplayName

Custom Field Name

nvarchar(50)

NOT NULL

LongDetail2DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

LongDetail3DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

LongDetail4DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

Field1DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

Field2DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

Field3DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

Field4DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

Field5DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

Field6DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

Field7DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

Field8DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

Field9DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

Field10DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

OtherCheck1DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

OtherCheck2DisplayName

nvarchar(50)

NOT NULL

Section 133¶

Reporting > Tables > dbo.ItemFieldNames

Author: Jacob Golden

Page 133 of 249

Custom Field Name

OtherCheck3DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

OtherCheck4DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

OtherCheck5DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

Auto1DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

Auto2DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

Auto3DisplayName

Custom Field Name

nvarchar(50)

NOT NULL

[dbo].[ItemGroupMembers]¶

Reporting > Tables > dbo.ItemGroupMembers

Author: Jacob Golden

Page 134 of 249

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

MasterItemID

int

NOT NULL

ItemID

int

NOT NULL

Priority

int

NOT NULL

GroupMemberDescription

nvarchar(100)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ItemGroupMembers

MasterItemID, ItemID

True

PAGE

[dbo].[ItemImages]¶

Reporting > Tables > dbo.ItemImages

Author: Jacob Golden

Page 135 of 249

MS_Description

Image Mapping for Items

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

ItemImageID

Unique ID

int

NOT NULL

ImageName

Image Name

nvarchar(500)

1000

NOT NULL

ImageData

Image Data (Binary)

varbinary(max)

max

NULL allowed

CompressionTypeID

Compression Type ID

int

NULL allowed

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ItemImages

ItemImageID

True

PAGE

IX_ItemImages_ImageName

ImageName

PAGE

[dbo].[ItemLanguages]¶

Reporting > Tables > dbo.ItemLanguages

Author: Jacob Golden

Page 136 of 249

MS_Description

Item Descriptions in Other Languages

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

ItemID

Item ID - Map to dbo.Items

int

NOT NULL

LanguageID

Language ID - Map to dbo.Languages

int

NOT NULL

ItemDescription

Item Description

nvarchar(255)

NOT NULL

ShortDetail

Short Detail

nvarchar(2048)

4096

NOT NULL

ShortDetail2

Short Detail 2

nvarchar(2048)

4096

NOT NULL

ShortDetail3

Short Detail 3

nvarchar(2048)

4096

NOT NULL

ShortDetail4

Short Detail 4

nvarchar(2048)

4096

NOT NULL

LongDetail

Long Detail

nvarchar(max)

max

NOT NULL

LongDetail2

Long Detail 2

nvarchar(max)

max

NOT NULL

LongDetail3

Long Detail 3

nvarchar(max)

max

NOT NULL

LongDetail4

Long Detail 4

nvarchar(max)

max

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ItemLanguages

ItemID, LanguageID

True

PAGE

[dbo].[ItemPointAccounts]¶

Reporting > Tables > dbo.ItemPointAccounts

Author: Jacob Golden

Page 137 of 249

MS_Description

Item Point Accounts

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

ItemID

Item ID - Map to dbo.Items

int

NOT NULL

PointAccountID

Point Account ID - Map to dbo.PointAccounts

int

NOT NULL

Increment

Increment

money

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ItemPointAccounts

ItemID, PointAccountID

True

PAGE

[dbo].[ItemPrices]¶

Reporting > Tables > dbo.ItemPrices

Author: Jacob Golden

Page 138 of 249

MS_Description

Item Prices

Columns

Key

Name

Data Type

Max Length (Bytes)

Nullability

ItemID

Item ID - Map to dbo.Items

int

NOT NULL

CurrencyCode

Currency Code - Map to dbo.Currencies

nvarchar(3)

NOT NULL

PriceTypeID

Price Type ID - Map to dbo.PriceTypes

int

NOT NULL

Price

Amount in Currency Code

money

NOT NULL

CommissionableVolume

Commissionable Volume

money

NOT NULL

BusinessVolume

Business Volume

money

NOT NULL

TaxablePrice

Taxable Amount

money

NOT NULL

ShippingPrice

Shipping Amount

money

NOT NULL

Other1Price

Customer Amount

money

NOT NULL

Other2Price

Customer Amount

money

NOT NULL

Other3Price

Customer Amount

money

NOT NULL

Other4Price

Customer Amount

money

NOT NULL

Other5Price

Customer Amount

money

NOT NULL

Other6Price

Customer Amount

money

NOT NULL

Other7Price

Customer Amount

money

NOT NULL

Other8Price

Customer Amount

money

NOT NULL

Other9Price

Customer Amount

money

NOT NULL

Other10Price

Customer Amount

money

NOT NULL

Indexes¶

Reporting > Tables > dbo.ItemPrices

Author: Jacob Golden

Page 139 of 249

Key

Name

Key Columns

Unique

Compression

PK_ItemPrices

ItemID, CurrencyCode, Price-

TypeID

True

PAGE

[dbo].[Items]¶

Reporting > Tables > dbo.Items

Author: Jacob Golden

Page 140 of 249

MS_Description

Items

Columns

Key

Name

Data Type

Max Length

(Bytes)

Nullability

ItemID

Unique ID

int

NOT NULL

ItemCode

Item Code

nvarchar(20)

NOT NULL

ItemDescription

Item Description

nvarchar(500)

1000

NOT NULL

ItemTypeID

Item Type ID - Map to dbo.ItemTypes

int

NOT NULL

ShortDetail

Short Detail

nvarchar(2048)

4096

NOT NULL

ShortDetail2

Short Detail 2

nvarchar(2048)

4096

NOT NULL

ShortDetail3

Short Detail 3

nvarchar(2048)

4096

NOT NULL

ShortDetail4

Short Detail 4

nvarchar(2048)

4096

NOT NULL

LongDetail

Long Detail

nvarchar(max)

max

NOT NULL

LongDetail2

Long Detail 2

nvarchar(max)

max

NOT NULL

LongDetail3

Long Detail 3

nvarchar(max)

max

NOT NULL

LongDetail4

Long Detail 4

nvarchar(max)

max

NOT NULL

Notes

Notes

nvarchar(2048)

4096

NOT NULL

Weight

Item Weight (Typically lbs, but is not defined)

money

NOT NULL

IsVirtual

Is Virtual Item? Boolean

bit

NOT NULL

IsGroupMaster

Is Group Master? Boolean

bit

NOT NULL

SuppressGroupMaster

Suppress Group Master? Boolean

bit

NOT NULL

GroupDescription

Group Description

nvarchar(500)

1000

NOT NULL

GroupMembersDescription

Group Members Description

nvarchar(500)

1000

NOT NULL

Section 141¶

Reporting > Tables > dbo.Items

Author: Jacob Golden

Page 141 of 249

AllowOnAutoOrder

Allow On Auto Order? Boolean

bit

NOT NULL

HideFromSearch

Hide From Search? Boolean

bit

NOT NULL

AvailableAllCountryRegions

Available in All Countries and Regions? Boolean

bit

NOT NULL

TinyImageName

Tiny Image Name

nvarchar(255)

NOT NULL

SmallImageName

Small Image Name

nvarchar(255)

NOT NULL

LargeImageName

Large Image Name

nvarchar(255)

NOT NULL

Field1

Custom Field

nvarchar(100)

NOT NULL

Field2

Custom Field

nvarchar(100)

NOT NULL

Field3

Custom Field

nvarchar(100)

NOT NULL

Field4

Custom Field

nvarchar(100)

NOT NULL

Field5

Custom Field

nvarchar(100)

NOT NULL

Field6

Custom Field

nvarchar(100)

NOT NULL

Field7

Custom Field

nvarchar(100)

NOT NULL

Field8

Custom Field

nvarchar(100)

NOT NULL

Field9

Custom Field

nvarchar(100)

NOT NULL

Field10

Custom Field

nvarchar(100)

NULL allowed

OtherCheck1

Custom Field

bit

NOT NULL

OtherCheck2

Custom Field

bit

NOT NULL

OtherCheck3

Custom Field

bit

NOT NULL

OtherCheck4

Custom Field

bit

NOT NULL

OtherCheck5

Custom Field

bit

NOT NULL

Auto1

Custom Field

nvarchar(100)

NOT NULL

Auto2

Custom Field

nvarchar(100)

NOT NULL

Auto3

Custom Field

nvarchar(100)

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

CalculateTaxOnKitDetail

Calculate Tax On Kit Detail? Boolean

bit

NOT NULL

Indexes¶

Reporting > Tables > dbo.Items

Author: Jacob Golden

Page 142 of 249

CalculateShipOnKitDetail

Calculate Shipping On Kit Detail? Boolean

bit

NOT NULL

ItemStatus

Item Status

int

NULL allowed

EntryDate

Item Created Date

datetime

NULL allowed

AvailableInAllWarehouses

Available in All Warehouses? - Boolean

bit

NULL allowed

TaxedInAllCountryRegions

Taxed in All Countries and Regions - Boolean

bit

NULL allowed

IsSubscriptionUpdate

Is Subscription Update? - Boolean

bit

NULL allowed

IsPointIncrement

Is Point Increment? - Boolean

bit

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_Items

ItemID

True

PAGE

[dbo].[ItemStaticKitMembers]¶

Reporting > Tables > dbo.ItemStaticKitMembers

Author: Jacob Golden

Page 143 of 249

MS_Description

Static Kit Members

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

MasterItemID

Unique ID

int

NOT NULL

ItemID

Item ID - Map to dbo.Items

int

NOT NULL

Quantity

Quantity of Item in Master Item

money

NOT NULL

ModifiedDate

Record Last Update Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ItemStaticKitMembers

MasterItemID, ItemID

True

PAGE

[dbo].[ItemSubscriptions]¶

Reporting > Tables > dbo.ItemSubscriptions

Author: Jacob Golden

Page 144 of 249

MS_Description

Item Subscriptions

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

ItemID

Item ID - Map to dbo.Items

int

NOT NULL

SubscriptionID

Subscription ID - Map to dbo.Subscriptions

int

NOT NULL

DaysEach

Increment Days

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ItemSubscriptions

ItemID, SubscriptionID

True

PAGE

[dbo].[ItemTypes]¶

Reporting > Tables > dbo.ItemTypes

Author: Jacob Golden

Page 145 of 249

MS_Description

Item Types Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

ItemTypeID

Unique ID

int

NOT NULL

ItemTypeDescription

Description

nvarchar(50)

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_ItemTypes

ItemTypeID

True

PAGE

[dbo].[ItemWarehouses]¶

Reporting > Tables > dbo.ItemWarehouses

Author: Jacob Golden

Page 146 of 249

MS_Description

Item Warehouses

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

ItemID

Item ID - Map to dbo.Items

int

NOT NULL

WarehouseID

Warehouse ID - Map to dbo.Warehouses

int

NOT NULL

MaxAllowedOnOrder

Max Quantity of ItemID Allowed Per Order

int

NOT NULL

StockLevel

Stock Level

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ItemWarehouses

ItemID, WarehouseID

True

PAGE

[dbo].[Languages]¶

Reporting > Tables > dbo.Languages

Author: Jacob Golden

Page 147 of 249

MS_Description

Languages

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

LanguageID

Unique ID

int

NOT NULL

LanguageDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_Languages

LanguageID

True

PAGE

[dbo].[MerchantDeclineLog]¶

Reporting > Tables > dbo.MerchantDeclineLog

Author: Jacob Golden

Page 148 of 249

MS_Description

Merchant Decline Log

Columns

Key

Name

Data Type

Max Length

(Bytes)

Nullability

MerchantDeclineLogID

Unique ID

int

NOT NULL

EntryDate

Record Creation Date

datetime

NOT NULL

MerchantDeclineReasonID

Merchant Decline Reason ID - Map to dbo.MerchantDecline-

Reasons

int

NOT NULL

Message

Merchant Message

nvarchar(500)

1000

NOT NULL

MerchantTypeID

Merchant Type ID - Map to dbo.MerchantTypes

int

NOT NULL

Amount

Amount of Decline

money

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

OrderID

Order ID - Map to dbo.Orders

int

NOT NULL

CurrencyCode

Currency Code - Map to dbo.Currencies

nvarchar(3)

NOT NULL

CreditCardDisplay

Credit Card Display

nvarchar(50)

NULL allowed

PassedCvvCode

Passed CVV Code

bit

NULL allowed

ExpirationMonth

Expire Month

int

NULL allowed

ExpirationYear

Expire Year

int

NULL allowed

BillingName

Billing Name

nvarchar(100)

NULL allowed

BillingAddress

Billing Address

nvarchar(100)

NULL allowed

BillingCity

Billing City

nvarchar(50)

NULL allowed

BillingState

Billing State

nvarchar(50)

NULL allowed

BillingZip

Billing Zip

nvarchar(50)

NULL allowed

BillingCountry

Billing Country

nvarchar(50)

NULL allowed

Indexes¶

Reporting > Tables > dbo.MerchantDeclineLog

Author: Jacob Golden

Page 149 of 249

AdditionalReturnData

Additional Return Data

nvarchar(max)

max

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_MerchantDeclineLog

MerchantDeclineLogID

True

PAGE

[dbo].[MerchantDeclineReasons]¶

Reporting > Tables > dbo.MerchantDeclineReasons

Author: Jacob Golden

Page 150 of 249

MS_Description

Merchant Decline Reasons Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

MerchantDeclineReasonID

Unique ID

int

NOT NULL

MerchantDeclineReasonDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_MerchantDeclineReasons

MerchantDeclineReason-

ID

True

PAGE

[dbo].[MerchantTypes]¶

Reporting > Tables > dbo.MerchantTypes

Author: Jacob Golden

Page 151 of 249

MS_Description

Merchant Types

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

MerchantTypeID

Unique ID

int

NOT NULL

Description

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_MerchantTypes

MerchantTypeID

True

PAGE

[dbo].[OrderBatches]¶

Reporting > Tables > dbo.OrderBatches

Author: Jacob Golden

Page 152 of 249

MS_Description

Order Batches

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

WarehouseID

Warehouse ID - Map to dbo.Warehouses

int

NOT NULL

BatchID

Batch ID

int

NOT NULL

PrintedDate

Printed Date

datetime

NOT NULL

TotalOrders

Total Orders Count

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_OrderBatches

WarehouseID, BatchID,

PrintedDate

True

PAGE

[dbo].[OrderChangeLog]¶

Reporting > Tables > dbo.OrderChangeLog

Author: Jacob Golden

Page 153 of 249

MS_Description

History of Order Changes

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

OrderChangeLogID

Unique ID

int

NOT NULL

OrderID

Order ID - Map to dbo.Orders

int

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

ModifiedBy

Record Last Modified User

nvarchar(50)

NOT NULL

Detail

Detail From and To Values

nvarchar(max)

max

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_OrderChangeLog

OrderChangeLogID

True

PAGE

IX_OrderChangeLog_OrderID

OrderID

PAGE

[dbo].[OrderDetails]¶

Reporting > Tables > dbo.OrderDetails

Author: Jacob Golden

Page 154 of 249

MS_Description

Order Details

Columns

Key

Name

Data Type

Max Length (Bytes)

Nullability

OrderID

Order ID - Map to dbo.Orders

int

NOT NULL

OrderLine

Sorting Order of Items on Order

int

NOT NULL

OrderDetailID

Unique ID

uniqueidentifier

NULL allowed

ParentOrderDetailID

Parent Order Detail ID

uniqueidentifier

NULL allowed

ItemID

Item ID - Map to dbo.Items

int

NOT NULL

ItemCode

Item Code

nvarchar(20)

NOT NULL

ItemDescription

Item Description

nvarchar(500)

1000

NOT NULL

Quantity

Quantity on Order Line

money

NOT NULL

PriceEach

Price Each Item

money

NOT NULL

PriceTotal

Price Each * Quantity

money

NOT NULL

Tax

Tax

money

NOT NULL

WeightEach

Weight Each

money

NOT NULL

Weight

Weight Each * Quantity

money

NULL allowed

BusinessVolumeEach

Business Volume Each

money

NOT NULL

BusinessVolume

Business Volume Each * Quantity

money

NOT NULL

CommissionableVolumeEach

Commissionable Volume Each

money

NOT NULL

CommissionableVolume

Commissionable Volume Each * Quantity

money

NOT NULL

Other1Each

Other 1 Each

money

NOT NULL

Other1

Other 1 Each * Quantity

money

NOT NULL

Other2Each

money

NOT NULL

Section 155¶

Reporting > Tables > dbo.OrderDetails

Author: Jacob Golden

Page 155 of 249

Other 2 Each

Other2

Other 2 Each * Quantity

money

NOT NULL

Other3Each

Other 3 Each

money

NOT NULL

Other3

Other 3 Each * Quantity

money

NOT NULL

Other4Each

Other 4 Each

money

NOT NULL

Other4

Other 4 Each * Quantity

money

NOT NULL

Other5Each

Other 5 Each

money

NOT NULL

Other5

Other 5 Each * Quantity

money

NOT NULL

OriginalTaxableEach

Original Taxable Each

money

NOT NULL

OriginalBusinessVolumeEach

Original Business Volume Each

money

NOT NULL

OriginalCommissionableVolumeEach

Original Commissionable Volume Each

money

NOT NULL

Other6Each

Other 6 Each

money

NOT NULL

Other6

Other 6 Each * Quantity

money

NOT NULL

Other7Each

Other 7 Each

money

NOT NULL

Other7

Other 7 Each * Quantity

money

NOT NULL

Other8Each

Other 8 Each

money

NOT NULL

Other8

Other 8 Each * Quantity

money

NOT NULL

Other9Each

Other 9 Each

money

NOT NULL

Other9

Other 9 Each * Quantity

money

NOT NULL

Other10Each

Other 10 Each

money

NOT NULL

Other10

Other 10 Each * Quantity

money

NOT NULL

ParentItemID

Parent Item ID - Map to dbo.Items

int

NULL allowed

Taxable

Taxable Amount

money

NOT NULL

FedTax

Federal Tax Amount

money

NOT NULL

StateTax

State Tax Amount

money

NOT NULL

CityTax

City Tax Amount

money

NOT NULL

Indexes¶

Reporting > Tables > dbo.OrderDetails

Author: Jacob Golden

Page 156 of 249

Used By

[queue].[Refresh]

CityLocalTax

City Local Tax Amount

money

NOT NULL

CountyTax

County Tax Amount

money

NOT NULL

CountyLocalTax

County Local Tax Amount

money

NOT NULL

ManualTax

Manual Tax Amount

money

NOT NULL

IsStateTaxOverride

Is State Tax Override? Boolean

bit

NOT NULL

Reference1

Reference Field

nvarchar(100)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_OrderDetails

OrderID, OrderLine

True

PAGE

[dbo].[OrderFieldNames]¶

Reporting > Tables > dbo.OrderFieldNames

Author: Jacob Golden

Page 157 of 249

MS_Description

Display Names for Custom Fields in Orders Table

Columns

Name

Data Type

Max Length (Bytes)

Nullability

BusinessVolumeDisplayName

Custom Field

nvarchar(200)

NOT NULL

CommissionableVolumeDisplayName

Custom Field

nvarchar(200)

NOT NULL

Other1DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other2DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other3DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other4DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other5DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other6DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other7DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other8DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other9DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other10DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other11DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other12DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other13DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other14DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other15DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other16DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other17DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other18DisplayName

nvarchar(200)

NOT NULL

Section 158¶

Reporting > Tables > dbo.OrderFieldNames

Author: Jacob Golden

Page 158 of 249

Custom Field

Other19DisplayName

Custom Field

nvarchar(200)

NOT NULL

Other20DisplayName

Custom Field

nvarchar(200)

NOT NULL

[dbo].[Orders]¶

Reporting > Tables > dbo.Orders

Author: Jacob Golden

Page 159 of 249

MS_Description

Orders

Columns

Key

Name

Data Type

Max Length

(Bytes)

Nullability

OrderID

Order ID - Map to dbo.Orders

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

OrderStatusID

Order Status ID - Map to dbo.OrderStatuses

int

NOT NULL

OrderDate

Order Date

datetime

NOT NULL

CurrencyCode

Currency Code - Map to dbo.Currencies

nvarchar(3)

NOT NULL

WarehouseID

Warehouse ID - Map to dbo.Warehouses

int

NOT NULL

ShipMethodID

Ship Method ID - Map to dbo.ShipMethods

int

NOT NULL

OrderTypeID

Order Type ID - Map to dbo.OrderTypes

int

NOT NULL

PriceTypeID

Price Type ID - Map to dbo.PriceTypes

int

NOT NULL

FirstName

Customer First Name

nvarchar(50)

NOT NULL

MiddleName

Customer Middle Name

nvarchar(50)

NOT NULL

LastName

Customer Last Name

nvarchar(50)

NOT NULL

NameSuffix

Customer Name Suffix

nvarchar(50)

NOT NULL

Company

Customer Company

nvarchar(100)

NOT NULL

Address1

Customer Address Line 1

nvarchar(100)

NOT NULL

Address2

Customer Address Line 2

nvarchar(100)

NOT NULL

Address3

Customer Address Line 3

nvarchar(100)

NOT NULL

City

Customer City

nvarchar(50)

NOT NULL

State

Customer State

nvarchar(50)

NOT NULL

Section 160¶

Reporting > Tables > dbo.Orders

Author: Jacob Golden

Page 160 of 249

Zip

Customer Zip

nvarchar(50)

NOT NULL

Country

Customer Country

nvarchar(50)

NOT NULL

County

Customer County

nvarchar(50)

NOT NULL

Email

Customer Email

nvarchar(200)

NOT NULL

Phone

Customer Phone

nvarchar(50)

NOT NULL

Notes

Order Notes

nvarchar(500)

1000

NOT NULL

Total

Order Total

money

NOT NULL

SubTotal

Order SubTotal

money

NOT NULL

TaxTotal

Order Tax Total

money

NOT NULL

ShippingTotal

Order Shipping Total

money

NOT NULL

DiscountTotal

Order Discount Total

money

NOT NULL

DiscountPercent

Order Discount Percent

money

NOT NULL

WeightTotal

Order Weight Total (Typically in lbs)

money

NOT NULL

BusinessVolumeTotal

Order Business Volume Total

money

NOT NULL

CommissionableVolumeTotal

Order Commissionable Volume Total

money

NOT NULL

TrackingNumber1

Order Tracking Number 1

nvarchar(50)

NOT NULL

TrackingNumber2

Order Tracking Number 2

nvarchar(50)

NOT NULL

TrackingNumber3

Order Tracking Number 3

nvarchar(50)

NOT NULL

TrackingNumber4

Order Tracking Number 4

nvarchar(50)

NOT NULL

TrackingNumber5

Order Tracking Number 5

nvarchar(50)

NOT NULL

Other1Total

Other 1 Total

money

NOT NULL

Other2Total

Other 2 Total

money

NOT NULL

Other3Total

Other 3 Total

money

NOT NULL

Other4Total

Other 4 Total

money

NOT NULL

Other5Total

Other 5 Total

money

NOT NULL

Other6Total

Other 6 Total

money

NOT NULL

Section 161¶

Reporting > Tables > dbo.Orders

Author: Jacob Golden

Page 161 of 249

Other7Total

Other 7 Total

money

NOT NULL

Other8Total

Other 8 Total

money

NOT NULL

Other9Total

Other 9 Total

money

NOT NULL

Other10Total

Other 10 Total

money

NOT NULL

ShippingTax

Order Shipping Tax

money

NOT NULL

OrderTax

Order Tax

money

NOT NULL

FedTaxTotal

Federal Tax Total

money

NOT NULL

StateTaxTotal

State Tax Total

money

NOT NULL

FedShippingTax

Federal Shipping Tax

money

NOT NULL

StateShippingTax

Stat Shipping Tax

money

NOT NULL

CityShippingTax

City Shipping Tax

money

NOT NULL

CityLocalShippingTax

City Local Shipping Tax

money

NOT NULL

CountyShippingTax

County Shipping Tax

money

NOT NULL

CountyLocalShippingTax

County Local Shipping Tax

money

NOT NULL

Other11

Other 11

nvarchar(200)

NULL allowed

Other12

Other 12

nvarchar(200)

NULL allowed

Other13

Other 13

nvarchar(200)

NULL allowed

Other14

Other 14

nvarchar(200)

NULL allowed

Other15

Other 15

nvarchar(200)

NULL allowed

Other16

Other 16

nvarchar(200)

NULL allowed

Other17

Other 17

nvarchar(200)

NULL allowed

Other18

Other 18

nvarchar(200)

NULL allowed

Other19

Other 19

nvarchar(200)

NULL allowed

Other20

Other 20

nvarchar(200)

NULL allowed

IsCommissionable

Is Commissionable? Boolean

bit

NOT NULL

AutoOrderID

Auto Order ID - Map to dbo.AutoOrders

int

NULL allowed

Section 162¶

Reporting > Tables > dbo.Orders

Author: Jacob Golden

Page 162 of 249

ReturnOrderID

Return Order ID - Map to dbo.Orders

int

NULL allowed

ReplacementOrderID

Replacement Order ID - Map to dbo.Orders

int

NULL allowed

ParentOrderID

Parent Order ID - Map to dbo.Orders

int

NULL allowed

BatchID

Batch ID

int

NOT NULL

DeclineCount

Decline Count

int

NOT NULL

TransferToCustomerID

Transfer To Customer ID - Map to dbo.Customers

int

NULL allowed

PartyID

Party ID - Map to dbo.Parties

int

NULL allowed

WebCarrierID1

Web Carrier ID 1

int

NULL allowed

WebCarrierID2

Web Carrier ID 2

int

NULL allowed

WebCarrierID3

Web Carrier ID 3

int

NULL allowed

WebCarrierID4

Web Carrier ID 4

int

NULL allowed

WebCarrierID5

Web Carrier ID 5

int

NULL allowed

ShippedDate

Shipped Date

datetime

NULL allowed

CreatedDate

Created Date

datetime

NOT NULL

LockedDate

Locked Date

datetime

NULL allowed

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

CreatedBy

Created By User

nvarchar(30)

NOT NULL

ModifiedBy

Record Last Modified By User

nvarchar(30)

NOT NULL

SuppressPackSlipPrice

Suppress Packing Slip Price? Boolean

bit

NOT NULL

ReturnCategoryID

Return Category ID - Map to dbo.ReturnCategories

int

NULL allowed

ReplacementCategoryID

Replacement Category ID - Map to dbo.Replacement-

Categories

int

NULL allowed

IsRMA

Is RMA? Boolean

bit

NOT NULL

TaxIntegrationCalculate

Tax Integration Calculate

nvarchar(200)

NULL allowed

TaxIntegrationCommit

Tax Integration Commit

nvarchar(200)

NULL allowed

HandlingFee

Handling Fee

money

NOT NULL

PickupName

nvarchar(100)

NOT NULL

Indexes¶

Reporting > Tables > dbo.Orders

Author: Jacob Golden

Page 163 of 249

Used By

[dbo].[CustomerTransactions]

[queue].[Refresh]

Pickup Name

TotalTaxable

Total Taxable Amount

money

NOT NULL

OrderSubStatusID

Order Sub Status ID

int

NULL allowed

OrderKey

Order Key (Text Version of OrderID)

nvarchar(50)

NULL allowed

ReferralId

Referral ID

int

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_Orders

OrderID

True

PAGE

IX_Orders_CurrencyCode

CurrencyCode

PAGE

IX_Orders_CustomerID

CustomerID

PAGE

IX_Orders_OrderDate

OrderDate

PAGE

IX_Orders_OrderStatusID

OrderStatusID

PAGE

IX_Orders_PartyID

PartyID

PAGE

IX_Orders_ReturnOrderID

ReturnOrderID

PAGE

IX_Orders_WarehouseID

WarehouseID

PAGE

[dbo].[OrderStatusChangeLog]¶

Reporting > Tables > dbo.OrderStatusChangeLog

Author: Jacob Golden

Page 164 of 249

MS_Description

History of Order Status Changes

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

OrderStatusChangeLogID

Unique ID

int

NOT NULL

OrderID

Order ID - Map to dbo.Orders

int

NOT NULL

OrderStatusID

Order Status ID - Map to dbo.OrderStatuses

int

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

ModifiedBy

Record Last Modified By User

nvarchar(30)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_OrderStatusChangeLog

OrderStatusChange-

LogID

True

PAGE

IX_OrderStatusChangeLog_OrderID

OrderID

PAGE

[dbo].[OrderStatuses]¶

Reporting > Tables > dbo.OrderStatuses

Author: Jacob Golden

Page 165 of 249

MS_Description

Order Status Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

OrderStatusID

Unique ID

int

NOT NULL

OrderStatusDescription

Description

nvarchar(50)

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_OrderStatuses

OrderStatusID

True

PAGE

[dbo].[OrderStatusOrderSubStatuses]¶

Reporting > Tables > dbo.OrderStatusOrderSubStatuses

Author: Jacob Golden

Page 166 of 249

MS_Description

Order Status Order Sub Status Mapping Table

Columns

Name

Data Type

Max Length (Bytes)

Nullability

OrderStatusOrderSubStatusID

Unique ID

int

NOT NULL

OrderStatusID

Order Status ID - Map to dbo.OrderStatuses

int

NOT NULL

OrderSubStatusID

Order Sub Status ID - Map to dbo.OrderSubStatusTypes

int

NOT NULL

[dbo].[OrderSubStatusTypes]¶

Reporting > Tables > dbo.OrderSubStatusTypes

Author: Jacob Golden

Page 167 of 249

MS_Description

Order Sub Status Types

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

OrderSubStatusID

Unique ID

int

NOT NULL

Description

Description

nvarchar(100)

NOT NULL

IsDeleted

Is Deleted? Boolean

bit

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_OrderSubStatusTypes

OrderSubStatusID

True

PAGE

[dbo].[OrderTypes]¶

Reporting > Tables > dbo.OrderTypes

Author: Jacob Golden

Page 168 of 249

MS_Description

Order Types Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

OrderTypeID

Unique ID

int

NOT NULL

OrderTypeDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_OrderTypes

OrderTypeID

True

PAGE

[dbo].[Overrides]¶

Reporting > Tables > dbo.Overrides

Author: Jacob Golden

Page 169 of 249

MS_Description

Overrides

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

PlanID

Plan ID - Map to dbo.CommissionRuns

int

NOT NULL

OverrideID

Unique ID

int

NOT NULL

OverrideDescription

Description

nvarchar(200)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_Overrides

PlanID, OverrideID

True

PAGE

[dbo].[Parties]¶

Reporting > Tables > dbo.Parties

Author: Jacob Golden

Page 170 of 249

MS_Description

Parties

Columns

Key

Name

Data Type

Max Length

(Bytes)

Nullability

PartyID

Unique ID

int

NOT NULL

HostID

Host ID - Map to dbo.Customers.CustomerID

int

NOT NULL

DistributorID

Distributor ID - Map to dbo.Customers.CustomerID

int

NOT NULL

StartDate

Party Start Date

datetime

NOT NULL

CloseDate

Party Close Date

datetime

NULL allowed

Description

Party Description

nvarchar(100)

NOT NULL

EventStartDate

Event Start Date

datetime

NULL allowed

EventEndDate

Event End Date

datetime

NULL allowed

PartyTypeID

Party Type ID - Map to dbo.PartyTypes

int

NOT NULL

PartyStatusID

Party Status ID - Map to dbo.PartyStatuses

int

NOT NULL

LanguageID

Language ID - Map to dbo.Languages

int

NOT NULL

Information

Party Information

nvarchar(500)

1000

NOT NULL

Address1

Party Address Line 1

nvarchar(100)

NOT NULL

Address2

Party Address Line 2

nvarchar(100)

NOT NULL

City

Party City

nvarchar(100)

NOT NULL

State

Party State

nvarchar(50)

NOT NULL

Zip

Party Zip

nvarchar(50)

NOT NULL

Country

Party Country

nvarchar(50)

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Indexes¶

Reporting > Tables > dbo.Parties

Author: Jacob Golden

Page 171 of 249

BookingPartyID

Booking Party ID

int

NULL allowed

Field1

Custom Field

nvarchar(250)

NULL allowed

Field2

Custom Field

nvarchar(250)

NULL allowed

Field3

Custom Field

nvarchar(250)

NULL allowed

Field4

Custom Field

nvarchar(250)

NULL allowed

Field5

Custom Field

nvarchar(250)

NULL allowed

CreatedDate

Party Created Date

datetime

NULL allowed

PartyExternalID

Party External ID

uniqueidentifier

NULL allowed

PartyShoppingUrl

Party Shopping URL

nvarchar(4000)

8000

NULL allowed

TimezoneID

Timezone ID

nvarchar(510)

1020

NULL allowed

VirtualMeetingLink

Virtual Meeting Link

nvarchar(1000)

2000

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_Parties

PartyID

True

PAGE

IX_Parties_DistributorID

DistributorID

PAGE

IX_Parties_HostID

HostID

PAGE

[dbo].[PartyGuests]¶

Reporting > Tables > dbo.PartyGuests

Author: Jacob Golden

Page 172 of 249

MS_Description

Party Guests

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

PartyID

Party ID - Map to dbo.Parties

int

NOT NULL

GuestID

Guest ID - Map to dbo.Guests

int

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_PartyGuests

PartyID, GuestID

True

PAGE

[dbo].[PartyStatuses]¶

Reporting > Tables > dbo.PartyStatuses

Author: Jacob Golden

Page 173 of 249

MS_Description

Party Statuses Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

PartyStatusID

Unique ID

int

NOT NULL

PartyStatusDescription

Description

nvarchar(100)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_PartyStatuses

PartyStatusID

True

PAGE

[dbo].[PartyTypes]¶

Reporting > Tables > dbo.PartyTypes

Author: Jacob Golden

Page 174 of 249

MS_Description

Party Types Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

PartyTypeID

Unique ID

int

NOT NULL

PartyTypeDescription

Description

nvarchar(100)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_PartyTypes

PartyTypeID

True

PAGE

[dbo].[PayableTypes]¶

Reporting > Tables > dbo.PayableTypes

Author: Jacob Golden

Page 175 of 249

MS_Description

Payable Types

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

PayableTypeID

Unique ID

int

NOT NULL

PayableTypeDescription

Description

nvarchar(50)

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_PayableTypes

PayableTypeID

True

PAGE

[dbo].[PaymentCards]¶

Reporting > Tables > dbo.PaymentCards

Author: Jacob Golden

Page 176 of 249

MS_Description

Payment Cards

Columns

Indexes

Key

Name

Data Type

Max Length

(Bytes)

Nullability

PaymentCardID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

PaymentCardTypeID

Payment Card Type ID - Map to dbo.PaymentCardTypes

int

NOT NULL

CardNumberDisplay

Payment Card Number (Masked)

nvarchar(50)

NULL allowed

IsPrimary

Is Primary? Boolean

bit

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_PaymentCards

PaymentCardID

True

PAGE

IX_PaymentCards_CustomerID

CustomerID

PAGE

[dbo].[PaymentCardTypes]¶

Reporting > Tables > dbo.PaymentCardTypes

Author: Jacob Golden

Page 177 of 249

MS_Description

Payment Card Types Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

PaymentCardTypeID

Unique ID

int

NOT NULL

PaymentCardTypeDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_PaymentCardTypes

PaymentCardTypeID

True

PAGE

[dbo].[Payments]¶

Reporting > Tables > dbo.Payments

Author: Jacob Golden

Page 178 of 249

MS_Description

Payments

Columns

Key

Name

Data Type

Max Length

(Bytes)

Nullability

PaymentID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

OrderID

Order ID - Map to dbo.Orders

int

NULL allowed

PaymentTypeID

Payment Type ID - Map to dbo.PaymentTypes

int

NOT NULL

PaymentDate

Payment Date

datetime

NOT NULL

Amount

Payment Amount in Currency Code

money

NOT NULL

CurrencyCode

Currency Code - Map to dbo.Currencies

nvarchar(3)

NOT NULL

WarehouseID

Warehouse ID - Map to dbo.Warehouses

int

NOT NULL

BillingName

Billing Name

nvarchar(50)

NULL allowed

CreditCardTypeID

Credit Card Type ID - Map to dbo.CreditCardTypes

int

NOT NULL

CreditCardNumber

Credit Card Number (Masked)

nvarchar(50)

NULL allowed

AuthorizationCode

Authorization Code

nvarchar(50)

NULL allowed

CheckNumber

Check Number

nvarchar(50)

NULL allowed

CheckAccountNumber

Check Account Number (Masked)

nvarchar(50)

NULL allowed

CheckRoutingNumber

Check Routing Number

nvarchar(50)

NULL allowed

CheckDate

Check Date

datetime

NULL allowed

CheckBankName

Check Bank Name

nvarchar(100)

NULL allowed

CheckBankAddress

Check Bank Address

nvarchar(100)

NULL allowed

CheckBankCityAddress

Check Bank City

nvarchar(50)

NULL allowed

Indexes¶

Reporting > Tables > dbo.Payments

Author: Jacob Golden

Page 179 of 249

Used By

[dbo].[CustomerTransactions]

[queue].[Refresh]

CheckBankStateAddress

Check Bank State

nvarchar(50)

NULL allowed

CheckBankZipAddress

Check Bank Zip

nvarchar(50)

NULL allowed

Memo

Memo

nvarchar(500)

1000

NULL allowed

WalletTypeID

Wallet Type ID - Map to dbo.WalletTypes

int

NULL allowed

DriversLicenseNumber

Drivers License Number (Masked)

nvarchar(50)

NULL allowed

MerchantTypeID

Merchant Type ID - Map to dbo.Merchants

int

NULL allowed

MerchantTransactionKey

Merchant Transaction Key

nvarchar(300)

NULL allowed

PointAccountID

Point Account ID - Map to dbo.PointAccounts

int

NULL allowed

BillingAddress1

Billing Address Line 1

nvarchar(100)

NULL allowed

BillingAddress2

Billing Address Line 2

nvarchar(50)

NULL allowed

BillingCity

Billing City

nvarchar(50)

NULL allowed

BillingState

Billing State

nvarchar(50)

NULL allowed

BillingZipAddress

Billing Zip

nvarchar(50)

NULL allowed

BillingCountry

Billing Country

nvarchar(50)

NULL allowed

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_Payments

PaymentID

True

PAGE

IX_Payments_OrderID

OrderID

PAGE

[dbo].[PaymentTypes]¶

Reporting > Tables > dbo.PaymentTypes

Author: Jacob Golden

Page 180 of 249

MS_Description

Payment Types

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

PaymentTypeID

Unique ID

int

NOT NULL

PaymentTypeDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_PaymentTypes

PaymentTypeID

True

PAGE

[dbo].[PayoutBills]¶

Reporting > Tables > dbo.PayoutBills

Author: Jacob Golden

Page 181 of 249

MS_Description

Bills to be Paid Out

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

TransactionID

Unique ID

int

NOT NULL

PayoutID

Payout ID - Map to dbo.Payouts

int

NULL allowed

BillID

Bill ID - Map to dbo.Bills

int

NULL allowed

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_PayoutBills

TransactionID

True

PAGE

[dbo].[Payouts]¶

Reporting > Tables > dbo.Payouts

Author: Jacob Golden

Page 182 of 249

MS_Description

Payouts

Columns

Key

Name

Data Type

Max Length (Bytes)

Nullability

PayoutID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

PayoutDate

Payout Date

datetime

NOT NULL

PayoutTypeID

Payout Type ID - Map to dbo.PayoutTypes

int

NOT NULL

CurrencyCode

Currency Code - Map to dbo.Currencies

nvarchar(3)

NOT NULL

Amount

Payout Amount in Currency Code

money

NOT NULL

PayeeName

Payee Name

nvarchar(101)

NULL allowed

PayeeCompany

Payee Company

nvarchar(100)

NOT NULL

PayeeAddress1

Payee Address Line 1

nvarchar(100)

NULL allowed

PayeeAddress2

Payee Address Line 2

nvarchar(100)

NULL allowed

PayeeCity

Payee City

nvarchar(50)

NULL allowed

PayeeState

Payee State

nvarchar(50)

NULL allowed

PayeeZip

Payee Zip

nvarchar(50)

NULL allowed

PayeeCountry

Payee Country

nvarchar(50)

NULL allowed

CheckNumber

Check Number

int

NULL allowed

CheckDate

Check Date

datetime

NULL allowed

VoidedDate

Voided Date

datetime

NULL allowed

DepositNumber

Deposit Number

int

NULL allowed

IsTaxable

Is Taxable? Boolean

bit

NOT NULL

BankAccountID

int

NOT NULL

Indexes¶

Reporting > Tables > dbo.Payouts

Author: Jacob Golden

Page 183 of 249

Bank Account ID

Reference

Reference

nvarchar(50)

NOT NULL

CreatedDate

Payout Created Date

datetime

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

CreatedBy

Payout Created by User

nvarchar(30)

NOT NULL

ModifiedBy

Record Last Modified By User

nvarchar(30)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_Payouts

PayoutID

True

PAGE

[dbo].[PayoutTypes]¶

Reporting > Tables > dbo.PayoutTypes

Author: Jacob Golden

Page 184 of 249

MS_Description

Payout Types

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

PayoutTypeID

Unique ID

int

NOT NULL

PayoutDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_PayoutTypes

PayoutTypeID

True

PAGE

[dbo].[PeriodRankScores]¶

Reporting > Tables > dbo.PeriodRankScores

Author: Jacob Golden

Page 185 of 249

MS_Description

Period Rank Scores

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

PeriodTypeID

Period Type ID - Map to dbo.PeriodTypes

int

NOT NULL

PeriodID

Period ID - Map to dbo.Periods

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

PaidRankID

Current Paid Rank as of Period - Map to dbo.Ranks

int

NOT NULL

Score

money

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_PeriodRankScores

PeriodTypeID, PeriodID,

CustomerID, PaidRankID

True

PAGE

[dbo].[Periods]¶

Reporting > Tables > dbo.Periods

Author: Jacob Golden

Page 186 of 249

MS_Description

Periods

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

PeriodTypeID

Period Type ID - Map to dbo.PeriodTypes

int

NOT NULL

PeriodID

Unique ID

int

NOT NULL

PeriodDescription

Description

nvarchar(50)

NOT NULL

StartDate

Period Start Date

datetime

NOT NULL

EndDate

Period End Date

datetime

NOT NULL

AcceptedDate

Period Accept Date

datetime

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_Periods

PeriodTypeID, PeriodID

True

PAGE

[dbo].[PeriodTypes]¶

Reporting > Tables > dbo.PeriodTypes

Author: Jacob Golden

Page 187 of 249

MS_Description

Period Types

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

PeriodTypeID

Unique ID

int

NOT NULL

PeriodTypeDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_PeriodTypes

PeriodTypeID

True

PAGE

[dbo].[PeriodVolumes]¶

Reporting > Tables > dbo.PeriodVolumes

Author: Jacob Golden

Page 188 of 249

MS_Description

Period Volumes

Columns

Key

Name

Data Type

Max Length (Bytes)

Nullability

PeriodTypeID

Period Type ID - Map to dbo.PeriodTypes

int

NOT NULL

PeriodID

Period ID - Map to dbo.Periods

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

RankID

Rank ID - Map to dbo.Ranks

int

NULL allowed

PaidRankID

Current Paid Rank ID - Map to dbo.Ranks

int

NULL allowed

Volume1

Custom Volume Amount

money

NOT NULL

Volume2

Custom Volume Amount

money

NOT NULL

Volume3

Custom Volume Amount

money

NOT NULL

Volume4

Custom Volume Amount

money

NOT NULL

Volume5

Custom Volume Amount

money

NOT NULL

Volume6

Custom Volume Amount

money

NOT NULL

Volume7

Custom Volume Amount

money

NOT NULL

Volume8

Custom Volume Amount

money

NOT NULL

Volume9

Custom Volume Amount

money

NOT NULL

Volume10

Custom Volume Amount

money

NOT NULL

Volume11

Custom Volume Amount

money

NOT NULL

Volume12

Custom Volume Amount

money

NOT NULL

Volume13

Custom Volume Amount

money

NOT NULL

Volume14

Custom Volume Amount

money

NOT NULL

Volume15

money

NOT NULL

Section 189¶

Reporting > Tables > dbo.PeriodVolumes

Author: Jacob Golden

Page 189 of 249

Custom Volume Amount

Volume16

Custom Volume Amount

money

NOT NULL

Volume17

Custom Volume Amount

money

NOT NULL

Volume18

Custom Volume Amount

money

NOT NULL

Volume19

Custom Volume Amount

money

NOT NULL

Volume20

Custom Volume Amount

money

NOT NULL

Volume21

Custom Volume Amount

money

NOT NULL

Volume22

Custom Volume Amount

money

NOT NULL

Volume23

Custom Volume Amount

money

NOT NULL

Volume24

Custom Volume Amount

money

NOT NULL

Volume25

Custom Volume Amount

money

NOT NULL

Volume26

Custom Volume Amount

money

NOT NULL

Volume27

Custom Volume Amount

money

NOT NULL

Volume28

Custom Volume Amount

money

NOT NULL

Volume29

Custom Volume Amount

money

NOT NULL

Volume30

Custom Volume Amount

money

NOT NULL

Volume31

Custom Volume Amount

money

NOT NULL

Volume32

Custom Volume Amount

money

NOT NULL

Volume33

Custom Volume Amount

money

NOT NULL

Volume34

Custom Volume Amount

money

NOT NULL

Volume35

Custom Volume Amount

money

NOT NULL

Volume36

Custom Volume Amount

money

NOT NULL

Volume37

Custom Volume Amount

money

NOT NULL

Volume38

Custom Volume Amount

money

NOT NULL

Volume39

Custom Volume Amount

money

NOT NULL

Volume40

Custom Volume Amount

money

NOT NULL

Section 190¶

Reporting > Tables > dbo.PeriodVolumes

Author: Jacob Golden

Page 190 of 249

Volume41

Custom Volume Amount

money

NOT NULL

Volume42

Custom Volume Amount

money

NOT NULL

Volume43

Custom Volume Amount

money

NOT NULL

Volume44

Custom Volume Amount

money

NOT NULL

Volume45

Custom Volume Amount

money

NOT NULL

Volume46

Custom Volume Amount

money

NOT NULL

Volume47

Custom Volume Amount

money

NOT NULL

Volume48

Custom Volume Amount

money

NOT NULL

Volume49

Custom Volume Amount

money

NOT NULL

Volume50

Custom Volume Amount

money

NOT NULL

Volume51

Custom Volume Amount

money

NOT NULL

Volume52

Custom Volume Amount

money

NOT NULL

Volume53

Custom Volume Amount

money

NOT NULL

Volume54

Custom Volume Amount

money

NOT NULL

Volume55

Custom Volume Amount

money

NOT NULL

Volume56

Custom Volume Amount

money

NOT NULL

Volume57

Custom Volume Amount

money

NOT NULL

Volume58

Custom Volume Amount

money

NOT NULL

Volume59

Custom Volume Amount

money

NOT NULL

Volume60

Custom Volume Amount

money

NOT NULL

Volume61

Custom Volume Amount

money

NOT NULL

Volume62

Custom Volume Amount

money

NOT NULL

Volume63

Custom Volume Amount

money

NOT NULL

Volume64

Custom Volume Amount

money

NOT NULL

Volume65

Custom Volume Amount

money

NOT NULL

Volume66

Custom Volume Amount

money

NOT NULL

Section 191¶

Reporting > Tables > dbo.PeriodVolumes

Author: Jacob Golden

Page 191 of 249

Volume67

Custom Volume Amount

money

NOT NULL

Volume68

Custom Volume Amount

money

NOT NULL

Volume69

Custom Volume Amount

money

NOT NULL

Volume70

Custom Volume Amount

money

NOT NULL

Volume71

Custom Volume Amount

money

NOT NULL

Volume72

Custom Volume Amount

money

NOT NULL

Volume73

Custom Volume Amount

money

NOT NULL

Volume74

Custom Volume Amount

money

NOT NULL

Volume75

Custom Volume Amount

money

NOT NULL

Volume76

Custom Volume Amount

money

NOT NULL

Volume77

Custom Volume Amount

money

NOT NULL

Volume78

Custom Volume Amount

money

NOT NULL

Volume79

Custom Volume Amount

money

NOT NULL

Volume80

Custom Volume Amount

money

NOT NULL

Volume81

Custom Volume Amount

money

NOT NULL

Volume82

Custom Volume Amount

money

NOT NULL

Volume83

Custom Volume Amount

money

NOT NULL

Volume84

Custom Volume Amount

money

NOT NULL

Volume85

Custom Volume Amount

money

NOT NULL

Volume86

Custom Volume Amount

money

NOT NULL

Volume87

Custom Volume Amount

money

NOT NULL

Volume88

Custom Volume Amount

money

NOT NULL

Volume89

Custom Volume Amount

money

NOT NULL

Volume90

Custom Volume Amount

money

NOT NULL

Volume91

Custom Volume Amount

money

NOT NULL

Volume92

Custom Volume Amount

money

NOT NULL

Section 192¶

Reporting > Tables > dbo.PeriodVolumes

Author: Jacob Golden

Page 192 of 249

Volume93

Custom Volume Amount

money

NOT NULL

Volume94

Custom Volume Amount

money

NOT NULL

Volume95

Custom Volume Amount

money

NOT NULL

Volume96

Custom Volume Amount

money

NOT NULL

Volume97

Custom Volume Amount

money

NOT NULL

Volume98

Custom Volume Amount

money

NOT NULL

Volume99

Custom Volume Amount

money

NOT NULL

Volume100

Custom Volume Amount

money

NOT NULL

Volume101

Custom Volume Amount

money

NOT NULL

Volume102

Custom Volume Amount

money

NOT NULL

Volume103

Custom Volume Amount

money

NOT NULL

Volume104

Custom Volume Amount

money

NOT NULL

Volume105

Custom Volume Amount

money

NOT NULL

Volume106

Custom Volume Amount

money

NOT NULL

Volume107

Custom Volume Amount

money

NOT NULL

Volume108

Custom Volume Amount

money

NOT NULL

Volume109

Custom Volume Amount

money

NOT NULL

Volume110

Custom Volume Amount

money

NOT NULL

Volume111

Custom Volume Amount

money

NOT NULL

Volume112

Custom Volume Amount

money

NOT NULL

Volume113

Custom Volume Amount

money

NOT NULL

Volume114

Custom Volume Amount

money

NOT NULL

Volume115

Custom Volume Amount

money

NOT NULL

Volume116

Custom Volume Amount

money

NOT NULL

Volume117

Custom Volume Amount

money

NOT NULL

Volume118

Custom Volume Amount

money

NOT NULL

Section 193¶

Reporting > Tables > dbo.PeriodVolumes

Author: Jacob Golden

Page 193 of 249

Volume119

Custom Volume Amount

money

NOT NULL

Volume120

Custom Volume Amount

money

NOT NULL

Volume121

Custom Volume Amount

money

NOT NULL

Volume122

Custom Volume Amount

money

NOT NULL

Volume123

Custom Volume Amount

money

NOT NULL

Volume124

Custom Volume Amount

money

NOT NULL

Volume125

Custom Volume Amount

money

NOT NULL

Volume126

Custom Volume Amount

money

NOT NULL

Volume127

Custom Volume Amount

money

NOT NULL

Volume128

Custom Volume Amount

money

NOT NULL

Volume129

Custom Volume Amount

money

NOT NULL

Volume130

Custom Volume Amount

money

NOT NULL

Volume131

Custom Volume Amount

money

NOT NULL

Volume132

Custom Volume Amount

money

NOT NULL

Volume133

Custom Volume Amount

money

NOT NULL

Volume134

Custom Volume Amount

money

NOT NULL

Volume135

Custom Volume Amount

money

NOT NULL

Volume136

Custom Volume Amount

money

NOT NULL

Volume137

Custom Volume Amount

money

NOT NULL

Volume138

Custom Volume Amount

money

NOT NULL

Volume139

Custom Volume Amount

money

NOT NULL

Volume140

Custom Volume Amount

money

NOT NULL

Volume141

Custom Volume Amount

money

NOT NULL

Volume142

Custom Volume Amount

money

NOT NULL

Volume143

Custom Volume Amount

money

NOT NULL

Volume144

Custom Volume Amount

money

NOT NULL

Section 194¶

Reporting > Tables > dbo.PeriodVolumes

Author: Jacob Golden

Page 194 of 249

Volume145

Custom Volume Amount

money

NOT NULL

Volume146

Custom Volume Amount

money

NOT NULL

Volume147

Custom Volume Amount

money

NOT NULL

Volume148

Custom Volume Amount

money

NOT NULL

Volume149

Custom Volume Amount

money

NOT NULL

Volume150

Custom Volume Amount

money

NOT NULL

Volume151

Custom Volume Amount

money

NOT NULL

Volume152

Custom Volume Amount

money

NOT NULL

Volume153

Custom Volume Amount

money

NOT NULL

Volume154

Custom Volume Amount

money

NOT NULL

Volume155

Custom Volume Amount

money

NOT NULL

Volume156

Custom Volume Amount

money

NOT NULL

Volume157

Custom Volume Amount

money

NOT NULL

Volume158

Custom Volume Amount

money

NOT NULL

Volume159

Custom Volume Amount

money

NOT NULL

Volume160

Custom Volume Amount

money

NOT NULL

Volume161

Custom Volume Amount

money

NOT NULL

Volume162

Custom Volume Amount

money

NOT NULL

Volume163

Custom Volume Amount

money

NOT NULL

Volume164

Custom Volume Amount

money

NOT NULL

Volume165

Custom Volume Amount

money

NOT NULL

Volume166

Custom Volume Amount

money

NOT NULL

Volume167

Custom Volume Amount

money

NOT NULL

Volume168

Custom Volume Amount

money

NOT NULL

Volume169

Custom Volume Amount

money

NOT NULL

Volume170

Custom Volume Amount

money

NOT NULL

Section 195¶

Reporting > Tables > dbo.PeriodVolumes

Author: Jacob Golden

Page 195 of 249

Volume171

Custom Volume Amount

money

NOT NULL

Volume172

Custom Volume Amount

money

NOT NULL

Volume173

Custom Volume Amount

money

NOT NULL

Volume174

Custom Volume Amount

money

NOT NULL

Volume175

Custom Volume Amount

money

NOT NULL

Volume176

Custom Volume Amount

money

NOT NULL

Volume177

Custom Volume Amount

money

NOT NULL

Volume178

Custom Volume Amount

money

NOT NULL

Volume179

Custom Volume Amount

money

NOT NULL

Volume180

Custom Volume Amount

money

NOT NULL

Volume181

Custom Volume Amount

money

NOT NULL

Volume182

Custom Volume Amount

money

NOT NULL

Volume183

Custom Volume Amount

money

NOT NULL

Volume184

Custom Volume Amount

money

NOT NULL

Volume185

Custom Volume Amount

money

NOT NULL

Volume186

Custom Volume Amount

money

NOT NULL

Volume187

Custom Volume Amount

money

NOT NULL

Volume188

Custom Volume Amount

money

NOT NULL

Volume189

Custom Volume Amount

money

NOT NULL

Volume190

Custom Volume Amount

money

NOT NULL

Volume191

Custom Volume Amount

money

NOT NULL

Volume192

Custom Volume Amount

money

NOT NULL

Volume193

Custom Volume Amount

money

NOT NULL

Volume194

Custom Volume Amount

money

NOT NULL

Volume195

Custom Volume Amount

money

NOT NULL

Volume196

Custom Volume Amount

money

NOT NULL

Indexes¶

Reporting > Tables > dbo.PeriodVolumes

Author: Jacob Golden

Page 196 of 249

Volume197

Custom Volume Amount

money

NOT NULL

Volume198

Custom Volume Amount

money

NOT NULL

Volume199

Custom Volume Amount

money

NOT NULL

Volume200

Custom Volume Amount

money

NOT NULL

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

OtherData1

Custom Field

nvarchar(max)

max

NULL allowed

OtherData2

Custom Field

nvarchar(max)

max

NULL allowed

OtherData3

Custom Field

nvarchar(max)

max

NULL allowed

OtherData4

Custom Field

nvarchar(max)

max

NULL allowed

OtherData5

Custom Field

nvarchar(max)

max

NULL allowed

OtherData6

Custom Field

nvarchar(max)

max

NULL allowed

OtherData7

Custom Field

nvarchar(max)

max

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_PeriodVolumes

PeriodTypeID, PeriodID,

CustomerID

True

PAGE

[dbo].[PointAccounts]¶

Reporting > Tables > dbo.PointAccounts

Author: Jacob Golden

Page 197 of 249

MS_Description

Point Accounts

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

PointAccountID

Unique ID

int

NOT NULL

PointAccountDescription

Description

nvarchar(50)

NOT NULL

CurrencyCode

Currency Code - Map to dbo.Currencies

nvarchar(3)

NOT NULL

CanPayForOrders

Can Pay For Orders? Boolean

bit

NULL allowed

LimitPaymentToSubTotal

Limit Payment To SubTotal? Boolean

bit

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_PointAccounts

PointAccountID

True

PAGE

[dbo].[PointTransactions]¶

Reporting > Tables > dbo.PointTransactions

Author: Jacob Golden

Page 198 of 249

MS_Description

Point Transactions

Columns

Indexes

Key

Name

Data Type

Max Length

(Bytes)

Nullability

PointTransactionID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

PointAccountID

Point Account ID - Map to dbo.PointAccounts

int

NOT NULL

Amount

Point Amount

money

NOT NULL

PointTransactionTypeID

Point Transaction Type ID - Map to dbo.PointTransaction-

Types

int

NOT NULL

PaymentID

Payment ID - Map to dbo.Payments

int

NULL allowed

TransactionDate

Transaction Date

datetime

NOT NULL

OrderID

Order ID - Map to dbo.Orders

int

NULL allowed

Reference

Reference

nvarchar(100)

NOT NULL

CreatedBy

Created By User

nvarchar(30)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_PointTransactions

PointTransactionID

True

PAGE

IX_PointTransactions_CustomerID

CustomerID

PAGE

[dbo].[PointTransactionTypes]¶

Reporting > Tables > dbo.PointTransactionTypes

Author: Jacob Golden

Page 199 of 249

MS_Description

Point Transaction Types

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

PointTransactionTypeID

Unique ID

int

NOT NULL

PointTransactionTypeDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_PointTransactionTypes

PointTransactionTypeID

True

PAGE

[dbo].[PriceTypes]¶

Reporting > Tables > dbo.PriceTypes

Author: Jacob Golden

Page 200 of 249

MS_Description

Price Types

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

PriceTypeID

Unique ID

int

NOT NULL

PriceTypeDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_PriceTypes

PriceTypeID

True

PAGE

[dbo].[Ranks]¶

Reporting > Tables > dbo.Ranks

Author: Jacob Golden

Page 201 of 249

MS_Description

Ranks

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

RankID

Unique ID

int

NOT NULL

RankDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_Ranks

RankID

True

PAGE

[dbo].[ReplacementCategories]¶

Reporting > Tables > dbo.ReplacementCategories

Author: Jacob Golden

Page 202 of 249

MS_Description

Replacement Categories

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

ReplacementCategoryID

Unique ID

int

NOT NULL

ReplacementCategoryDescription

Description

nvarchar(100)

NOT NULL

Enabled

Is Enabled? Boolean

bit

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ReplacementCategories

ReplacementCategoryID

True

PAGE

[dbo].[ReturnCategories]¶

Reporting > Tables > dbo.ReturnCategories

Author: Jacob Golden

Page 203 of 249

MS_Description

Return Categories

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

ReturnCategoryID

Unique ID

int

NOT NULL

ReturnCategoryDescription

Description

nvarchar(100)

NOT NULL

Enabled

Is Enabled? Boolean

bit

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ReturnCategories

ReturnCategoryID

True

PAGE

[dbo].[ShipCarriers]¶

Reporting > Tables > dbo.ShipCarriers

Author: Jacob Golden

Page 204 of 249

MS_Description

Ship Carriers

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

ShipCarrierID

Unique ID

int

NOT NULL

ShipCarrierDescription

Description

nvarchar(50)

NOT NULL

TrackingUrl

Tracking URL

nvarchar(255)

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_ShipCarriers

ShipCarrierID

True

PAGE

[dbo].[ShipMethods]¶

Reporting > Tables > dbo.ShipMethods

Author: Jacob Golden

Page 205 of 249

MS_Description

Ship Methods

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

ShipMethodID

Unique ID

int

NOT NULL

ShipMethodDescription

Description

nvarchar(50)

NULL allowed

WarehouseID

Warehouse ID - Map to dbo.Warehouses

int

NOT NULL

ShipCarrierID

Ship Carrier ID - Map to dbo.ShipCarriers

int

NOT NULL

DisplayOnWeb

Display on Web? Boolean

bit

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_ShipMethods

ShipMethodID

True

PAGE

[dbo].[ShipRates]¶

Reporting > Tables > dbo.ShipRates

Author: Jacob Golden

Page 206 of 249

MS_Description

Ship Rates

Columns

Name

Data Type

Max Length (Bytes)

Nullability

ShipMethodID

Ship Method ID - Map to dbo.ShipMethods

int

NOT NULL

CurrencyCode

Currency Code - Map to dbo.Currencies

nvarchar(3)

NOT NULL

ChargeLineID

Charge Line ID

int

NOT NULL

MinRange

Minimum Range

money

NOT NULL

MaxRange

Maximum Range

money

NOT NULL

FlatAmount

Flat Amount

money

NOT NULL

PercentageAmount

Percentage Amount

money

NOT NULL

MinCharge

Minimum Charge

money

NOT NULL

ByPrice

By Price? Boolean

bit

NOT NULL

ByWeight

By Weight? Boolean

bit

NOT NULL

UsePercentage

Use Percentage? Boolean

bit

NOT NULL

[dbo].[SmsMessages]¶

Reporting > Tables > dbo.SmsMessages

Author: Jacob Golden

Page 207 of 249

MS_Description

SMS Messages

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

MessageID

Unique ID

uniqueidentifier

NOT NULL

MessageDate

Message Date

datetime

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

SmsStatusID

SMS Status ID - Map to dbo.SmsStatuses

int

NOT NULL

ParentMessageID

Parent Message ID

uniqueidentifier

NULL allowed

FromNumber

From Number

nvarchar(30)

NOT NULL

ToNumber

To Number

nvarchar(30)

NOT NULL

Message

Message Text

nvarchar(max)

max

NOT NULL

Exception

Exception

nvarchar(500)

1000

NULL allowed

ModifiedDate

Record Last Modified Date

datetime

NOT NULL

BroadcastID

Broadcast ID - Map to dbo.Broadcasts

int

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_SmsMessages

MessageID

True

PAGE

[dbo].[SmsStatuses]¶

Reporting > Tables > dbo.SmsStatuses

Author: Jacob Golden

Page 208 of 249

MS_Description

SMS Statuses Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

SmsStatusID

Unique ID

int

NOT NULL

SmsStatusDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_SmsStatuses

SmsStatusID

True

PAGE

[dbo].[SocialNetworks]¶

Reporting > Tables > dbo.SocialNetworks

Author: Jacob Golden

Page 209 of 249

MS_Description

Social Networks Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

SocialNetworkID

Unique ID

int

NOT NULL

SocialNetworkDescription

Description

nvarchar(100)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_SocialNetworks

SocialNetworkID

True

PAGE

[dbo].[Subscriptions]¶

Reporting > Tables > dbo.Subscriptions

Author: Jacob Golden

Page 210 of 249

MS_Description

Subscriptions Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

SubscriptionID

Unique ID

int

NOT NULL

SubscriptionDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_Subscriptions

SubscriptionID

True

PAGE

[dbo].[TaxAddressTypes]¶

Reporting > Tables > dbo.TaxAddressTypes

Author: Jacob Golden

Page 211 of 249

MS_Description

Tax Address Types Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

TaxAddressTypeID

Unique ID

int

NOT NULL

TaxAddressTypeDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_TaxAddressTypes

TaxAddressTypeID

True

PAGE

[dbo].[TaxCodeTypes]¶

Reporting > Tables > dbo.TaxCodeTypes

Author: Jacob Golden

Page 212 of 249

MS_Description

Tax Code Types Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

TaxCodeTypeID

Unique ID

int

NOT NULL

TaxCodeDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_TaxCodeTypes

TaxCodeTypeID

True

PAGE

[dbo].[TaxNameTypes]¶

Reporting > Tables > dbo.TaxNameTypes

Author: Jacob Golden

Page 213 of 249

MS_Description

Tax Name Types Reference Table

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

TaxNameTypeID

Unique ID

int

NOT NULL

TaxNameTypeDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_TaxNameTypes

TaxNameTypeID

True

PAGE

[dbo].[UniLevelTree]¶

Reporting > Tables > dbo.UniLevelTree

Author: Jacob Golden

Page 214 of 249

MS_Description

Unilevel Tree (Memory Optimized Table)

Columns

Indexes

Used By

[dbo].[UniLevelDownline]

[dbo].[UniLevelUpline]

Key

Name

Data Type

Max Length (Bytes)

Nullability

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

SponsorID

Sponsor ID - Map to dbo.Customers

int

NOT NULL

NestedLevel

Nested Level

int

NOT NULL

Placement

Placement

int

NOT NULL

Lft

Index Left Boundary

int

NOT NULL

Rgt

Index Right Boundary

int

NOT NULL

Key

Name

Key Columns

Unique

PK_UniLevelTree

CustomerID

True

[dbo].[UnilevelTreeHistory]¶

Reporting > Tables > dbo.UnilevelTreeHistory

Author: Jacob Golden

Page 215 of 249

MS_Description

History of Unilievel Tree Changes

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

UnilevelTreeHistoryID

Unique ID

int

NOT NULL

CustomerID

Customer ID - Map to dbo.Customers

int

NOT NULL

MoveDate

Move Date

datetime

NOT NULL

PreviousSponsorID

Previous Sponsor ID - Map to dbo.Customers

int

NOT NULL

PreviousNestedLevel

Previous Nested Level

int

NOT NULL

NewSponsorID

New Sponsor ID - Map to dbo.Customers

int

NOT NULL

NewNestedLevel

New Nested Level

int

NOT NULL

Reason

Reason for Move

nvarchar(1000)

2000

NOT NULL

PreviousPlacement

Previous Placement

int

NOT NULL

NewPlacement

New Placement

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_UnilevelTreeHistory

UnilevelTreeHistoryID,

CustomerID

True

PAGE

[dbo].[Users]¶

Reporting > Tables > dbo.Users

Author: Jacob Golden

Page 216 of 249

MS_Description

Users

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

UserID

Unique ID

int

NOT NULL

LoginName

Login Name

nvarchar(100)

NOT NULL

FirstName

First Name

nvarchar(50)

NOT NULL

LastName

Last Name

nvarchar(50)

NOT NULL

Email

Email

nvarchar(50)

NOT NULL

DepartmentID

Department ID - Map to dbo.Departments

int

NOT NULL

IsActive

Is Active? Boolean

bit

NOT NULL

IsDeleted

Is Deleted? Boolean

bit

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_Users

UserID

True

PAGE

[dbo].[VolumePushCycleLogs]¶

Reporting > Tables > dbo.VolumePushCycleLogs

Author: Jacob Golden

Page 217 of 249

MS_Description

Volume Push Cycle Logs

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

VolumePushCycleLogID

Unique ID

int

NOT NULL

CommissionPlanID

Commission Plan ID - Map to dbo.Runs

int

NULL allowed

PeriodID

Period ID - Map to dbo.Periods

int

NULL allowed

PeriodTypeID

Period Type ID - Map to dbo.PeriodTypes

int

NULL allowed

VolumePushIsTrueUp

VP Is a True Up? Boolean

bit

NULL allowed

VolumePushCycleStartDate

VP Cycle Start Date

datetime

NULL allowed

VolumePushCycleEndDate

VP Cycle End Date

datetime

NULL allowed

VolumePushCycleErrorDate

VP Cycle Error Date

datetime

NULL allowed

Key

Name

Key Columns

Unique

Compression

PK_VolumePushCycleLogs

VolumePushCycleLogID

True

PAGE

[dbo].[WalletTypes]¶

Reporting > Tables > dbo.WalletTypes

Author: Jacob Golden

Page 218 of 249

MS_Description

Wallet Types

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

WalletTypeID

Unique ID

int

NOT NULL

WalletTypeDescription

Description

nvarchar(50)

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_WalletTypes

WalletTypeID

True

PAGE

[dbo].[WarehouseCurrencies]¶

Reporting > Tables > dbo.WarehouseCurrencies

Author: Jacob Golden

Page 219 of 249

MS_Description

Warehouse Currencies

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

WarehouseID

Warehouse ID - Map to dbo.Warehouses

int

NOT NULL

CurrencyCode

Currency Code - Map to dbo.Currencies

nvarchar(3)

NOT NULL

Priority

Priority for Sorting

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_WarehouseCurrencies

WarehouseID, Currency-

Code

True

PAGE

[dbo].[Warehouses]¶

Reporting > Tables > dbo.Warehouses

Author: Jacob Golden

Page 220 of 249

MS_Description

Warehouses

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

WarehouseID

Unique ID

int

NOT NULL

WarehouseDescription

Warehouse Description

nvarchar(50)

NOT NULL

WarehouseAddress1

Warehouse Address Line 1

nvarchar(50)

NOT NULL

WarehouseAddress2

Warehouse Address Line 2

nvarchar(50)

NOT NULL

WarehouseCity

Warehouse City

nvarchar(30)

NOT NULL

WarehouseState

Warehouse State

nvarchar(10)

NOT NULL

WarehouseZip

Warehouse Zip

nvarchar(20)

NOT NULL

WarehouseCountry

Warehouse Country

nvarchar(20)

NOT NULL

TimeZoneID

Timezone ID

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_Warehouses

WarehouseID

True

PAGE

[dbo].[WebCategories]¶

Reporting > Tables > dbo.WebCategories

Author: Jacob Golden

Page 221 of 249

MS_Description

Web Categories

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

WebID

Web ID

int

NOT NULL

WebCategoryID

Unique ID

int

NOT NULL

ParentID

Parent ID

int

NULL allowed

WebCategoryDescription

Description

nvarchar(50)

NOT NULL

NestedLevel

Nested Level

int

NOT NULL

SortOrder

Sort Order

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_WebCategories

WebID, WebCategoryID

True

PAGE

[dbo].[WebCategoryItems]¶

Reporting > Tables > dbo.WebCategoryItems

Author: Jacob Golden

Page 222 of 249

MS_Description

Web Category Items

Columns

Indexes

Key

Name

Data Type

Max Length (Bytes)

Nullability

WebID

Web ID

int

NOT NULL

WebCategoryID

Web Category ID - Map to dbo.WebCategories

int

NOT NULL

ItemID

Item ID - Map to dbo.Items

int

NOT NULL

SortOrder

Sort Order

int

NOT NULL

Key

Name

Key Columns

Unique

Compression

PK_WebCategoryItems

WebID, WebCategoryID,

ItemID

True

PAGE

Views¶

Reporting > Views

Author: Jacob Golden

Page 223 of 249

Objects

Name

dbo.BinaryDownline

Downline View for Binary Tree

dbo.BinaryUpline

Upline View for Binary Tree

dbo.CustomerTransactions

Customer Transactions

dbo.EnrollerDownline

Downline View for Enroller Tree

dbo.EnrollerUpline

Upline View for Enroller Tree

dbo.UniLevelDownline

Downline View for Unilevel Tree

dbo.UniLevelUpline

Upline View for Unllevel Tree

[dbo].[BinaryDownline]¶

Reporting > Views > dbo.BinaryDownline

Author: Jacob Golden

Page 224 of 249

MS_Description

Downline View for Binary Tree

Columns

SQL Script

CREATE view [dbo].[BinaryDownline]

as

Select DownlineCustomerID = tp.CustomerID,

t.CustomerID,

t.ParentID,

Level = t.NestedLevel - tp.NestedLevel,

t.Placement,

IndentedSort = t.lft

From BinaryTree tp

inner Join BinaryTree t

on t.lft between tp.lft and tp.rgt

GO

EXEC sp_addextendedproperty N'MS_Description', N'Downline View for Binary Tree', 'SCHEMA',

N'dbo', 'VIEW', N'BinaryDownline', NULL, NULL

GO

Uses

[dbo].[BinaryTree]

Name

Data Type

Max Length (Bytes)

DownlineCustomerID

int

CustomerID

int

ParentID

int

Level

int

Placement

int

IndentedSort

int

[dbo].[BinaryUpline]¶

Reporting > Views > dbo.BinaryUpline

Author: Jacob Golden

Page 225 of 249

MS_Description

Upline View for Binary Tree

Columns

SQL Script

CREATE view [dbo].[BinaryUpline]

as

Select UplineCustomerID = tp.CustomerID,

t.CustomerID,

t.ParentID,

Level = t.NestedLevel - tp.NestedLevel,

t.Placement,

IndentedSort = t.lft

From BinaryTree tp

inner Join BinaryTree t

on t.lft <= tp.lft and t.rgt >= tp.rgt

GO

EXEC sp_addextendedproperty N'MS_Description', N'Upline View for Binary Tree', 'SCHEMA', N'dbo',

'VIEW', N'BinaryUpline', NULL, NULL

GO

Uses

[dbo].[BinaryTree]

Name

Data Type

Max Length (Bytes)

UplineCustomerID

int

CustomerID

int

ParentID

int

Level

int

Placement

int

IndentedSort

int

[dbo].[CustomerTransactions]¶

Reporting > Views > dbo.CustomerTransactions

Author: Jacob Golden

Page 226 of 249

MS_Description

Customer Transactions

Columns

SQL Script

CREATE view [dbo].[CustomerTransactions]

as

select CustomerID,

TransactionTypeID = 0,

OrderID,

PaymentID=null,

AdjustmentID=null,

TransactionDate=OrderDate,

CurrencyCode,

-- Cancelled Orders and AutoOrders less than accepted do not count towards the

balance

Amount=case when OrderStatusID=4 or (OrderTypeID=8 and OrderStatusID < 7) then 0 else

Total end

from Orders o

union

select CustomerID,

TransactionTypeID = 1,

OrderID,

PaymentID,

null,

TransactionDate = PaymentDate,

CurrencyCode,

Amount = -Amount

from Payments p

Name

Data Type

Max Length (Bytes)

CustomerID

int

TransactionTypeID

int

OrderID

int

PaymentID

int

AdjustmentID

int

TransactionDate

datetime

CurrencyCode

nvarchar(3)

Amount

money

Section 227¶

Reporting > Views > dbo.CustomerTransactions

Author: Jacob Golden

Page 227 of 249

union

select CustomerID,

ca.CustomerTransactionTypeID,

null,

TransactionID,

TransactionDate,

CurrencyCode,

Amount

from CustomerAdjustments ca

GO

EXEC sp_addextendedproperty N'MS_Description', N'Customer Transactions', 'SCHEMA', N'dbo',

'VIEW', N'CustomerTransactions', NULL, NULL

GO

Uses

[dbo].[CustomerAdjustments]

[dbo].[Orders]

[dbo].[Payments]

[dbo].[EnrollerDownline]¶

Reporting > Views > dbo.EnrollerDownline

Author: Jacob Golden

Page 228 of 249

MS_Description

Downline View for Enroller Tree

Columns

SQL Script

CREATE view [dbo].[EnrollerDownline]

as

Select DownlineCustomerID = tp.CustomerID,

t.CustomerID,

t.EnrollerID,

Level = t.NestedLevel - tp.NestedLevel,

IndentedSort = t.lft

From EnrollerTree tp

inner Join EnrollerTree t

on t.lft between tp.lft and tp.rgt

GO

EXEC sp_addextendedproperty N'MS_Description', N'Downline View for Enroller Tree', 'SCHEMA',

N'dbo', 'VIEW', N'EnrollerDownline', NULL, NULL

GO

Uses

[dbo].[EnrollerTree]

Name

Data Type

Max Length (Bytes)

DownlineCustomerID

int

CustomerID

int

EnrollerID

int

Level

int

IndentedSort

int

[dbo].[EnrollerUpline]¶

Reporting > Views > dbo.EnrollerUpline

Author: Jacob Golden

Page 229 of 249

MS_Description

Upline View for Enroller Tree

Columns

SQL Script

CREATE view [dbo].[EnrollerUpline]

as

Select UplineCustomerID = tp.CustomerID,

t.CustomerID,

t.EnrollerID,

Level = t.NestedLevel - tp.NestedLevel,

IndentedSort = t.lft

From EnrollerTree tp

inner Join EnrollerTree t

on t.lft <= tp.lft and t.rgt >= tp.rgt

GO

EXEC sp_addextendedproperty N'MS_Description', N'Upline View for Enroller Tree', 'SCHEMA',

N'dbo', 'VIEW', N'EnrollerUpline', NULL, NULL

GO

Uses

[dbo].[EnrollerTree]

Name

Data Type

Max Length (Bytes)

UplineCustomerID

int

CustomerID

int

EnrollerID

int

Level

int

IndentedSort

int

[dbo].[UniLevelDownline]¶

Reporting > Views > dbo.UniLevelDownline

Author: Jacob Golden

Page 230 of 249

MS_Description

Downline View for Unilevel Tree

Columns

SQL Script

CREATE view [dbo].[UniLevelDownline]

as

Select DownlineCustomerID = tp.CustomerID,

t.CustomerID,

t.SponsorID,

Level = t.NestedLevel - tp.NestedLevel,

t.Placement,

IndentedSort = t.lft

From UniLevelTree tp

inner Join UniLevelTree t

on t.lft between tp.lft and tp.rgt

GO

EXEC sp_addextendedproperty N'MS_Description', N'Downline View for Unilevel Tree', 'SCHEMA',

N'dbo', 'VIEW', N'UniLevelDownline', NULL, NULL

GO

Uses

[dbo].[UniLevelTree]

Name

Data Type

Max Length (Bytes)

DownlineCustomerID

int

CustomerID

int

SponsorID

int

Level

int

Placement

int

IndentedSort

int

[dbo].[UniLevelUpline]¶

Reporting > Views > dbo.UniLevelUpline

Author: Jacob Golden

Page 231 of 249

MS_Description

Upline View for Unllevel Tree

Columns

SQL Script

CREATE view [dbo].[UniLevelUpline]

as

Select UplineCustomerID = tp.CustomerID,

t.CustomerID,

t.SponsorID,

Level = t.NestedLevel - tp.NestedLevel,

t.Placement,

IndentedSort = t.lft

From UniLevelTree tp

inner Join UniLevelTree t

on t.lft <= tp.lft and t.rgt >= tp.rgt

GO

EXEC sp_addextendedproperty N'MS_Description', N'Upline View for Unllevel Tree', 'SCHEMA',

N'dbo', 'VIEW', N'UniLevelUpline', NULL, NULL

GO

Uses

[dbo].[UniLevelTree]

Name

Data Type

Max Length (Bytes)

UplineCustomerID

int

CustomerID

int

SponsorID

int

Level

int

Placement

int

IndentedSort

int

Stored Procedures¶

Reporting > Programmability > Stored Procedures

Author: Jacob Golden

Page 232 of 249

Objects

Name

cache.ExpireSessions

Expire Cached Web Sessions

cache.Get

Get Cached Cart

cache.GetSession

Get Cached Web Session

cache.Purge

Purge Cached Carts

cache.Set

Insert Cart to Caching Table

cache.SetSession

Insert Web Session to Caching Table

dbo.AuthenticateCustomer

Customer Authentication for User/PasswordHash in the dbo.Customers table

dbo.CommandExecute

Used for Maintenance Functions

dbo.IndexOptimize

Index Optimization

queue.Refresh

Update Cached Cart

[cache].[ExpireSessions]¶

Reporting > Programmability > Stored Procedures > cache.Expire-

Sessions

Author: Jacob Golden

Page 233 of 249

MS_Description

Expire Cached Web Sessions

SQL Script

create procedure [cache].[ExpireSessions]

WITH EXECUTE AS OWNER

as

BEGIN

DELETE cache.Session

Where ModifiedDate < DATEADD(day,-2,Getdate())

END

GO

EXEC sp_addextendedproperty N'MS_Description', N'Expire Cached Web Sessions', 'SCHEMA', N'cache',

'PROCEDURE', N'ExpireSessions', NULL, NULL

GO

Uses

[cache].[Session]

[cache]

[cache].[Get]¶

Reporting > Programmability > Stored Procedures > cache.Get

Author: Jacob Golden

Page 234 of 249

MS_Description

Get Cached Cart

Parameters

SQL Script

create procedure [cache].[Get] (@ID nvarchar(1000))

as

BEGIN

Select ID, Data, CreatedDate, SecondsToLive, CurrentDate = GetUtcDate()

From cache.Store

where ID = @ID

END

GO

EXEC sp_addextendedproperty N'MS_Description', N'Get Cached Cart', 'SCHEMA', N'cache',

'PROCEDURE', N'Get', NULL, NULL

GO

Uses

[cache].[Store]

[cache]

Name

Data Type

Max Length (Bytes)

@ID

nvarchar(1000)

2000

[cache].[GetSession]¶

Reporting > Programmability > Stored Procedures > cache.Get-

Session

Author: Jacob Golden

Page 235 of 249

MS_Description

Get Cached Web Session

Parameters

SQL Script

create procedure [cache].[GetSession]

(

@SessionID uniqueidentifier

)

WITH EXECUTE AS OWNER

as

BEGIN

Select Data From cache.session where SessionID =@SessionID

END

GO

EXEC sp_addextendedproperty N'MS_Description', N'Get Cached Web Session', 'SCHEMA', N'cache',

'PROCEDURE', N'GetSession', NULL, NULL

GO

Uses

[cache].[Session]

[cache]

Name

Data Type

Max Length (Bytes)

@SessionID

uniqueidentifier

[cache].[Purge]¶

Reporting > Programmability > Stored Procedures > cache.Purge

Author: Jacob Golden

Page 236 of 249

MS_Description

Purge Cached Carts

SQL Script

create procedure [cache].[Purge]

as

BEGIN

DELETE cache.Store

WHERE DATEADD(ss,SecondsToLive,CreatedDate) < GetUtcDate()

END

GO

EXEC sp_addextendedproperty N'MS_Description', N'Purge Cached Carts', 'SCHEMA', N'cache',

'PROCEDURE', N'Purge', NULL, NULL

GO

Uses

[cache].[Store]

[cache]

[cache].[Set]¶

Reporting > Programmability > Stored Procedures > cache.Set

Author: Jacob Golden

Page 237 of 249

MS_Description

Insert Cart to Caching Table

Parameters

SQL Script

create procedure [cache].[Set] ( @ID nvarchar(1000), @SecondsToLive bigint, @Data

nvarchar(max) )

as

BEGIN

Update cache.Store Set Data = @Data, SecondstoLive=@SecondsToLive, Created-

Date=GetUtcDate()

Where ID=@ID

if @@Rowcount = 0

begin

Insert [cache].Store

values(@ID, @Data, GetUtcDate(), @SecondsToLive)

end

END

GO

EXEC sp_addextendedproperty N'MS_Description', N'Insert Cart to Caching Table', 'SCHEMA',

N'cache', 'PROCEDURE', N'Set', NULL, NULL

GO

Uses

[cache].[Store]

[cache]

Name

Data Type

Max Length (Bytes)

@ID

nvarchar(1000)

2000

@SecondsToLive

bigint

@Data

nvarchar(max)

max

[cache].[SetSession]¶

Reporting > Programmability > Stored Procedures > cache.Set-

Session

Author: Jacob Golden

Page 238 of 249

MS_Description

Insert Web Session to Caching Table

Parameters

SQL Script

create procedure [cache].[SetSession]

(

@SessionID uniqueidentifier,

@Data nvarchar(max)

)

WITH EXECUTE AS OWNER

as

BEGIN

Update cache.Session Set Data = @Data, ModifiedDate=GetDate()

Where SessionID=@SessionID

if @@Rowcount = 0 begin

Insert [cache].Session

values(@SessionID, GetDate(), @Data)

end

END

GO

EXEC sp_addextendedproperty N'MS_Description', N'Insert Web Session to Caching Table', 'SCHEMA',

N'cache', 'PROCEDURE', N'SetSession', NULL, NULL

GO

Uses

[cache].[Session]

[cache]

Name

Data Type

Max Length (Bytes)

@SessionID

uniqueidentifier

@Data

nvarchar(max)

max

[dbo].[AuthenticateCustomer]¶

Reporting > Programmability > Stored Procedures >

dbo.AuthenticateCustomer

Author: Jacob Golden

Page 239 of 249

MS_Description

Customer Authentication for User/PasswordHash in the dbo.Customers table

Parameters

SQL Script

CREATE procedure [dbo].[AuthenticateCustomer]

(

@LoginName nvarchar(500),

@Password nvarchar(500)

)

as

begin

SET NOCOUNT ON

DECLARE

@PasswordHash varbinary(50),

@Salt binary(8),

@TestPasswordHash varbinary(50),

@CanLogin bit,

@CustomerID int,

@Combined varbinary(max),

@IsAuthenticated bit = 0,

@FirstName nvarchar(50) = '',

@LastName nvarchar(50) = ''

SELECT

@PasswordHash = PasswordHash,

@CustomerID = CustomerID,

@FirstName = FirstName,

@LastName = LastName,

@CanLogin = CanLogin

FROM Customers

WHERE LoginName=@LoginName

IF @PasswordHash is not null and @CanLogin = 1 BEGIN

set @Salt = substring(@PasswordHash, 21, 8)

set @Combined = cast(@Password as varbinary(max)) + @Salt

Name

Data Type

Max Length (Bytes)

@LoginName

nvarchar(500)

1000

@Password

nvarchar(500)

1000

Section 240¶

Reporting > Programmability > Stored Procedures >

dbo.AuthenticateCustomer

Author: Jacob Golden

Page 240 of 249

set @TestPasswordHash = hashbytes('SHA1', @combined) + @Salt

set @IsAuthenticated = case when @PasswordHash = @TestPasswordHash then 1 else 0

end

END

IF @IsAuthenticated=1 BEGIN

Select @CustomerID AS CustomerID, @FirstName AS FirstName, @LastName AS LastName

END ELSE BEGIN

Select CustomerID, FirstName, LastName From Customers Where 1 = 0

END

GO

EXEC sp_addextendedproperty N'MS_Description', N'Customer Authentication for User/PasswordHash in

the dbo.Customers table', 'SCHEMA', N'dbo', 'PROCEDURE', N'AuthenticateCustomer', NULL, NULL

GO

Uses

[dbo].[Customers]

[dbo].[CommandExecute]¶

Reporting > Programmability > Stored Procedures > dbo.Command-

Execute

Author: Jacob Golden

Page 241 of 249

MS_Description

Used for Maintenance Functions

Uses

[dbo].[CommandLog]

Used By

[dbo].[IndexOptimize]

[dbo].[IndexOptimize]¶

Reporting > Programmability > Stored Procedures > dbo.Index-

Optimize

Author: Jacob Golden

Page 242 of 249

MS_Description

Index Optimization

Uses

[dbo].[CommandExecute]

[queue]

[queue].[Refresh]¶

Reporting > Programmability > Stored Procedures > queue.Refresh

Author: Jacob Golden

Page 243 of 249

MS_Description

Update Cached Cart

Parameters

SQL Script

CREATE procedure [queue].[Refresh]

(

@ManualStartDate datetime = null

)

as

set xact_abort on

begin tran

/********************************

* If this is the first time, pull 24 hrs

*********************************/

if @ManualStartDate is null and not exists (select * from queue.Controller) begin

set @ManualStartDate = getdate()-1

end

/********************************

* If this is a reset, let's reset

*********************************/

if @ManualStartDate is not null begin

update queue.Controller set ModifiedDate = @ManualStartDate

where tablename = 'Customers'

if @@rowcount = 0

insert queue.controller values('Customers', @ManualStartDate)

update queue.Controller set ModifiedDate = @ManualStartDate

where tablename = 'CustomerAccounts'

if @@rowcount = 0

Name

Data Type

Max Length (Bytes)

@ManualStartDate

datetime

Section 244¶

Reporting > Programmability > Stored Procedures > queue.Refresh

Author: Jacob Golden

Page 244 of 249

insert queue.controller values('CustomerAccounts', @ManualStartDate)

update queue.Controller set ModifiedDate = @ManualStartDate

where tablename = 'CustomerSites'

if @@rowcount = 0

insert queue.controller values('CustomerSites', @ManualStartDate)

update queue.Controller set ModifiedDate = @ManualStartDate

where tablename = 'Orders'

if @@rowcount = 0

insert queue.controller values('Orders', @ManualStartDate)

end

/********************************

* Clear out existing tables

*********************************/

--> Orders

if object_id('queue.orders') is not null drop table queue.orders

select * into queue.Orders from orders where OrderID is null

alter table queue.Orders add constraint PK_Orders_1 primary key clustered (OrderID)

--> OrderDetail

if object_id('queue.OrderDetails') is not null drop table queue.OrderDetails

select * into queue.OrderDetails from OrderDetails where OrderID is null

alter table queue.OrderDetails add constraint PK_OrderDetail_1 primary key clustered (Order-

ID, OrderLine)

--> Payments

if object_id('queue.Payments') is not null drop table queue.Payments

select * into queue.Payments from Payments where PaymentID is null

alter table queue.Payments add constraint PK_Payments_1 primary key clustered (PaymentID)

--> Customers

if object_id('queue.Customers') is not null drop table queue.Customers

select * into queue.Customers from Customers where CustomerID is null

alter table queue.Customers add constraint PK_Customers_1 primary key clustered (CustomerID)

--> CustomerAccounts

if object_id('queue.CustomerAccounts') is not null drop table queue.CustomerAccounts

select * into queue.CustomerAccounts from CustomerAccounts where CustomerID is null

alter table queue.CustomerAccounts add constraint PK_CustomerAccounts_1 primary key clustered

(CustomerID)

--> CustomerAccounts

if object_id('queue.CustomerSites') is not null drop table queue.CustomerSites

select * into queue.CustomerSites from CustomerSites where CustomerID is null

alter table queue.CustomerSites add constraint PK_CustomerSites_1 primary key clustered

(CustomerID)

Section 245¶

Reporting > Programmability > Stored Procedures > queue.Refresh

Author: Jacob Golden

Page 245 of 249

/********************************

* Pull in new data

*********************************/

exec sp_executesql N'

insert queue.orders

select * from dbo.orders where ModifiedDate > (select modifieddate from queue.Controller

where tablename = ''orders'')

insert queue.OrderDetails

select * from dbo.orderdetails where orderid in (select orderid from queue.orders)

insert queue.Payments

select * from dbo.Payments where orderid in (select orderid from queue.orders)

insert queue.Customers

select * from dbo.Customers where ModifiedDate > (select modifieddate from queue.Controller

where tablename = ''customers'')

--> Now pull in any customers for orders not in the set already

insert queue.Customers

select * from dbo.Customers where CustomerID in (select CustomerID from queue.orders) and

CustomerID not in (select customerid from queue.Customers)

insert queue.CustomerAccounts

select * from dbo.CustomerAccounts where ModifiedDate > (select modifieddate from

queue.Controller where tablename = ''customeraccounts'')

insert queue.CustomerSites

select * from dbo.CustomerSites where ModifiedDate > (select modifieddate from

queue.Controller where tablename = ''customersites'')

'

/********************************

* Update modified dates

*********************************/

declare @maxModified datetime

select @maxModified = max(modifieddate) from queue.orders

if @maxModified is not null begin

update queue.Controller set ModifiedDate = @maxModified

where tablename = 'Orders'

if @@rowcount = 0

insert queue.controller values('Orders', @maxModified)

end

set @maxModified = null

select @maxModified = max(modifieddate) from queue.customers

if @maxModified is not null begin

update queue.Controller set ModifiedDate = @maxModified

where tablename = 'Customers'

if @@rowcount = 0

insert queue.controller values('Customers', @maxModified)

Section 246¶

Reporting > Programmability > Stored Procedures > queue.Refresh

Author: Jacob Golden

Page 246 of 249

end

set @maxModified = null

select @maxModified = max(modifieddate) from queue.customeraccounts

if @maxModified is not null begin

update queue.Controller set ModifiedDate = @maxModified

where tablename = 'CustomerAccounts'

if @@rowcount = 0

insert queue.controller values('CustomerAccounts', @maxModified)

end

set @maxModified = null

select @maxModified = max(modifieddate) from queue.customersites

if @maxModified is not null begin

update queue.Controller set ModifiedDate = @maxModified

where tablename = 'CustomerSites'

if @@rowcount = 0

insert queue.controller values('CustomerSites', @maxModified)

end

commit tran

/********************************

* Add cache.Store

*********************************/

GO

EXEC sp_addextendedproperty N'MS_Description', N'Update Cached Cart', 'SCHEMA', N'queue',

'PROCEDURE', N'Refresh', NULL, NULL

GO

Uses

[dbo].[CustomerAccounts]

[dbo].[Customers]

[dbo].[CustomerSites]

[dbo].[OrderDetails]

[dbo].[Orders]

[dbo].[Payments]

[queue]

[queue].[Controller]

Scalar-valued Functions¶

Reporting > Programmability > Functions > Scalar-valued Functions

Author: Jacob Golden

Page 247 of 249

Objects

Name

dbo.GetLocalDate

Returns Date in Central US Time

dbo.VerifyCustomerTempAuthHash

Verifies Customer Password Hash

[dbo].[GetLocalDate]¶

Reporting > Programmability > Functions > Scalar-valued Functions

dbo.GetLocalDate

Author: Jacob Golden

Page 248 of 249

MS_Description

Returns Date in Central US Time

SQL Script

CREATE FUNCTION [dbo].[GetLocalDate] ()

returns DATETIME

as

begin

Declare @gd as DATETIME = getUTCdate()

DECLARE @D AS datetimeoffset

SET @D = CONVERT(datetimeoffset, @gd) AT TIME ZONE 'Central Standard Time'

RETURN CONVERT(datetime, @D);

end

GO

EXEC sp_addextendedproperty N'MS_Description', N'Returns Date in Central US Time', 'SCHEMA',

N'dbo', 'FUNCTION', N'GetLocalDate', NULL, NULL

GO

Used By

[sync].[CommissionRunSyncState]

[upload].[OrderTrackingQueue]

[dbo].[VerifyCustomerTempAuthHash]¶

Reporting > Programmability > Functions > Scalar-valued Functions

dbo.VerifyCustomerTempAuthHash

Author: Jacob Golden

Page 249 of 249

MS_Description

Verifies Customer Password Hash

Parameters

SQL Script

CREATE

FUNCTION [dbo].[VerifyCustomerTempAuthHash]

(

@PlainText NVARCHAR(MAX),

@Hash VARBINARY(80)

)

RETURNS BIT

AS

BEGIN

DECLARE @salt BINARY(8)

SET @salt = substring(@hash, 65, 8)

DECLARE @combined VARBINARY(MAX)

SET @combined = CAST(@PlainText AS VARBINARY(MAX)) + @salt

IF @Hash = hashbytes('SHA2_512', @combined) + @Salt RETURN 1

RETURN 0

END

GO

EXEC sp_addextendedproperty N'MS_Description', N'Verifies Customer Password Hash', 'SCHEMA',

N'dbo', 'FUNCTION', N'VerifyCustomerTempAuthHash', NULL, NULL

GO

Name

Data Type

Max Length (Bytes)

@PlainText

nvarchar(max)

max

@Hash

varbinary(80)