Data Dictionary for OrderCalc SDK

Introduction¶

See the sections below for configuration steps in the Exigo Portal.

Reporting Services Data Dictionary¶

This data dictionary shows the current mapping (as of March 1, 2016) of Reporting Services

(SyncSQL) tables to their Core DB production counterparts. This document is intended solely for

clients who are using the SyncSQL service and are also doing their own OrderCalc development

using the OrderCalc Visual Studio SDK plug-in.

This document is broken down by table and follows the same format for each section:

Unique ID

Production

Reporting

Notes

Primary Key

Name

Names

Etc…

…

The table name sections are outlined in this document in the following order:

•

Customers

•

CustomerTypes

•

CustomerExtendedGroups

•

CustomerExtendedDetails

•

OrderStatuses

•

AutoOrders

•

AutoOrderDetails

•

AutoOrderPaymentTypes

•

Orders

•

OrderDetails

•

Payments

•

Items

•

ItemPrices

CUSTOMERS¶

Unique ID

Production

Reporting

Notes

Primary Key

CustomerID

FirstName

MiddleName

LastName

Company

CustomerTy

CustomerTypeID

CustomerStatusTy

CustomerStatusID

Email

Phone

Phone2

MobilePhone

Fax

Address1

MainAddress1

Address2

MainAddress2

Address3

MainAddress3

City

MainCity

State

MainState

Zip

MainZip

Country

MainCountry

County

MainCounty

MailAddress1

MailAddress2

MailAddress3

MailCity

MailState

MailZip

MailCountry

MailCounty

OtherAddress1

OtherAddress2

OtherAddress3

OtherCity

OtherState

OtherZip

OtherCountry

OtherCounty

CanLogin

PayableTo

PayableToName

WarehouseID

DefaultWarehouseID

PayableTy

PayableTypeID

Checkthreshhold

CheckThreshold

LanguageID

Gender

(CUSTOMERS CONTINUED)¶

TaxID

TaxCode

According to the code,

there are different options

for masking TaxId based

on if your reporting

database accepts sensitive

data.

Unique ID

Production

Reporting

Notes

TaxTy

TaxCodeTypeID

SalesTaxExempt

IsSalesTaxExempt

SalesTaxID

SalesTaxCode

According to the code,

there are different options

for masking SalesTaxId

based on if your reporting

database accepts sensitive

data.

SalesTaxExemptExpireDate

VatRegistration

BinaryPlacementTy

BinaryPlacementTypeID

UseBinaryHoldingTank

IsInBinaryHoldingTank

subscribedate

IsEmailSubscribed

The subscribedate is

only part of the formula

To find out if they have a

subscription date.

SubscribeIP

EmailSubscribeIP

Notes

Field1

Field2

Field3

Field4

Field5

Field6

Field7

Field8

Field9

Field10

Field11

Field12

Field13

Field14

Field15

Date1

Date2

Date3

Date4

Date5

EntryDate

CreatedDate

CUSTOMERTYPES¶

ModifiedDate

CreatedBy

ModifiedBy

Unique ID

Production

Reporting

Notes

Primary Key

CustomerTy

CustomerTypeID

Description

CustomerTypeDescription

PriceTy

PriceTypeID

CUSTOMEREXTENDEDGROUPS

Unique ID

Production

Reporting

Notes

Primary Key

ExtendedGroupID

CustomerExtendedGroupID

Description

CustomerExtendedGroupDescription

Field1

Field1Name

Field2

Field2Name

Field3

Field3Name

Field4

Field4Name

Field5

Field5Name

Field6

Field6Name

Field7

Field7Name

Field8

Field8Name

Field9

Field9Name

Field10

Field10Name

Field11

Field11Name

Field12

Field12Name

Field13

Field13Name

Field14

Field14Name

Field15

Field15Name

Field16

Field16Name

Field17

Field17Name

Field18

Field18Name

Field19

Field19Name

Field20

Field20Name

Field21

Field21Name

Field22

Field22Name

Field23

Field23Name

Field24

Field24Name

Field25

Field25Name

Field26

Field26Name

Field27

Field27Name

Field28

Field28Name

Field29

Field29Name

CUSTOMEREXTENDEDDETAILS¶

Field30

Field30Name

Unique ID

Production

Reporting

Notes

Primary Key

CustomerExtendedID

CustomerExtendedDetailID

ExtendedGroupID

CustomerExtendedGroupID

Field1

Memory optimized for

125 characters in size

Field2

Field3

Field4

Field5

Field6

Field7

Field8

Field9

Field10

Field11

Field12

Field13

Field14

Field15

Field16

Field17

Field18

Field19

Field20

Field21

Field22

Field23

Field24

Field25

Field26

Field27

Field28

Field29

Field30

ModifiedDate

ORDERSTATUSES

Unique ID¶

Production

Reporting

Notes

Primary Key

OrderStatusTy

OrderStatusID

Description

OrderStatusDescription

AUTOORDERS

Unique ID

Production

Reporting

Notes

Primary Key

AutoOrderID

AutoOrderStatusTy

AutoOrderStatusID

FrequencyTy

FrequencyTypeID

StartDate

StopDate

LastRunDate

NextRunDate

CancelledDate

CurrencyCode

WarehouseID

ShipMethodID

AutoOrderPaymentTy

AutoOrderPaymentTypeID

AutoOrderProcessTy

AutoOrderProcessTypeID

FirstName

Address source resolves

the customer's main

address, mail address, or

auto order address.

MiddleName

LastName

NameSuffix

Company

Address1

Address2

Address3

City

State

Zip

Country

County

Email

Phone

Notes

Memory optimized for

100 characters in size

Total

SubTotal

TotalTax

TaxTotal

Shipping

ShippingTotal

Discount

DiscountTotal

BusinessVolume

BusinessVolumeTotal

CommissionableVolume

CommissionableVolumeTotal

Description

AutoOrderDescription

(AUTOORDERS CONTINUED)¶

Other11

Memory optimized for

100 characters in

size

Other12

Other13

Other14

Other15

Other16

Other17

Other18

Other19

Other20

Unique ID

Production

Reporting

Notes

CreatedDate

ModifiedDate

CreatedBy

ModifiedBy

AUTOORDERDETAILS

Unique ID

Production

Reporting

Notes

Primary Key

AutoOrderID

Primary Key

OrderLine

ItemID

ItemCode

Description

ItemDescription

Qty

Quantity

PriceEach

PriceExt

PriceTotal

BVEach

BusinessVolumeEach

BusinessVolume

CVEach

CommissionableVolumeEach

CommissionableVolume

ManualPriceEach

PriceEachOverride

ManualTaxablePriceEach

TaxableEachOverride

ManualShippingPriceEach

ShippingPriceEachOverride

ManualBVEach

BusinessVolumeEachOverride

ManualCVEach

CommissionableVolumeEachOverride

DynamicKitItemID

ParentItemID

Reference1

AUTOORDERPAYMENTTYPES

Unique ID

Production

Reporting

Notes

ORDERS¶

Primary Key

AutoOrderPaymentTy

AutoOrderPaymentTypeID

Description

AutoOrderPaymentTypeDescription

Unique ID

Production

Reporting

Notes

Primary Key

OrderID

OrderStatusTy

OrderStatusID

OrderStatusId is an

index

OrderTy

OrderTypeID

PriceTy

PriceTypeID

FirstName

MiddleName

LastName

NameSuffix

Company

Address1

Address2

Address3

City

State

Zip

Country

County

Email

Phone

Notes

Total

SubTotal

TotalTax

TaxTotal

Shipping

ShippingTotal

Discount

DiscountTotal

DiscountPercent

Weight

WeightTotal

BusinessVolume

BusinessVolumeTotal

CommissionableVolume

CommissionableVolumeTotal

TrackingNumber

TrackingNumber1

TrackingNumber2

TrackingNumber3

TrackingNumber4

TrackingNumber5

(ORDERS CONTINUED)¶

Other1

Other1Total

Other2

Other2Total

Other3

Other3Total

Other4

Other4Total

Other5

Other5Total

Other6

Other6Total

Other7

Other7Total

Other8

Other8Total

Other9

Other9Total

Other10

Other10Total

Unique ID

Production

Reporting

Notes

ShippingTax

OrderTax

TotalFedTax

FedTaxTotal

TotalStateTax

StateTaxTotal

FedShippingTax

StateShippingTax

CityShippingTax

CityLocalShippingTax

CountyShippingTax

CountyLocalShippingTax

Other11

Other12

Other13

Other14

Other15

Other16

Other17

Other18

Other19

Other20

IsCommissionable

AutoOrderID

ReplacementOrderID

ParentOrderID

BatchID

DeclineCount

TransferToID

TransferToCustomerID

PartyID

PartyId is an Index

WebCarrierID

WebCarrierID1

Logic resolves if there is

a web carrier ID

ORDERDETAILS¶

WebCarrierID2

WebCarrierID3

WebCarrierID4

WebCarrierID5

ShippedDate

CreatedDate

LockedDate

ModifiedDate

CreatedBy

ModifiedBy

SuppressPackSlipPrice

ReturnCategoryID

ReplacementCategoryID

Unique ID

Production

Reporting

Notes

Primary Key

OrderID

Primary Key

OrderLine

ItemID

ItemCode

Description

ItemDescription

Qty

Quantity

PriceEach

PriceExt

PriceTotal

CombinedTax

Tax

WeightEach

Weight

This is the combined

weight of the order.

BVEach

BusinessVolumeEach

BusinessVolume

CVEach

CommissionableVolumeEach

CommissionableVolume

Other1Each

Other1

Other2Each

Other2

Other3Each

Other3

Other4Each

Other4

Other5Each

Other5

Other6Each

Other6

Other7Each

Other7

Other8Each

PAYMENTS¶

Other8

Other9Each

Other9

Other10Each

Other10

DynamicKitItemID

ParentItemID

Taxable

FedTax

StateTax

CityTax

CityLocalTax

CountyTax

CountyLocalTax

ManualTax

IsStateTaxOverride

Reference1

Unique ID

Production

Reporting

Notes

Primary Key

PaymentID

Primary Key

CustomerID

PaymentTy

PaymentTypeID

PaymentDate

Amount

CurrencyCode

WarehouseID

BillingName

CreditCardTy

CreditCardTypeID

CreditNumber

CreditCardNumber

CreditAuthorization

AuthorizationCode

CheckNumber

Memo

DLNumber

DriversLicenseNumber

MerchantTy

MerchantTypeID

MerchantTransactionKey

PointAccountID

ModifiedDate

ITEMS

Unique ID

Production

Reporting

Notes

Primary Key

ItemID

ItemCode

Description

ItemDescription

Memory optimized for

200 characters in size

(ITEMS CONTINUED)¶

ItemTy

ItemTypeID

ShortDetail

Memory optimized for

350 characters in size

ShortDetail2

ShortDetail3

ShortDetail4

LongDetail

LongDetail2

LongDetail3

LongDetail4

Notes

Memory optimized for 50

characters in size

Weight

IsVirtual

IsGroupMaster

SuppressGroupMaster

GroupDescription

Memory optimized for 40

characters in size

GroupMembersDescription

AllowAutoOrder

AllowOnAutoOrder

HideFromSearch

Unique ID

Production

Reporting

Notes

AvailableAllCountryRegions

TinyPic

TinyImageName

Memory optimized for

50 characters in

size

SmallPic

SmallImageName

LargePic

LargeImageName

Field1

Memory optimized for

40 characters in size

Field2

Field3

Field4

Field5

Field6

Field7

Field8

Field9

Field10

OtherCheck1

OtherCheck2

OtherCheck3

OtherCheck4

OtherCheck5

Auto1

Memory optimized for

50 characters in size

Auto2

Auto3

ModifiedDate

ITEMPRICES

Unique ID¶

Production

Reporting

Notes

Primary Key

ItemID

Primary Key

CurrencyCode

Primary Key

PriceTy

PriceTypeID

Price

CommissionableVolume

BusinessVolume

TaxablePrice

ShippingPrice

Other1Price

Other2Price

Other3Price

Other4Price

Other5Price

Other6Price

Other7Price

Other8Price

Other9Price

Other10Price