Steve Rubenstein's Recurring Billing
Database
Version 1.1
Last Updated October 15, 2013
 Column Name Key? Column
Type
Column
Size
Default
Value
 Column Description
avAddress - Stores billing, shipping and other addresses for users and companies. Archived all old email addresses.
addressIDPrimaryint  Identity integer primary key for address.
companyIDForeignint 0ID of the company the address applies to.
userIDForeignint 0ID of the addresss the user applies to.
userID_authorForeignint 0User who created/updated this address.
addressID_parentForeignint 0ID of address from which this address was updated. Equals 0 if new address and not updated version.
addressID_trend int 0ID of address from which this address was originally based. Equals addressID if new address and not updated version.
addressName varchar100''Name of contact person at address or customer internal reference name for the address.
addressDescription varchar255 Optional note by user about this address in conjunction with name.
addressTypeShipping bit 0Yes/no indicator for whether this address is shipping address.
addressTypeBilling bit 0Yes/no indicator for whether this address is billing address.
address varchar100''First line of address.
address2 varchar100''Second line of address.
address3 varchar100''Third line of address.
city varchar50''Address city.
state varchar50''Address state/province.
zipCode varchar15''Address zip/postal code. Does not include +4, which is stored in separate field so that zip code can be searched and used for shipping information.
zipCodePlus4 varchar4''Additional 4 numbers in US zip codes.
county varchar100''Address county (not country).
country varchar100''Address country. Should not be blank, but if it is, USA is implied.
regionIDForeignint 0ID of region where address is located.
addressStatus bit 0Yes/no indicator for whether this is a current address or archived address.
addressVersion smallint 0Version number of this particular address where the original version is 1.
addressDateCreated smalldatetime getdate()Date/time address was created.
addressDateUpdated smalldatetime getdate()Date/time address was updated or was no longer the current address.
avAffiliate - Identifies users/companies that are partners in that they refer customers and get credit for it. Similar to cobrand but without the cobranded site.
affiliateIDPrimaryint  Indentity field primary key for each affiliate.
companyID_authorForeignint 0Company the affiliate belongs to.
userID_authorForeignint 0User who added the affiliate.
companyIDForeignint 0Company ID of the affiliate.
userIDForeignint 0Primary user contact of affiliate.
affiliateCode varchar25''Unique code that identifies affiliate.
affiliateName varchar255''Internal reference name of affiliate. Probably company name.
affiliateURL varchar100''URL of affiliate site.
affiliateStatus bit 0Yes/no indicator for whether affiliate is currently active.
affiliateID_custom varchar50''Custom affiliate ID as defined by company for internal purposes.
affiliateIsExported bit  Indicates whether record has been exported. Null = not exported; 0 = Exported but awaiting confirmation the record was imported; 1 = Exported and successfully imported.
affiliateDateExported smalldatetime  Date/time that record export status was updated.
affiliateDateCreated smalldatetime getdate()Date/time company was added as an affiliate.
affiliateDateUpdated smalldatetime getdate()Date/time that the affiliate was last updated.
avBank - Stores customer bank information used for direct withdrawals from bank accounts, such as e-check or ACH transactions.
bankIDPrimaryint  Identity field primary key identifying each bank account information.
userIDForeignint 0User who is the main contact for this bank account or whose personal bank account it is.
companyIDForeignint 0ID of company that bank account belongs to.
userID_authorForeignint 0User who added this bank account record.
bankName varchar255''Name of bank or financial institution.
bankBranch varchar255''Name/location of bank branch.
bankBranchCity varchar50''City of bank branch.
bankBranchState varchar50''State of bank branch.
bankBranchCountry varchar100''Country of bank branch.
bankBranchContactName varchar100''Name of contact person at bank branch.
bankBranchPhone varchar25''Phone number of bank branch.
bankBranchFax varchar25''Fax number of bank branch.
bankRoutingNumber varchar100''Routing number of bank for electronic transactions.
bankAccountNumber varchar100''Account number of customer at bank.
bankAccountName varchar100''Name on the bank account, typically user or company name.
bankCheckingOrSavings bit  Indicator of whether bank account is checking (0) or savings (1). Blank implies other (bankAccountType).
bankPersonalOrCorporate bit 0Indicator of whether bank account is personal (0) or corporate (1).
bankDescription varchar255''Description of bank account, e.g., personal checking (for internal use).
bankAccountType varchar50''Type of bank account. Designed for use if not checking or savings type.
bankStatus bit 0Yes/no indicator of whether bank account is currently active.
bankRetain bit 0Indicates whether to store bank account information after it is used.
addressIDForeignint 0ID of address that is address of record for bank account.
bankDateCreated smalldatetime getdate()Date/time when bank account information was created.
bankDateUpdated smalldatetime getdate()Date/time when bank info was last updated.
avCategory - Stores category information. Categories are currently used for products only, but can easily be expanded to support categorizing other listings.
categoryIDPrimaryint  Identity field primary key for each category.
companyIDForeignint 0Company the category applies to.
userIDForeignint 0User who created the category.
categoryCode varchar50''Code to identify category. Used to designate a "special" category without requiring the ID.
categoryName varchar255''Name of category.
categoryDescription varchar255''Description of category. For internal purposes only.
categoryTitle varchar255''Name of category as displayed to users.
categoryOrder int 0Order in which category is listed, including sub-category listings. Default is alphabetical. May allow custom order at a later time.
categoryOrder_manual int 0Manual order in which categories are listed, not the normal alphabetical order within category levels.
categoryStatus bit 0Indicator of whether the category is currently active.
categoryID_parent int 0ID of parent category, if category is subcategory. If primary category, value = 0.
categoryID_parentList varchar500''Comma-delimited list identifying category path to get to this subcategory. If primary category, is blank.
categoryLevel tinyint 0Level number of category, beginning at 1 for primary category. Supports maximum of 255 category levels.
categoryHasChildren bit 0Yes/no indicator for whether category contains any subcategories.
categoryAcceptListing bit 0Yes/no indicator for whether category allows products to be listed in category, or whether category contains only subcategories.
categoryIsListed bit 0Indicates whether category is listed on site in main list of categories.
categoryHasCustomPrice bit 0Indicates whether there are any custom pricing options for this category.
categoryViewCount int 0Number of times category page has been viewed. For shopping cart more than billing system.
categoryItemsPerPage int 0Number of items to list per page in this category.
categoryNumberOfPages int 0Indicates maximum number of pages used to display items in category.
headerFooterID_headerForeignint 0ID of header in table that stores header and footer separately. Is 0 if no header.
headerFooterID_footerForeignint 0ID of footer in table that stores header and footer separately. Is 0 if no footer.
templateFilename varchar50''Template file used to display category.
categoryDateCreated smalldatetime getdate()Date/time category was created.
categoryDateUpdated smalldatetime getdate()Date/time category was last updated.
avChecklist - Stores checklist items for each company, checklist category and checklist type.
checklistIDPrimaryint  Identity field primary key for each checklist item.
userIDForeignint 0User who created the checklist item.
companyIDForeignint 0Company that the checklist item is for.
checklistName varchar1000''Name or text of the checklist item displayed in the checklist.
checklistOrder smallint 0Order in which the checklist item is listed within the checklist for this company of this checklist type (based on avChecklistTarget).
checklistStatus bit 0Yes/no indicator for whether checklist item is still active.
checklistCategoryIDForeignint 0Category ID of checklist item.
checklistCategoryOrder smallint 0Order of checklist category for this checklist target. Redundant data but used to simplify ordering.
checklistDateCreated smalldatetime getdate()Date/time when checklist item was created.
checklistDateUpdated smalldatetime getdate()Date/time when checklist item was last updated.
avChecklistCategory - Stores checklist categories. For each company and checklist target, checklist items can be grouped into categories.
checklistCategoryIDPrimaryint  Identify field primary key for checklist category.
userIDForeignint 0User who created checklist category.
companyIDForeignint 0Company the checklist category is for.
checklistCategoryType varchar25''Type of checklist category. Currently unused unless populated by company when creating category.
checklistCategoryName varchar255''Name of checklist category. Should be unique for company and checklist target.
checklistCategoryOrder smallint 0Order in which checklist category is listed (not necessarliy alphabetical) for this company and checklist target.
checklistCategoryMultiple bit 0For checklist categories with multiple columns, indicates whether user can check multiple columns or just one. Ignored if only one column.
checklistCategoryDateCreated smalldatetime getdate()Date/time checklist category was created.
checklistCategoryDateUpdated smalldatetime getdate()Date/time checklist category was last updated.
avChecklistCategoryColumn - Stores columns for each checklist category. Enables more than simply a single "yes/no" option for each checklist item.
checklistCategoryColumnIDPrimaryint  Identity field primary key for each checklist category column.
checklistCategoryIDForeignint 0ID of checklist category the column is for.
checklistCategoryColumnName varchar255''Name of the checklist category column that is displayed in the header.
checklistCategoryColumnDescription varchar255''Description of column. Displayed in separate chart to better explain the short column name.
checklistCategoryColumnOrder tinyint 0Order in which the column is listed. Maximum of 255 columns per checklist category.
checklistCategoryColumnStatus bit 0Indicator of whether column is currently active.
checklistCategoryColumnDateCreated smalldatetime getdate()Date the checklist category column was created.
checklistCategoryColumnDateUpdated smalldatetime getdate()Date/time the checklist category column was last updated.
avChecklistCategoryGroup - For each checklist category, assign permissions to each group. Cannot assign permissions at user level.
checklistCategoryGroupIDPrimaryint  Identity field primary key for group permissions for each checklist category.
checklistCategoryIDForeignint 0ID of checklist category.
groupIDForeignint 0ID of group of users.
checklistCategoryGroupPermissionBinaryTotal int 0Total binary number of permissions for checklist category / group combination for this permission type.
permissionCategoryIDForeignint 0ID of permission type.
avChecklistCategoryTarget - For each checklist category, determines which target types the checklist category is used for, e.g., users, companies, invoices, etc.
checklistCategoryIDPrimaryint  Half of primary key - checklist category ID.
primaryTargetIDPrimaryint  Half of primary key - target type of checklist (user, company, invoice, etc.)
userIDForeignint 0User who added the target type for checklist category.
checklistCategoryTargetName varchar255''Internal reference name of checklist / target type combination.
checklistCategoryTargetStatus bit 0Indicator of whether category checklist is currently used for this target type.
checklistCategoryTargetDateCreated smalldatetime getdate()Date/time when target type was added for this checklist category.
checklistCategoryTargetDateUpdated smalldatetime getdate()Date/time when target type was last updated for this checklist category.
avChecklistTarget - Stores when each checklist item / column was updated for each checklist target (user, company, invoice, etc.). Stores each action, including checking, un-checking or updating comment.
checklistTargetIDPrimaryint  Identity field primary key for each checklist target.
userIDForeignint 0User who checked this checklist item for this target.
checklistIDForeignint 0Checklist item that was checked.
checklistCategoryColumnIDForeignint 0Checklist category column that was checked for this checklist item.
checklistTargetStatus bit 0Indicates whether this is the current status of the checklist/column combination for this target, or whether it has been updated by another user.
checklistTargetCheckedOffOrOn bit 0Indicates whether this record stores if the checklist item / column combination was checked or unchecked by this user.
checklistTargetComment varchar100''Comment entered by user who checked this item/column.
primaryTargetIDForeignint 0Target type of this checklist item/column, i.e.. user, invoice, company, etc.
targetID int 0Primary key of the target of this checklist item/column.
checklistTargetDateCreated smalldatetime getdate()Date/time when this checklist item/column was checked.
checklistTargetDateUpdated smalldatetime getdate()Date/time when checklist item/column was unchecked or rechecked by another user, changing status.
avCobrand - Identifies users/companies that are partners in that they refer customers and get credit for it. Cobrands may have affiliates.
cobrandIDPrimaryint  Identity field primary key for cobrand.
companyID int 0Company that is the cobrand partner.
userID int 0Primary user contact at company of cobrand partner.
companyID_author int 0Company this cobrand partner applies to.
userID_author int 0User who added this company as a cobrand partner for the company.
cobrandName varchar255''Internal reference name of cobranded site, defaults to partner company name.
cobrandCode varchar50''Unique code created by company that identifies this particular cobrand partner.
cobrandStatus bit 0Indicator of whether cobrand partner site is currently active.
cobrandImage varchar100''URL to logo of cobrand partner.
cobrandURL varchar255''URL of cobrand partner site. This is not the URL of the partner company, but the URL to reach the cobranded site.
cobrandTitle varchar100''Public title of cobranded site displayed to cobrand site visitors.
cobrandDomain varchar100''Domain of the partner company site for identifying users clicking over from their site to cobrand site.
cobrandDirectory varchar50''File system directory used to store images and other files for cobrand site. Relative directory path from main cobrand directory defined separately.
cobrandID_custom varchar50''Custom cobrand ID as defined by company for internal purposes.
cobrandIsExported bit  Indicates whether record has been exported. Null = not exported; 0 = Exported but awaiting confirmation the record was imported; 1 = Exported and successfully imported.
cobrandDateExported smalldatetime  Date/time that record export status was updated.
cobrandDateCreated smalldatetime getdate()Date/time cobrand partner site was created.
cobrandDateUpdated smalldatetime getdate()Date/time partner cobrand site was last updated.
avCommission - The various types of commissions available to compensate salespeople for each product, price, product bundle, etc.
commissionIDPrimaryint  Identity field primary key identifying each commission type.
userIDForeignint 0User who created or last updated the commission type.
companyIDForeignint 0Company the commission type applies to.
commissionName varchar50''Internal reference name of the commission type.
commissionID_custom varchar50''Custom ID of commission for internal purposes.
commissionStatus bit 0Indicator of whether commission type is currently active.
commissionAppliesToExistingProducts bit 0Indicates whether commission can be applied for all products.
commissionAppliesToCustomProducts bit 0Indicates whether commission applies to custom products created on one-off basis for line items and subscriptions.
commissionAppliesToInvoice bit 0Indicates whether commission can be applied to entire invoice/subscriber rather than individual line items/subscriptions only.
commissionAppliedStatus bit 0Indicates whether commission type has even been applied.
commissionDateBegin smalldatetime getdate()Date/time this commission type becomes active.
commissionDateEnd smalldatetime  Optional date/time commission type becomes inactive. Can be populated in advance or automatically updated when commission is no longer active.
commissionID_parentForeignint 0ID of commission from which this commission was copied via either copy or update. Ensures each commission is archived and not updated.
commissionID_trend int 0ID of commission that was original version, which could be several layers above parent ID. For reporting purposes. For original version, is commissionID.
commissionIsParent bit 0Indicates whether commission has been updated, and thus copied. Does not apply if commission is manually copied.
commissionHasMultipleStages bit 0Indicates whether commission has multiple stages, i.e, time periods. Used for subscriptions only.
commissionDescription varchar255''Description of commission for internal purposes.
commissionPeriodOrInvoiceBased bit 0Indicates whether commissions are calculated based on sum of invoices in a time period (0) or individual invoices (1).
commissionPeriodIntervalType varchar5''Time period used to calculate invoice: m (monthly) or q (quarterly). Blank if commissions are calculated on invoice basis.
commissionDateCreated smalldatetime getdate()Date/time when commission type was created.
commissionDateUpdated smalldatetime getdate()Date/time when commisson was updated, archived or made inactive.
avCommissionCategory - Stores the categories and price combinations that the commission does or does not apply to.
commissionCategoryIDPrimaryint  Identity field primary key for each commission / category record.
commissionIDForeignint 0ID of commission.
categoryIDForeignint 0ID of category that commission applies to.
commissionCategoryChildren bit 0Indicates whether commission applies to category children.
commissionCategoryStatus bit 0Indicates whether commission is currently active.
userIDForeignint 0ID of user who added category to commission.
commissionCategoryDateCreated smalldatetime getdate()Date/time when category commission record was created.
commissionCategoryDateUpdated smalldatetime getdate()Date/time when category commission record was last updated.
avCommissionCustomer - Specifies which salespeople receive commissions for a customer. Users must be listed. Affiliates, cobrands and vendors are implied unless listed in this table as an exception, generally a percentage less than 1.
commissionCustomerIDPrimaryint  Identity field primary key of salesperson / customer relationship.
companyID_authorForeignint 0ID of company that owns customer.
userID_authorForeignint 0ID of user who authorized target to receive commission for customer.
primaryTargetIDForeignint 0Target type that receives commission, e.g., user, affiliate, cobrand, etc.
targetID int 0ID of target that receives commission.
companyIDForeignint 0ID of customer company for which target gets paid commission.
commissionCustomerAllUsers bit 0Indicates that commission applies for all users for this customer.
commissionCustomerAllSubscribers bit 0Indicates whether commission applies to all subscribers for this customer.
commissionCustomerPercent decimal5.40Percentage of total available commission based on commission plan that this target receives.
commissionCustomerDateBegin smalldatetime getdate()Date that target first receives commission for this customer.
commissionCustomerDateEnd smalldatetime  Date that customer last receives commission for this customer.
commissionCustomerStatus bit 0Indicates whether this customer commission is active.
commissionCustomerPrimary bit 0Indicates whether this recipient of commission is the primary recipient.
commissionCustomerDescription varchar255''Optional description of salesperson / customer relationship.
commissionCustomerDateCreated smalldatetime   
commissionCustomerDateUpdated smalldatetime   
avCommissionCustomerSubscriber - For each salesperson / customer relation, stores the subscribers for which the salesperson should receive a commission based on invoice subscriber. No rows if applies to all customer subscribers.
commissionCustomerIDPrimaryint  ID of customer / salesperson relationship.
subscriberIDPrimaryint  ID of customer subscriber for which salesperson should receive commission.
avCommissionCustomerUser - For each salesperson / customer relation, stores which contacts for which the salesperson shoudl be paid, i.e., based on the invoice contact. No rows if applies to all users.
commissionCustomerIDPrimaryint  ID of customer / salesperson relationship.
userIDPrimaryint  ID of customer contact for which salesperson should receive commission.
avCommissionProduct - Stores which products and custom prices the commission does or does not apply to.
commissionProductIDPrimaryint  Identity field primary key for each commission / product record.
commissionIDForeignint 0ID of commission.
productIDForeignint 0ID of product that commission applies to.
commissionProductChildren bit 0Indicates whether commission applies to product children.
commissionProductStatus bit 0Indicates whether commission is currently active.
userIDForeignint 0ID of user who added product to commission.
commissionProductDateCreated smalldatetime getdate()Date/time when product commission record was created.
commissionProductDateUpdated smalldatetime getdate()Date/time when product commission record was last updated.
avCommissionStage - Stores the stages for a commission schedule. Multiple stages are used for subscriptions where the commissions varies over time.
commissionStageIDPrimaryint  Identity field primary key for each commission stage.
commissionID int 0ID of commission method.
commissionStageOrder tinyint 0Order in which commission stage is processed for subscriptions.
commissionStageAmount money 0Amount of commission in dollars or percentage of revenue where the percentage is a number between 0 and 1.
commissionStageDollarOrPercent bit 0Indicates whether commission amount is based on percent or dollar figure of sales amount.
commissionStageAmountMinimum money 0Minimum commission amount if using percent.
commissionStageAmountMaximum money 0Maximum commission amount if using percent.
commissionStageVolumeDiscount bit 0Indicates whether commission amount varies based on volume of quantity sold.
commissionStageVolumeDollarOrQuantity bit 0Indicates whether volume applies to dollar amount of sales or quantity.
commissionStageVolumeStep bit 0Indicates whether commission is calculated based on total quantity or sum of commissions at each step for that quantity.
commissionStageInterval smallint 0Number of intervals that the commission is used during subscription.
commissionStageIntervalType varchar5''Type of interval for how long commission stage is used during subscription, e.g., day, week, month, year.
commissionStageText varchar255''Text describing commission stage that should be added to invoice commission text.
commissionStageDescription varchar255''Internal description of commission stage.
avCommissionTarget - Stores the salespersons which receive each commission plan where a salesperson may be a user, affiliiate, cobrand, vendor, or a group of them.
commissionTargetIDPrimaryint  Identity field primary key identifying commission type and salesperson/group combination.
commissionIDForeignint 0Type of commission.
primaryTargetIDForeignint 0Target type that this commission applies to, e.g., user, company, group, affiliate, cobrand, etc.
targetID int 0ID of target type.
commissionTargetStatus bit 0Indicates whether this commission type is currently active for this salesperson or group.
userIDForeignint 0ID of user who added target to commission.
commissionTargetDateCreated smalldatetime getdate()Date/time commission target was added.
commissionTargetDateUpdated smalldatetime getdate()Date/time commission target was updated, i.e., made inactive.
avCommissionVolumeDiscount - For commission types that vary based on revenue or quantity, stores the quantity/revenue steps used to calculate variable commissions.
commissionVolumeDiscountIDPrimaryint  Identity field primary key identifying each scale for commission type.
commissionStageID int 0ID of commission stage that volume discount option is for.
commissionVolumeDiscountQuantityMinimum decimal19.40Minimum dollar amount or quantity to qualify for this step.
commissionVolumeDiscountQuantityIsMaximum bit 0Indicates whether this is the maximum value for the commission. (quantity = -1).
commissionVolumeDiscountAmount money 0Dollar amount of commission for this step.
commissionVolumeDiscountIsTotalCommission bit 0Indicates that commission is total commission for all quantity within quantity range. Enables $100 for up to 50 units, $40 for units 51-75, and $1 each for units 76 and above.
avCompany - Stores company information, including corporate client information.
companyIDPrimaryint  Identity field primary key for each company.
userIDForeignint 0Default user for company, typically user created when company was created.
companyName varchar255''Name of company.
companyDBA varchar255''DBA name of company. Not official company name, but named used for conducting business.
companyURL varchar100''URL to company corporate website.
languageIDForeigntinyint 0Language used for the company for billing and content purposes.
companyPrimary bit 0Indicates whether this is a company that is a direct user of the system rather than a customer, partner, etc. Basically, identifies the corporate client.
companyStatus bit 0Indicates whether company users have permission to log in.
cobrandIDForeignint 0ID of cobrand site that referred company.
affiliateIDForeignint 0ID of affiliate that referred company.
companyID_custom varchar50''Custom company ID as defined by primary company or cobrand.
companyID_parentForeignint 0ID of parent company if company is a subsidary or division of an existing company.
companyID_author int 0ID of company that is the customer that created this company / which customer the company belongs to.
userID_authorForeignint 0ID of user who created company. Is 0 if company's own user created company instead of customer.
companyIsAffiliate bit 0Indicates whether the company is an affiliate.
companyIsCobrand bit 0Indicates whether the company is a cobrand.
companyIsVendor bit 0Indicates whether the company is a vendor.
companyIsCustomer bit 0Indicates whether company is a customer.
companyIsTaxExempt bit 0Indicates whether company is exempt from taxes.
companyDirectory varchar25''Name of directory created for each primary companies which is used for storing header, footer, images, etc. Typically companyID + 3 random lowercase letters.
companyIsExported bit  Indicates whether record has been exported. Null = not exported; 0 = Exported but awaiting confirmation the record was imported; 1 = Exported and successfully imported.
companyDateExported smalldatetime  Date/time that record export status was updated.
companyDateCreated smalldatetime getdate()Date/time company was created.
companyDateUpdated smalldatetime getdate()Date/time company was last updated.
avContact - Stores messages sent manually via the system via Contact Management module. Also stores incoming messages submitted by customers.
contactIDPrimaryint  Identity field primary key for each contact management email/fax sent.
companyID_authorForeignint 0ID of system company that content management entry pertains to.
userID_authorForeignint 0ID of user who sent email/fax.
companyID_targetForeignint 0ID of company that message is being sent to.
userID_targetForeignint 0ID of user that email/fax was sent to.
primaryTargetIDForeignint 0Target type that this email/fax was sent in reference to, e.g., user, company, invoice, shipment etc.
targetID int 0ID of target email/fax was sent in reference to.
contactSubject varchar100''Subject of email/fax.
contactMessage text ''Body of email message or comments on fax cover page.
contactHtml bit 0Indicates whether email was sent in html format.
contactFax bit 0Indicates whether message was faxed.
contactEmail bit 0Indicates whether message was emailed.
contactByCustomer bit 0Indicates whether message was submitted by customer (rather than to). Identifies feedback or support request.
contactFromName varchar100''Email address the message was sent from.
contactReplyTo varchar100''Reply-to email address for message.
contactTo varchar255''Email address(es) message was sent to.
contactCC varchar255''Email address(es) cc'd on this email.
contactBCC varchar255''Email address(es) bcc'd on this email.
contactTopicIDForeignint 0ID of default contact management subject.
contactTemplateIDForeignint 0ID of default contact management template used to generate email.
contactID_custom varchar50''Custom ID to identify request. Used for support tickets.
contactID_orig int 0ID of original contact management entry that this entry was a reply to.
contactReplied bit 0For entries submitted by customers, indicates whether the company has replied.
contactStatus bit 0Indicates whether this contact management entry (ticket) is resolved.
contactDateSent smalldatetime  Date/time that message was sent. Necessary because messages can be saved for sending later.
contactDateCreated smalldatetime getdate()Date/time message was sent.
contactDateUpdated smalldatetime getdate()Date/time message record was last updated.
avContactTemplate - Stores default email templates that are used when sending contact management emails.
contactTemplateIDPrimaryint  Identity field primary key of email template.
companyIDForeignint 0ID of company that template belongs to.
userIDForeignint 0ID of user who created template.
primaryTargetIDForeignint 0Target type that this email template applies to, e.g., user, company, invoice, shipment etc.
languageIDForeigntinyint 0ID of language that template uses.
contactTemplateName varchar100''Reference name of email template.
contactTemplateHtml bit 0Indicates whether email template is in text or html format.
contactTemplateFromName varchar100''Name of person/company that email is from by default.
contactTemplateReplyTo varchar100''Email address that email is from by default.
contactTemplateCC varchar255''Email address(es) for which the email should be cc'd by default
contactTemplateBCC varchar255''Email address(es) for which the email should be bcc'd by default.
contactTemplateSubject varchar100''Subject of email template.
contactTemplateMessage text ''Default message of email template.
contactTemplateOrder smallint 0Order in which template is listed within each primary target type.
contactTemplateStatus bit 0Indicates whether email template is currently being used.
contactTemplateDateCreated smalldatetime getdate()Date/time that template was created.
contactTemplateDateUpdated smalldatetime getdate()Date/time that template was last updated.
avContactTopic - Stores list of topic that customers may choose from when submitting feedback or support request.
contactTopicIDPrimaryint  Identity field primary key for each default contact management topic..
companyIDForeignint 0ID of company that created topic.
userIDForeignint 0ID of user who created or last updated topic.
languageIDForeigntinyint 0ID of language.
contactTopicName varchar50''Name of topic as displayed internally.
contactTopicTitle varchar50''Name of topic as displayed to customer.
contactTopicOrder tinyint 0Order in which topic is listed.
contactTopicEmail varchar255''Email address(es) to notify when contact form is submitted with this topic.
contactTopicStatus bit 0Indicates whether the topic is currently listed on the site for customers to select.
contactTopicDateCreated smalldatetime getdate()Date/time when topic was added.
contactTopicDateUpdated smalldatetime getdate()Date/time when topic was last updated.
avContent - Stores the various customizable content items, including email messages, email subjects, comfirmation messages, etc.
contentIDPrimaryint  Identity field primary key of content item.
userIDForeignint 0User who created content.
contentName varchar100''Name of content item.
contentDescription varchar255''Description of content item.
contentCode varchar50''Unique code identifying content. Used for querying for content value.
contentCategoryIDForeignint 0ID of content category.
contentType varchar25''Type of content, e.g., email body, etc.
contentOrder smallint 0Order in which content item is listed.
contentMaxlength smallint 0Maximum length permitted for this content.
contentHtmlOk bit 0Indicates whether the content may be in html format.
contentRequired bit 0Indicates whether the content value is required.
contentStatus bit 0Indicates whether content is currently being used.
contentFilename varchar50''Optional name of file that is updated with content (in customer directory).
contentDateCreated smalldatetime getdate()Date/time content item was created.
contentDateUpdated smalldatetime getdate()Date/time content item was last updated.
avContentCategory - Stores the content categories used to organize the content items.
contentCategoryIDPrimaryint  Identity field primary key of content/category combo.
contentCategoryName varchar100''Name of content category.
contentCategoryCode varchar50''Code that uniquely identifies content listing. Used for querying.
contentCategoryDescription varchar255''Description of content category.
contentCategoryOrder smallint 0Order in which content category is listed.
contentCategoryStatus bit 0Indicates whether the content category is currently being used.
userIDForeignint 0User who created content category.
contentCategoryDateCreated smalldatetime getdate()Date/time content category was created.
contentCategoryDateUpdated smalldatetime getdate()Date/time content category was last updated.
avContentCompany - Stores every content version for combination of company, language and content type.
contentCompanyIDPrimaryint  Identity field primary key for each version of the content/company/language combo.
contentIDForeignint 0ID of content type.
companyIDForeignint 0ID of company the content is for.
languageIDForeigntinyint 0ID of language the content applies to.
userIDForeignint 0ID of author who created this content.
contentCompanyStatus bit 0Indicates whether this content version is currently active.
contentCompanyText nvarchar8000''Text of the content. Supports double-byte storage.
contentCompanyText sysname ''Text of the content. Supports double-byte storage.
contentCompanyHtml bit 0Indicates whether content is in html or text format.
contentCompanyOrder smallint 0Order in which content was updated, beginning at 1 and increasing sequentially.
contentCompanyDateCreated smalldatetime getdate()Date/time when this version of the content was created.
contentCompanyDateUpdated smalldatetime getdate()Date/time when this version of the content was updated and thus no longer the current version.
avCreditCard - Stores credit card information for customers and archives old versions if requested.
creditCardIDPrimaryint  Identity field primary key of credit card information.
addressIDForeignint 0ID of billing address used for credit card.
userIDForeignint 0ID of user who credit card belongs to.
companyIDForeignint 0ID of company that credit card belongs to.
userID_authorForeignint 0ID of user who entered credit card information.
creditCardName varchar100''Name on credit card.
creditCardNumber varchar100''Credit card number (encrypted;numbers only).
creditCardExpirationMonth char2''Month of expiration date in 2-digit format.
creditCardExpirationYear char4''Year of credit card expiration date in 4-digit format.
creditCardType varchar50''Type of credit card in text format (e.g., Visa, MasterCard).
creditCardCVC varchar15''3-4 digit numeric code on back of card.
creditCardStatus bit 0Indicates whether this credit card is currently used.
creditCardRetain bit 0Indicates whether to store credit card information after it is used.
creditCardDescription varchar255''Description of credit card.
creditCardCVCstatus bit  Indicates whether credit card has been verified successfully: Null=not attempted, 0=no, 1=yes.
creditCardDateCreated smalldatetime getdate()Date/time when credit card information was added.
creditCardDateUpdated smalldatetime getdate()Date/time when credit card information was last updated or made inactive.
avCustomField - Stores the list of custom user-defined fields created by each company.
customFieldIDPrimaryint  Identity field primary key for each user-defined field.
companyIDForeignint 0ID of company that created user-defined field.
userIDForeignint 0ID of user who created or last updated user-defined field.
customFieldName varchar100''Name of user-defined field for internal purposes.
customFieldTitle varchar100''Label of user-defined field as displayed on site.
customFieldDescription varchar255''Description of user-defined field.
customFieldType varchar50'varchar'Database field type of user-defined field, e.g., int, varchar, datetime, decimal, bit.
customFieldFormType varchar20''Form field type used when entering/selecting value for custom field.
customFieldStatus bit 0Indicates whether user-defined field is currently being used.
customFieldExportXml bit 0Indicates whether user-defined field should be included in XML export.
customFieldExportTab bit 0Indicates whether user-defined field should be included in tab-delimited export.
customFieldExportHtml bit 0Indicates whether user-defined field should be displayed in HTML.
customFieldInternal bit 0Indicates whether customer can view this custom field value or if internal only.
customFieldDateCreated smalldatetime getdate()Date/time that user-defined field was created.
customFieldDateUpdated smalldatetime getdate()Date/time that user-defined field was last updated.
avCustomFieldBit - Stores each version of the value of a custom field of type True/False (bit) for a particular target.
customFieldBitIDPrimaryint  Identity field primary key for each custom field value for a target.
customFieldIDForeignint 0ID of custom field.
userIDForeignint 0ID of user who added this custom field value.
primaryTargetIDForeignint 0ID of target type.
targetID int 0Primary key value of target.
customFieldOptionIDForeignint 0ID of custom field option that was selected as the value of this target.
customFieldBitStatus bit 0Indicates whether this is the current value of the custom field for this target.
customFieldBitValue bit  Value of custom field for this target.
customFieldBitDateCreated smalldatetime getdate()Date/time this custom field value was added for this target.
customFieldBitDateUpdated smalldatetime getdate()Date/time this custom field value was no longer being used for this target and was updated to a new value (stored in a separate row).
avCustomFieldDateTime - Stores each version of the value of a custom field of type date/time (smalldatetime) for a particular target.
customFieldDateTimeIDPrimaryint  Identity field primary key for each custom field value for a target.
customFieldIDForeignint 0ID of custom field.
userIDForeignint 0ID of user who added this custom field value.
primaryTargetIDForeignint 0ID of target type.
targetID int 0Primary key value of target.
customFieldOptionIDForeignint 0ID of custom field option that was selected as the value of this target.
customFieldDateTimeStatus bit 0Indicates whether this is the current value of the custom field for this target.
customFieldDateTimeValue smalldatetime  Value of custom field for this target.
customFieldDateTimeDateCreated smalldatetime getdate()Date/time this custom field value was added for this target.
customFieldDateTimeDateUpdated smalldatetime getdate()Date/time this custom field value was no longer being used for this target and was updated to a new value (stored in a separate row).
avCustomFieldDecimal - Stores each version of the value of a custom field of type float (decimal) for a particular target.
customFieldDecimalIDPrimaryint  Identity field primary key for each custom field value for a target.
customFieldIDForeignint 0ID of custom field.
userIDForeignint 0ID of user who added this custom field value.
primaryTargetIDForeignint 0ID of target type.
targetID int 0Primary key value of target.
customFieldOptionIDForeignint 0ID of custom field option that was selected as the value of this target.
customFieldDecimalStatus bit 0Indicates whether this is the current value of the custom field for this target.
customFieldDecimalValue decimal18.4 Value of custom field for this target.
customFieldDecimalDateCreated smalldatetime getdate()Date/time this custom field value was added for this target.
customFieldDecimalDateUpdated smalldatetime getdate()Date/time this custom field value was no longer being used for this target and was updated to a new value (stored in a separate row).
avCustomFieldInt - Stores each version of the value of a custom field of type integer (int) for a particular target.
customFieldIntIDPrimaryint  Identity field primary key for each custom field value for a target.
customFieldIDForeignint 0ID of custom field.
userIDForeignint 0ID of user who added this custom field value.
primaryTargetIDForeignint 0ID of target type.
targetID int 0Primary key value of target.
customFieldOptionIDForeignint 0ID of custom field option that was selected as the value of this target.
customFieldIntStatus bit 0Indicates whether this is the current value of the custom field for this target.
customFieldIntValue int  Value of custom field for this target.
customFieldIntDateCreated smalldatetime getdate()Date/time this custom field value was added for this target.
customFieldIntDateUpdated smalldatetime getdate()Date/time this custom field value was no longer being used for this target and was updated to a new value (stored in a separate row).
avCustomFieldOption - Stores the list of options for custom fields where the value is selected via select, checkbox or radio button.
customFieldOptionIDPrimaryint  Identity field primary key for each custom field select option.
customFieldIDForeignint 0ID of cusotm field that option is for.
customFieldOptionLabel varchar100''Label of custom field option as displayed to customer when selecting.
customFieldOptionValue varchar100''Value of custom field stored when selected.
customFieldOptionOrder smallint 0Order in which cusotm field option is listed.
customFieldOptionStatus bit 0Indicates whether this option is currently being used or has been replaced by an updated list of options.
customFieldOptionVersion smallint 0Version of options list this option is a part of, beginning at 1 and incrementing.
userIDForeignint 0ID of user who created this list of custom field options.
customFieldOptionDateCreated smalldatetime getdate()Date/time when optoin was created.
customFieldOptionDateUpdated smalldatetime getdate()Date/time when option was replaced by a new version and thus no longer active.
avCustomFieldTarget - Stores the targets that each custom user-defined field applies to, where a target is the primary key of another table, e.g., user, company, invoice.
customFieldIDPrimaryint  ID of user-defined field.
primaryTargetIDPrimaryint  ID of target of user-defined field.
customFieldTargetStatus bit 0Indicates whether this user-defined field is still used for this target.
userIDForeignint 0ID of user who created or last updated this record.
customFieldTargetOrder smallint 0Order in which this user-defined field is listed for this target.
customFieldTargetDateCreated smalldatetime getdate()Date/time that user-defined field was enabled for this target.
customFieldTargetDateUpdated smalldatetime getdate()Date/time that user-defined field was last udpated for this target.
avCustomFieldVarchar - Stores each version of the value of a custom field of type text (varchar) for a particular target.
customFieldVarcharIDPrimaryint  Identity field primary key for each custom field value for a target.
customFieldIDForeignint 0ID of custom field.
userIDForeignint 0ID of user who added this custom field value.
primaryTargetIDForeignint 0ID of target type.
targetID int 0Primary key value of target.
customFieldOptionIDForeignint 0ID of custom field option that was selected as the value of this target.
customFieldVarcharStatus bit 0Indicates whether this is the current value of the custom field for this target.
customFieldVarcharValue varchar1000 Value of custom field for this target.
customFieldVarcharDateCreated smalldatetime getdate()Date/time this custom field value was added for this target.
customFieldVarcharDateUpdated smalldatetime getdate()Date/time this custom field value was no longer being used for this target and was updated to a new value (stored in a separate row).
avExportQuery - Stores the listing of queries that can be exported via the system.
exportQueryIDPrimaryint  Identity field primary key for each query that can be exported.
exportQueryName varchar100''Internal name of query.
exportQueryTitle varchar100''Name of query as displayed to customer.
exportQueryDescription varchar255''Description of query.
exportQueryOrder smallint ''Order in which query is listed.
exportQueryStatus bit 0Indicates whether query is still active and can still be exported.
avExportQueryField - For each query, stores which fields are included and the default order in which they are displayed/exported.
exportQueryFieldIDPrimaryint  Identity field primary key for each field added to query. Enables adding the same table field more than once to a query.
exportQueryIDForeignint 0ID of query.
exportTableFieldIDForeignint 0ID of field included in query.
exportQueryFieldOrder smallint 0Order in which field is returned in results.
exportQueryFieldAs varchar100''Name of field as returned in query using "select as" to differentiate from similar table field in query.
avExportQueryFieldCompany - For each company, stores custom settings of which fields to include in exports for each export query.
exportQueryFieldIDPrimaryint  ID of query / field combination.
companyIDPrimaryint  ID of company with export settings.
exportQueryFieldCompanyOrder smallint 0Order in which field is listed in export file.
exportQueryFieldCompanyXmlStatus bit 0Indicates whether field should be included in XML export.
exportQueryFieldCompanyTabStatus bit 0Indicates whether field should be included in tab-delimited export.
exportQueryFieldCompanyHtmlStatus bit 0Indicates whether field should be displayed in browser.
userIDForeignint 0ID of user who last updated these settings.
avExportTable - Stores the "tables" that customers may export, where a table may include related fields from other tables.
exportTableIDPrimaryint  Primary key identity field for each export "table" which may include related fields from other tables.
primaryTargetIDForeignint 0ID of primary target of table.
exportTableName varchar50''Name of table as displayed for export.
exportTableDescription varchar255''Description of table as displayed to customer.
exportTableOrder smallint 0Order in which table is listed when customers select export options.
exportTableStatus bit 0Indicates whether table is currently export-able.
avExportTableField - Stores the fields for each "table" that can be exported.
exportTableFieldIDPrimaryint  Identity field primary key for each database field that can be exported.
exportTableIDForeignint 0ID of export table that field is in.
exportTableFieldName varchar100''Actual database field name.
exportTableFieldType varchar50''Field data type.
exportTableFieldPrimaryKey bit 0Indicates whether field is the prmiary key of the table (or a component of the primary key).
exportTableFieldSize smallint 0Maximum length of field, if varchar field.
exportTableFieldDescription varchar255''Description of field as displayed to customer to explain its purpose.
exportTableFieldOrder smallint 0Order in which field is listed within table.
exportTableFieldXmlName varchar100''Default name of field to use when exporting in XML.
exportTableFieldXmlStatus bit 0Indicates whether field can be included when exporting in XML
exportTableFieldTabName varchar100''Default name of field to use when exporting in tab-delimited format.
exportTableFieldTabStatus bit 0Indicates whether field can be included when exporting in tab-delimited format.
exportTableFieldHtmlName varchar100''Default name of field to use when displaying in html.
exportTableFieldHtmlStatus bit 0Indicates whether field can be included when displaying in html.
exportTableFieldStatus bit 0Indicates whether this field should be exported.
avExportTableFieldCompany - For each company, stores the export options when exporting in xml or tab-delimited format, and for when displaying in html.
exportTableFieldIDForeignint 0ID of field to be exported.
companyIDForeignint 0ID of company that export option is for.
exportTableFieldCompanyXmlName varchar100''Name of field to use when exporting in XML.
exportTableFieldCompanyTabName varchar100''Name of field to use when exporting in tab-delimited format.
exportTableFieldCompanyHtmlName varchar100''Name of field to use when displaying in html.
exportTableFieldCompanyStatus bit 0Indicates whether company wants to use custom value of field for exporting.
userIDForeignint 0ID of user who last updated values.
avFieldArchive - Stores archived versions of fields that are not archived within their own tables, primarily intended for avUser and avCompany table fields but can be used for all other tables as well.
fieldArchiveIDPrimaryint  Identity field primary key to uniquely identify each archived field value.
fieldArchiveTableName varchar50''Name of table which contains the updated field.
fieldArchiveFieldName varchar50''Name of field that was updated.
fieldArchiveValue varchar255''Old value of field before it was updated.
primaryTargetIDForeignint 0Target type for which this field has been updated, e g., user, company, etc.
targetID int 0ID of target type.
userIDForeignint 0ID of user who changed the field value.
fieldArchiveDate smalldatetime getdate()Date/time that field was updated from this value to the new value.
avGroup - Groups of users within each company. Used for assigning permissions.
groupIDPrimaryint  Identity field primary key of group.
companyIDForeignint 0ID of company the group belongs to.
userIDForeignint 0ID of user who created group.
groupName varchar100''Name of group.
groupCategory varchar50''Category of group for organization purposes, describing user type, e.g., customer, partner, vendor, employee, etc.
groupDescription varchar255''Description of group.
groupStatus bit 0Identifies whether the group is currently being used and accepting new users.
groupID_custom varchar50''Custom ID of group. Primarily for integration purposes.
groupDateCreated smalldatetime getdate()Date/time group was created.
groupDateUpdated smalldatetime getdate()Date/time group was last updated.
avGroupTarget - Stores the members of each group, including uesrs, companies, affiliates, cobrands, etc.
groupTargetIDPrimaryint  Identity field primary key for each group to which target belongs. Provides full audit capability.
groupIDForeignint 0ID of group.
primaryTargetIDForeignint 0Target type that is a member of this group, e.g., user, company, group, cobrand, etc.
targetID int 0ID of target type.
userIDForeignint 0ID of user who added or last updated target/group status.
groupTargetStatus bit 0Indicates whether target currently is member of group.
groupTargetDateCreated smalldatetime getdate()Date/time that target was added to group.
groupTargetDateUpdated smalldatetime getdate()Date/time that target/group status was last updated.
avHeaderFooter - Stores each version of header and footer for categories and cobrand sites. Requires 2 rows to store header and footer separately for each category/cobrand.
headerFooterIDPrimaryint  Identity field primary key for each category header or footer.
primaryTargetIDForeignint 0Target type to which this header or footer belongs, e.g., user, company, group, cobrand, etc.
targetID int 0ID of target type.
userIDForeignint 0User who created category header/footer.
languageID tinyint 0Indicates which language the header/footer is used for.
headerFooterStatus bit 0Yes/no indicator of whether this is the current category header or footer.
headerFooterText ntext ''The actual category header/footer.
headerFooterHtml bit 0Indicates whether header/footer is in text or html format.
headerFooterIndicator bit 0Indicates whethis is the category header (0) or footer (1). Header is returned first in 2-row query to get both.
headerFooterDateCreated smalldatetime getdate()Date/time when category header/footer was created.
headerFooterDateUpdated smalldatetime getdate()Date/time when category header/footer was updated (and thus no longer being used).
avImage - Stores images used for each product.
imageIDPrimaryint  Identity field primary key of product image.
primaryTargetIDForeignint 0Target type that image is for, e.g., product, category, etc.
targetID int 0ID of target the image is for, e.g., product.
imageURL varchar255''URL to image.
imageAlt varchar100''Text to dsiplay in alt attribute of image tag.
imageHeight smallint 0Height of image in pixels.
imageWidth smallint 0Width of image in pixels.
imageBorder tinyint 0Size of border to display with image.
imageSize smallint 0Size of image in bytes.
imageOther varchar100''Other tag attributes to include in image tag.
imageTag varchar600''Full image tag used to display image.
imageStatus bit 0Indicates whether image is currently used.
imageOrder smallint 0Indicates order in which image is displayed.
imageHasThumbnail bit 0Indicates whether image has a thumbnail image.
imageIsThumbnail bit 0Indicates whether image is thumbnail or full-sized.
imageID_parentForeignint ''ID of full-sized product image if image is thumbnail and should link to full image.
imageUploaded bit 0Indicates whether image was uploaded to server.
imageDeleted bit 0Indicates whether image was deleted from server.
imageDisplayCategory bit 0Indicates whether this image is displayed with product on category page.
imageDateCreated smalldatetime getdate()Date/time image was added to product.
imageDateUpdated smalldatetime getdate()Date/time image was last updated.
userIDForeignint 0ID of user who added image to product.
avInvoice - Stores invoices. Can also be used as the shopping cart order summary.
invoiceIDPrimaryint  Identity field primary key for each invoice.
userIDForeignint 0ID of user who invoice is for.
companyIDForeignint 0ID of company that invoice is for.
invoiceClosed bit 0Indicates whether the invoice is closed and no longer open for new line items or changes.
invoiceDateClosed smalldatetime  Date/time that invoice was closed.
invoiceSent bit 0Indicates whether invoice has been sent to customer.
invoicePaid bit  Indicates whether invoice has been paid. Null = no; 0 = partially paid; 1 = fully paid.
invoiceDatePaid smalldatetime  Date/time that invoice has been fully paid.
invoiceTotal money 0Total dollar amount of invoice, including line items, taxes and shipping.
invoiceTotalTax money 0Sum of taxes for this invoice.
invoiceTotalLineItem money 0Total dollar amount of all line items in invoice.
invoiceTotalShipping money 0Total dollar amount of all shipping costs for invoice.
invoiceTotalPaymentCredit money 0Total amount of payment credits applied to this invoice. Stored as negative number since should be subtracted when calculating invoice total.
invoiceShipped bit  Indicates whether items have shipped (if applicable). Null = no or n/a, 0 = partial, 1 = yes.
invoiceCompleted bit 0Indicates whether this invoice has been fully processed, including payment, shipping, etc.
invoiceDateCompleted smalldatetime  Date/time that invoice was completed, including all necessary actions.
invoiceStatus bit 0Indicates whether invoice is currently active.
languageIDForeigntinyint 0ID of language in instance where customer uses more than one language.
invoiceDateDue smalldatetime  Date that invoice is due from customer.
invoiceID_custom varchar50''Custom ID identifying invoice. Used for internal purposes to synch with other systems.
invoiceManual bit 0Indicates whether invoice was created manually.
userID_authorForeignint 0ID of user who manually created invoice. Is 0 if customer created invoice or if automated.
companyID_authorForeignint 0ID of company the customer is submitting the invoice with.
regionIDForeignint 0ID of region where order is being shipped.
invoiceShippingMethod varchar50''Method used for shipping ordered products to customer.
addressID_shippingForeignint 0ID of shipping address.
addressID_billingForeignint 0ID of billing address.
creditCardIDForeignint 0ID of credit card information.
bankIDForeignint 0ID of bank account used to pay for invoice.
invoiceInstructions varchar500''Special instructions for each invoice/purchase.
subscriberIDForeignint 0ID of subscriber used to automatically generate this invoice.
invoiceIsExported bit  Indicates whether record has been exported. Null = not exported; 0 = Exported but awaiting confirmation the record was imported; 1 = Exported and successfully imported.
invoiceDateExported smalldatetime  Date/time that record export status was updated.
invoiceDateCreated datetime getdate()Date/time invoice was created.
invoiceDateUpdated datetime getdate()Date/time invoice was last updated.
avInvoiceLineItem - Stores individual line items within an invoice, or items in shopping cart.
invoiceLineItemIDPrimaryint  Identity field primary key for each invoice line item.
invoiceIDForeignint 0ID of invoice the line item applies to.
invoiceLineItemName nvarchar510''Name of line item stored in appropriate language and using current name.
invoiceLineItemName sysname ''Name of line item stored in appropriate language and using current name.
invoiceLineItemDescription nvarchar2000''Optional description of line item in existing language and based on current value.
invoiceLineItemDescription sysname ''Optional description of line item in existing language and based on current value.
invoiceLineItemDescriptionHtml bit 0Indicates whether description is in html format.
productIDForeignint 0ID of product the line item is for. Equals 0 if manual item or bundled product.
priceIDForeignint 0ID of price used for this product.
categoryIDForeignint 0ID of category the product was added from. Useful for reporting purposes if product is listed in multiple categories.
regionIDForeignint 0ID of region. For pricing and tax purposes.
invoiceLineItemQuantity decimal14.40Quantity of item purchased.
invoiceLineItemPriceUnit money 0Dollar amount of unit price of item.
invoiceLineItemSubTotal money 0Total dollar amount of line item (price * quantity) minus any discount.
invoiceLineItemDiscount money 0Dollar amount of any discount or credit applied to this line item. Probably added manually. Applies to the subtotal, not the price per unit.
invoiceLineItemTotal money 0Final total price of line item, after discount and taxes.
invoiceLineItemPriceNormal money 0Normal product price as opposed to potential custom price or volume discount.
invoiceLineItemProductID_custom varchar50''Custom product ID for products with variants where ID is dynamically generated and does not exist as a distinct product listing.
invoiceLineItemTotalTax money 0Dollar amount of taxes applicable for this line item.
invoiceLineItemOrder smallint 0Order in which line item is listed on invoice. By default, order in which items were added. Can be updated manually as well.
invoiceLineItemStatus bit 0Indicates whether this line item is active or is not included on current invoice for some reason.
invoiceLineItemProductIsBundle bit 0Indicates whether this product is a bundle of products.
invoiceLineItemProductInBundle bit 0Indicates whether this product was added to invoice as a component of a product bundle.
userIDForeignint 0ID of user who has manually created or updated this line item.
invoiceLineItemManual bit 0Indicates whether the line item was added manually.
productParameterExceptionIDForeignint 0ID of product parameter exception that affected price.
userID_cancelForeignint 0ID of user who made line item inactive.
invoiceLineItemID_parent int 0ID of line item from which this line item was updated. Ensures each line item is archived and not updated. If new, equals 0.
invoiceLineItemID_trend int 0ID of line item from which this line item was originally based. Ensures each line item is archived and not updated. If new, equals invoiceLineItemID.
subscriptionIDForeignint 0ID of subscription used to automatically generate this line item.
priceStageIDForeignint 0ID of price stage of custom price used for this line item.
priceVolumeDiscountIDForeignint 0ID of volume discount option used for pricing of this line item.
invoiceLineItemDateBegin smalldatetime  Beginning date/time of the time period for which this line item applies.
invoiceLineItemDateEnd smalldatetime  Ending date/time of the time period for which this line item applies.
invoiceLineItemDateCreated datetime getdate()Date/time line item was added to invoice.
invoiceLineItemDateUpdated datetime 0Date/time line item was last updated.
avInvoiceLineItemParameter - Stores the product parameters selected for each invoice/purchase line item.
invoiceLineItemIDPrimaryint  ID of line item that product parameter applies to.
productParameterOptionIDPrimaryint  ID of product parameter option selected for line item.
avInvoicePayment - Matches payment transaction(s) to the invoice(s) to which it applies. Enables a payment to be applied to multiple invoices and multiple payments to apply to a single invoice.
invoiceIDForeignint 0ID of invoice.
paymentIDForeignint 0ID of payment transaction.
invoicePaymentManual bit 0Indicates whether payment was manually applied to this invoice.
userIDForeignint 0ID of user who applied payment to invoice.
invoicePaymentDate smalldatetime getdate()Date/time payment was applied to invoice.
invoicePaymentAmount money 0Amount applied to this invoice via this payment.
avInvoicePaymentCredit - Records which invoice(s) a payment credit(s) applies to.
invoicePaymentCreditIDPrimaryint  Identity field primary key for credits added to invoice. Necessary because a single credit could be added to an invoice multiple times due to rollovers.
paymentCreditIDPrimaryint  ID of payment credit.
invoiceIDPrimaryint  ID of invoice the payment credit is applied to.
userIDForeignint 0ID of user who applied credit to invoice.
invoicePaymentCreditManual bit 0Indicates whether credit was manually applied to invoice.
invoicePaymentCreditDate smalldatetime getdate()Date/time credit was applied to invoice.
invoicePaymentCreditAmount money 0Amount of credit applied to invoice.
invoicePaymentCreditText nvarchar510''Text describing payment credit displayed as line item text on invoice.
invoicePaymentCreditText sysname ''Text describing payment credit displayed as line item text on invoice.
invoicePaymentCreditRolloverPrevious bit 0Indicates whether the credit amount is a partial amount rolled over from an earlier invoice that could not use the full credit.
invoicePaymentCreditRolloverNext bit 0Indicates whether the credit amount is a partial amount that will be rolled over to the next invoice since this invoice could not use the full credit (or remaining portion thereof).
avInvoiceSent - Stores each time invoice was sent to customer and the method.
invoiceSentIDPrimaryint  Identity field primary key for each time invoice was sent.
invoiceIDForeignint 0ID of invoice that was sent.
invoiceSentDate smalldatetime getdate()Date/time that invoice was sent.
invoiceSentEmail bit 0Indicates whether invoice was sent via email.
invoiceSentFax bit 0Indicates whether invoice was sent via fax.
invoiceSentPostage bit 0Indicates whether invoice was sent via postal mail.
userID_authorForeignint 0ID of user who requested the invoice be sent.
userID_targetForeignint 0ID of user who invoice was sent to.
avInvoiceTax - Stores taxes for each invoice.
invoiceTaxIDPrimaryint  Identity field primary key of invoice tax record.
invoiceIDForeignint 0ID of invoice the tax applies to.
invoiceLineItemIDForeignint 0ID of line item which tax applies to (if specific to a line item)
taxIDForeignint 0ID of tax type.
userIDForeignint 0ID of user who manually added tax to invoice.
invoiceTaxManual bit 0Indicates whether tax was manually added.
invoiceTaxOrder smallint 0Order in which tax is listed on invoice within all line items and taxes.
invoiceTaxAmount money 0Dollar amount of tax.
invoiceTaxName nvarchar200''Name of tax
invoiceTaxName sysname ''Name of tax
invoiceTaxDescription nvarchar510''Description of tax
invoiceTaxDescription sysname ''Description of tax
invoiceTaxDateCreated smalldatetime getdate()Date/time the tax was added to the invoice.
invoiceTaxDateUpdated smalldatetime 0Date/time the tax item was last updated.
avInvoiceTemplate - Stores the display of an invoice based generated via the template. Enables storing multiple versions of invoice for different templates or if the template changed.
invoiceIDPrimaryint  ID of invoice.
templateIDPrimaryint  ID of template used to generate invoice.
invoiceTemplateStatus bit 0Indicates whether this is the most recent version of the invoice display (in case multiple templates were used).
invoiceTemplateManual bit 0Indicates whether invoice display was manually generated (or via subscription).
invoiceTemplateDateCreated smalldatetime getdate()Date/time that this invoice was generated via the template.
invoiceTemplateDateUpdated smalldatetime getdate()Date/time that invoice display using the template was last updated.
invoiceTemplateHtml bit 0Indicates whether the invoice is stored in html format.
invoiceTemplateText text ''Html or text of invoice.
userIDForeignint 0ID of user who manually generated invoice via the template.
avIPaddress - For each company, stores the list of IP addresses or range blocks that may access the system, both for user login and web services originating requests.
IPaddressIDPrimaryint  Identity field primary key for each IP address the company restricts access to.
companyIDForeignint 0ID of company the IP address applies to.
IPaddress varchar25''IP address or minimum IP address permitted to access the site for this company.
IPaddress_max varchar25''If IP address range, maximum IP address permitted to access the site for this company.
IPaddressBrowser bit 0Determines whether users are permitted to login from this IP address or range.
IPaddressWebService bit 0Determines whether web services request may originate from this IP address/range.
userIDForeignint 0ID of user who added this IP address.
IPaddressDateCreated smalldatetime getdate()Date/time when IP address was added for this company.
IPaddressDateUpdated smalldatetime getdate()Date/time when this IP address for this company was last updated.
avLanguage - Stores the various languages used by customers.
languageIDPrimarytinyint  Primary key of each language.
languageName varchar100''Name of language.
languageAbbreviation varchar50''Abbreviated name of language, typically 2 characters based on standard code.
languageDefault bit 0Indicates whether language is the default for all new companies.
avLanguageCompany - Identifies which languages are used by each corporate client. Language used by customer is stored in avCompany.
companyIDForeignint 0ID of company.
languageIDForeigntinyint 0ID of language.
userIDForeignint 0ID of user who enabled this language for this company.
languageCompanyDefault bit 0Indicates whether this is the default language for this company, which would be the default for all company customers unless otherwise indicated.
languageCompanyStatus bit 0Indicates whether this language is currently being used by this company.
languageCompanyDateCreated smalldatetime getdate()Date this language was originally enabled for this company.
languageCompanyDateUpdated smalldatetime getdate()Date this language was last updated for this company.
avLoginAttempt - Stores failed login attempts. Used to temporarily disable a username after X unsuccessful login attempts within a designated period of time.
loginAttemptIDPrimaryint  Identity field primary key of failed login attempt.
companyID_author int 0ID of company to which user with this username belongs, either as a user, customer or partner.
loginAttemptUsername varchar255''Username used in failed login attempt.
loginAttemptCount smallint 0Number of times this user has attempted to login with the username.
loginAttemptDateCreated smalldatetime getdate()Date/time that first login failed.
loginAttemptDateUpdated smalldatetime getdate()Date/time of last failed login.
avLoginSession - Stores each instance of a login session for a user
loginSessionIDPrimaryint  Identity field primary key for each login session.
userID int 0ID of user that logged in.
loginSessionDateBegin smalldatetime getdate()Date/time that session began.
loginSessionDateEnd smalldatetime Date/time that session ended.
loginSessionTimeout bit 0Indicates whether the session ended via a manual logout or a timeout.
loginSessionCurrent bit 0Indicates whether this is the current session for this user.
avMerchant - Stores the merchant processors used to process credit card and bank/ACH transactions.
merchantIDPrimaryint  Identity field primary key for each merchant processor.
companyIDForeignint 0ID of company that is merchant processor.
userIDForeignint 0ID of contact as merchant processor company.
merchantName varchar100''Internal reference name of merchant processor.
merchantTitle varchar100''Public name of merchant processor used to add merchant accounts.
merchantURL varchar100''URL to merchant processor website.
merchantDescription varchar255''Internal description of merchant processor.
merchantBank bit 0Indicates whether merchant processor can process bank/ACH transactions.
merchantCreditCard bit 0Indicates whether merchant processor can process credit card transactions.
merchantFilename varchar100''Name of file that processes transactions for this merchant processor.
merchantStatus bit 0Indicates whether merchant processor is still active and thus an option when adding a merchant account.
merchantRequiredFields varchar255''List of fields required for merchant accounts for this merchant.
merchantDateCreated smalldatetime getdate()Date/time that merchant was added.
merchantDateUpdated smalldatetime getdate()Date/time that merchant was last updated.
avMerchantAccount - Stores the merchant account details for each company that uses a merchant processor.
merchantAccountIDPrimaryint  Identity field primary key for each merchant account.
companyIDForeignint 0ID of company that merchant account is for.
userIDForeignint 0ID of user that added merchant account.
merchantIDForeignint 0ID of merchant processor for this merchant account.
merchantAccountUsername varchar100''Encrypted username used by company for processing transactions.
merchantAccountPassword varchar100''Encrypted password used by company for processing transactions.
merchantAccountID_custom varchar100''Encrypted custom ID used by company for processing transactions.
merchantAccountStatus bit 0Indicates whether merchant account is still being used.
merchantAccountBank bit 0Indicates whether merchant account is used to process bank/ACH transactions.
merchantAccountCreditCard bit 0Indicates whether merchant account is used to process credit card transactions.
merchantAccountDescription varchar255''Internal description of merchant account.
merchantAccountDateCreated smalldatetime getdate()Date/time that merchant account was created.
merchantAccountDateUpdated smalldatetime getdate()Date/time that merchant account was last updated or made inactive.
avNewsletter - Stores each newsletter that was sent.
newsletterIDPrimaryint  Identity field primary key for each newsletter.
contactIDForeignint 0ID of contact management entry that stores the newsletter.
newsletterRecipientCount int 0Number of users the newsletter was sent to.
newsletterDescription varchar255''Internal description of newsletter.
newsletterCriteria text  Code description for who newsletter was sent to for purposes of re-populating filtering criteria form. In URL format and displayed as form.
avNewsletterSubscriber - Stores subscribers to newsletters who are not registered users/customers.
newsletterSubscriberIDPrimaryint  Identity field primary key for each newsletter subscriber.
companyID_authorForeignint 0ID of company that subscriber is a customer of.
newsletterSubscriberEmail varchar100''Email address of subscriber.
newsletterSubscriberStatus bit 0Indicates whether person is currently subscribed or opted-out.
newsletterSubscriberHtml bit 0Indicates whether subscriber prefers newsletter in text or html format.
userIDForeignint 0If user registers, change status but record userID for reporting purposes.
cobrandIDForeignint 0ID of cobrand partner that subscriber signed up with.
affiliateIDForeignint 0ID of affiliate that referred subscriber.
newsletterSubscriberDateCreated smalldatetime getdate()Daet/time that user originally subscribed.
newsletterSubscriberDateUpdated smalldatetime getdate()Date/time subscription info was last updated, presumably to unsubscribe.
newsletterSubscriberIsExported bit  Indicates whether record has been exported. Null = not exported; 0 = Exported but awaiting confirmation the record was imported; 1 = Exported and successfully imported.
newsletterSubscriberDateExported smalldatetime  Date/time that record export status was updated.
avNote - Stores notes posted by users about other users, companies, invoices, etc.
noteIDPrimaryint  Identity field primary key for each note.
userID_authorForeignint 0ID of user who created note.
userID_targetForeignint 0ID of user who note is about.
companyID_targetForeignint 0ID of company that note is about.
primaryTargetIDForeignint 0Target type that note is about, e.g., user, customer, invoice, etc.
targetID int 0ID of target that note is about.
noteMessage text ''Text of the note.
noteStatus bit 0Indicates whether the note is active or has been hiddden.
noteDateCreated smalldatetime getdate()Date/time that note was created.
noteDateUpdated smalldatetime getdate()Date/time that note was last updated.
avPayflow - Stores the options for the payment workflow method for how to process subscriber payments, including sending of invoices/receipts, charging customers and handling charge failures.
payflowIDPrimaryint  Identity field primary key for each payment workflow method.
userIDForeignint 0ID of user who created payment workflow method.
companyIDForeignint 0ID of company that payment workflow method is for.
payflowName varchar100''Internal reference name of payment workflow method. Must be unique.
payflowID_custom varchar50''Optional unique code that identifies each payment workflow method. Primarily for integration purposes.
payflowDefault bit 0Indicates whether this is the default payment workflow method for all customers of this company unless otherwise specified.
payflowStatus bit 0Indicates whether this payment workflow method is actively being used.
payflowInvoiceSend bit 0Indicates whether to send an invoice.
payflowReceiptSend bit 0Indicates whether to send a receipt after being charged.
payflowInvoiceDaysFromSubscriberDate tinyint 0Number of days from subscriber processing to send invoice.
payflowChargeDaysFromSubscriberDate tinyint 0Number of days from subscriber processing date to charge customer.
payflowRejectNotifyCustomer bit 0Indicates whether to notify customer upon charge rejection.
payflowRejectNotifyAdmin bit 0Indicates whether to notify admin upon rejection of customer charge.
payflowRejectRescheduleDays tinyint 0Number of days to reshedule charge after charge is rejected. 0 = no automatic rescheduling.
payflowRejectMaximum_company tinyint 0Maximum number of times a customer charge may be rejected for this payment workflow method before a (manual) action is taken. 0 = no limit.
payflowRejectMaximum_invoice tinyint 0Maximum number of times a charge may be rejected for an individual invoice before a (manual) action is taken. 0 = no limit.
payflowRejectMaximum_subscriber tinyint 0Maximum number of times a charge may be rejected for a particular subscriber before a (manual) action is taken. 0 = no limit.
payflowRejectTask bit 0Indicates whether to create a task for admin when charge is rejected.
payflowOrder smallint 0Order in which payment workflow method is listed.
payflowDescription varchar255''Internal description of payment workflow method.
templateID int 0ID of template used to generate invoice/receipt.
payflowEmailFromName varchar100''Name of person that email is from.
payflowEmailReplyTo varchar100''Email address that email is sent from.
payflowEmailSubject varchar100''Subject of email.
payflowEmailCC varchar255''Email address(es) that are cc'd on the email.
payflowEmailBCC varchar255''Email address(es) that are bcc'd on the email.
payflowDateCreated smalldatetime getdate()Date/time that payment workflow method was created.
payflowDateUpdated smalldatetime getdate()Date/time that payment workflow method was last updated.
avPayflowInvoice - Stores the payment workflow method used to process each invoice.
payflowInvoiceIDPrimaryint  Identity field primary key for each invoice / payment workflow method combination.
payflowIDForeignint 0ID of payment workflow method used to process invoice.
invoiceIDForeignint 0ID of invoice being processed.
payflowInvoiceStatus bit 0Indicates whether invoice is currently being processed via payment workflow method.
payflowInvoiceCompleted bit 0Indicates whether invoice has finished being processed by payment workflow method.
payflowInvoiceManual bit 0Indicates invoice was manually set to use this payment workflow method.
userIDForeignint 0ID of user who added invoice to payment workflow method.
payflowInvoiceDateCreated smalldatetime getdate()Date/time that invoice was declared to be processed with payment workflow method.
payflowInvoiceDateUpdated smalldatetime getdate()Date/time that invoice / payment workflow method was updated, probably to reflect it is completed or to be replaced by another method.
avPayflowNotify - For each payment workflow method, stores which admin users to notify for designated events: invoice,receipt,rejection,creditCardExpiration.
payflowNotifyIDPrimaryint  Identity field primary key for each notification option for each user for a payment processing method.
payflowIDPrimaryint  ID of payment workflow method.
userIDPrimaryint  ID of admin user to notify for payment workflow method.
payflowNotifyStatus bit 0payflowUserType
payflowNotifyType varchar50''Purpose of notification: invoice,receipt,rejection,creditCardExpiration
payflowNotifyTask bit 0Indicates whether to create a task for this user.
payflowNotifyEmail bit 0Indicates whether to email user.
payflowNotifyDateCreated smalldatetime getdate()Date/time that user was added to be notified.
payflowNotifyDateUpdated smalldatetime getdate()Date/time that user notification option was last updated.
avPayflowTarget - Stores which payment workflow method to use for processing target invoices. Overrides group settings.
payflowTargetIDPrimaryint  Identity field primary key for each target / payment workflow method.
payflowIDForeignint 0ID of payment workflow method used to process company invoices.
primaryTargetIDForeignint 0ID of target type.
targetID int 0ID of target.
userIDForeignint 0ID of user who added or last updated target to be processed by payment workflow method.
payflowTargetStatus bit 0Indicates whether payment workflow method is still used to process invoices for this target.
payflowTargetDateBegin smalldatetime getdate()Date/time that payment workflow method should begin being used to process invoices for this target.
payflowTargetDateEnd smalldatetime  Date/time that payment workflow method should no longer be used to process invoices for this target.
payflowTargetDateCreated smalldatetime getdate()Date/time that payment workflow method was added for target.
payflowTargetDateUpdated smalldatetime getdate()Date/time that payment workflow method was last updated for target.
avPayflowTemplate - For each notice type of a payment workflow method, determines which template is used for notice.
payflowTemplateIDPrimaryint  Identity field primary key for template used for notice type of payment workflow method.
payflowIDForeignint 0ID of payment workflow method that template is used for.
templateIDForeignint 0ID of template used for payment workflow method.
payflowTemplateStatus bit 0Indicates whether template is currently being used for notice type of payment workflow method.
payflowTemplateType varchar100''Type(s) of notice that template is used for: invoice,receipt,rejection,creditCardExpiration.
payflowTemplatePaymentMethod varchar100''List of payment methods when template is used: credit card,bank,check,cash,certified check,cashier check,barter,services,other
payflowTemplateNotifyMethod varchar100''List of notification methods when this template is used: email,html,doc,pdf
userID int 0ID of user who added payment workflow method template record.
payflowTemplateDateCreated smalldatetime getdate()Date/time that template record was added for payment workflow method.
payflowTemplateDateUpdated smalldatetime getdate()Date/time that template record was last updated (or made inactive) for payment workflow method.
avPayment - Stores each payment and refund transaction, including unsuccessful payments and refunds.
paymentIDPrimaryint  Identity field primary key of payment transaction.
userIDForeignint 0ID of user who paid the payment.
companyIDForeignint 0ID of company that paid the payment.
userID_authorForeignint 0ID of user who manually processed the payment.
companyID_authorForeignint 0ID of company that received payment.
paymentManual bit 0Indicates whether payment was recorded manually.
paymentDateCreated smalldatetime getdate()Date/time that payment was created (and processed, unless scheduled).
paymentDateUpdated smalldatetime getdate()Date/time that payment was last updated.
creditCardIDForeignint 0ID of credit card used for payment.
bankIDForeignint 0ID of bank account information used for payment.
merchantAccountIDForeignint 0ID of merchant account used for payment.
paymentCheckNumber smallint 0Check number (or e-check number) if payment was made via check.
paymentID_custom varchar50''Custom ID Of payment, probably ID of customer who made payment or internal reference/receipt of a received check or cash payment.
paymentStatus bit 0Indicates whether payment is active, i.e., if the payment is essentially deleted, or was refunded or credited.
paymentApproved bit 0Indicates whether payment was approved (1), rejected (0), or not yet determined (null) such as waiting for a check to clear.
paymentAmount money 0Amount of payment.
paymentDescription varchar255''Description of payment.
paymentMessage varchar255''Error or success message returned by merchant processor.
paymentProcessed bit 0Indicates whether payment was processed by the system.
paymentMethod varchar25''Method of payment: credit card,check,cash,ACH,e-check,other
paymentDateReceived smalldatetime getdate()Date/time that payment was received or that refund was sent. Primarily for manual transactions recorded after they are received/sent.
paymentDateScheduled smalldatetime  Date/time that payment is scheduled to be processed.
paymentCategoryIDForeignint 0ID of payment category used to classify this payment/refund.
subscriberIDForeignint 0ID of subscriber that payment or refund applies to.
paymentIsRefund bit 0Indicates whether "payment" is a refund as opposed to payment.
paymentID_refund int 0ID of payment that represents refund which refunded all or part of this particular payment.
subscriberProcessIDForeignint 0ID of subscrinber processing entry during which this payment was processed.
paymentIsExported bit  Indicates whether record has been exported. Null = not exported; 0 = Exported but awaiting confirmation the record was imported; 1 = Exported and successfully imported.
paymentDateExported smalldatetime  Date/time that record export status was updated.
avPaymentCategory - Store categories for the 3 payment types: "payment" from customers, "refund" to customer, and "credit" which is a discount added to an invoice as a line item.
paymentCategoryIDPrimaryint  Identity field primary key for each payment category.
companyIDForeignint 0ID of company that created payment category.
paymentCategoryName varchar100''Name of payment category.
paymentCategoryTitle varchar255''Text displayed on invoice line item for this payment category.
paymentCategoryID_custom varchar50''Custom ID of payment category, primarily for integration purposes.
paymentCategoryOrder tinyint 0Order in which payment category is listed, based on type.
paymentCategoryStatus bit 0Indicates whether payment category is still included in list.
paymentCategoryCreatedViaSetup bit 0Indicates whether payment category was created via setup or when recording a payment.
paymentCategoryType varchar10''Type of payment that category is for: payment (received from customer), refund (sent to customer) or credit (applied to invoice).
paymentCategoryAutoMethod varchar255''Lists the payment method(s) for which this category title should be used for automated payments, or as the default category for manual payments.
userIDForeignint 0ID of user who created or last updated payment category.
paymentCategoryDateCreated smalldatetime getdate()Date/time that payment category was created.
paymentCategoryDateUpdated smalldatetime getdate()Date/time that payment category was last updated.
avPaymentCredit - Stores the various payment credits and discounts, including both one-time and ongoing credits.
paymentCreditIDPrimaryint  Identity field primary key of payment credit.
companyID_authorForeignint 0ID of client company that processed payment credit.
userID_authorForeignint 0ID of user who processed credit.
userIDForeignint 0ID of user who received the credit.
companyIDForeignint 0ID of company that received the credit.
paymentCreditStatus bit 0Indicates whether credit is active.
paymentCreditName varchar255''Name of payment credit.
paymentCreditDescription varchar255''Description of payment credit.
paymentCreditDateBegin smalldatetime  Date/time the credit/discount begins for customer if not a one-time deal.
paymentCreditDateEnd smalldatetime  Last date that credit can be used, i.e., expiration date.
paymentCreditAppliedMaximum smallint 0Maximum number of times the discount may be applied.
paymentCreditAppliedCount smallint 0Number of times the discount has been applied to date.
paymentCreditDateCreated smalldatetime getdate()Date/time credit was created.
paymentCreditDateUpdated smalldatetime getdate()Date/time credit was last updated.
paymentCreditID_custom varchar50''Custom ID of payment credit.
paymentCreditAmount money 0Amount of payment credit.
paymentCreditRollover bit 0Indictates whether credit can be rolled over to future invoices if invoice total is less than credit amount.
paymentCategoryIDForeignint 0ID of payment category used to classify this credit.
subscriberIDForeignint 0ID of subscriber that payment credit applies to.
paymentCreditCompleted bit 0Indicates whether payment credit has been completely used.
paymentCreditIsExported bit  Indicates whether record has been exported. Null = not exported; 0 = Exported but awaiting confirmation the record was imported; 1 = Exported and successfully imported.
paymentCreditDateExported smalldatetime  Date/time that record export status was updated.
avPaymentCreditProduct - For each payment credit, stores which products and/or product subscription the credit applies to. Primarily for reporting purposes.
paymentCreditIDPrimaryint  ID of payment credit.
productIDPrimaryint  ID of product that payment credit applies to.
subscriptionIDPrimaryint  ID of product subscription that payment credit applies to.
invoiceLineItemIDPrimaryint  ID of invoice line item that credit applies to.
avPaymentRefundProduct - For each payment refund, stores which product(s) and/or product subscription(s) the refund applies to. Primarily for reporting purposes.
paymentIDPrimaryint  ID of payment credit.
productIDPrimaryint  ID of product that payment refund applies to.
subscriptionIDPrimaryint  ID of product subscription that payment refund applies to.
invoiceLineItemIDPrimaryint  ID of invoice line item that refund applies to.
avPermission - Stores the various permissions available in the system.
permissionIDPrimaryint  Identity field primary key of permission.
permissionCategoryIDForeignint 0ID of structure variable used to evaluate whether user has permission.
permissionBinaryNumber int 0Binary number representing permission.
userIDForeignint 0ID of user who created permission.
permissionName varchar100''Name of permission.
permissionTitle varchar100''Title of permission as displayed on form to assign permissions.
permissionDescription varchar255''Description of permission.
permissionOrder tinyint 0Order in which permission is listed.
permissionStatus bit 0Indicates whether permission is currently used.
permissionSuperuserOnly bit 0Indicates permission is available only to primary company, not customers.
permissionDateCreated smalldatetime getdate()Date/time permission was created.
permissionDateUpdated smalldatetime getdate()Date/time permission was last updated.
avPermissionAction - For each permission, the list of actions associated with that permission. Enables grouping of similar actions into a single permission.
permissionIDPrimaryint  ID of permission that enforces access to this action.
permissionAction varchar50 Name of action as defined in the source code.
avPermissionCategory - Stores names of structures used to determine whether user have permission based on bitwise permission method. Each permission gets a binary number with a maximum of 32 bits. Requires multiple structures since only supports 31 permissions per structure.
permissionCategoryIDPrimaryint  Identity field primary key of permission category.
permissionCategoryName varchar100''Name of category.
permissionCategoryTitle varchar100''Title of permission category as displayed on form to assign permissions.
permissionCategoryOrder tinyint 0Order in which category is listed.
permissionCategoryDescription varchar255''Description of permission category.
permissionCategoryStatus bit 0Indicates whether category is currently active.
userIDForeignint 0ID of user who created or last updated permission category.
permissionCategoryDateCreated smalldatetime getdate()Date/time permission category was created.
permissionCategoryDateUpdated smalldatetime getdate()Date/time permission category was last updated.
avPermissionTarget - Stores permission settings for each permission category for a target, i.e., users, companies, groups, etc.
permissionTargetIDPrimaryint  Identity field primary key for each permission assigned to a target. Provides full audit capability.
permissionCategoryIDForeignint 0ID of permission category.
primaryTargetIDForeignint 0Target type that is assigned permission, e.g., user, company, group, cobrand, etc.
targetID int 0ID of target type.
userIDForeignint 0ID of user who created or last updated record.
permissionTargetOrder smallint 0Order in which permissions were added. Used to group each instance of permissions for a target.
permissionTargetBinaryTotal int 0Binary number representing permission level.
permissionTargetStatus bit 0Indicates whether permission setting is still valid.
permissionTargetDateCreated smalldatetime getdate()Date/time when target was granted permission.
permissionTargetDateUpdated smalldatetime getdate()Date/time when target permission was updated or made inactive.
avPhone - Stores all versions of each type of phone number for users/companies.
phoneIDPrimaryint  Identity field primary key of phone number.
userIDForeignint 0ID of user the phone number is for.
companyIDForeignint 0ID of company the phone number is for.
userID_authorForeignint 0ID of the user that added this phone number. Is 0 if customer added phone number.
phoneID_parent int 0ID of phone number record that this was updated from. Equals 0 if new phone number.
phoneID_trend int 0ID of phone number from which this phone number was originally based. Equals phoneID if new phone number and not updated version.
phoneVersion smallint 0Version of this phone number, where the first version is 1.
phoneAreaCode varchar5''Area code of phone number.
phoneNumber varchar10''Phone number.
phoneExtension varchar5''Phone number extension.
phoneStatus bit 0Indicates whether this is the current phone number or an archived version.
phoneType varchar25''Type of phone number, e.g., telephone, fax, pager, mobile, etc.
phoneDescription varchar255''Description of phone number.
phoneDateCreated smalldatetime getdate()Date/time phone number was added.
phoneDateUpdated smalldatetime ''Date/time phone number was updated and no longer active.
avPrice - Stores custom pricing options for a particular product or bundle of products.
priceIDPrimaryint  Identity field primary key of price.
companyIDForeignint 0ID of company that created price.
categoryIDForeignint 0ID of category that price applies to.
productIDForeignint 0ID of product that price applies to.
priceAppliesToCategory bit 0Indicates whether price applies to all products in category(s). If so, check avPriceCategory table. Must be percentage or dollar discount, not straight price.
priceAppliesToCategoryChildren bit 0Indicates whether price applies to subcategories of category.
priceAppliesToProduct bit 0Indicates whether price applies to one or more products.
priceAppliesToProductChildren bit 0Indicates whether price automatically applies to all child products of a product.
priceAppliesToAllProducts bit 0Indicates whether price applies to all products in all categories. Must be percentage or dollar discount, not straight price.
priceAppliesToAllCustomers bit 0Indicates whether the price applies to all customers.
priceAppliesToInvoice bit 0Indicates whether custom price applies to entire invoice instead of individual line items.
priceCode varchar25''Unique discount code that customers can enter to receive special price.
priceCodeRequired bit 0Indicates whether code is required to receive price.
userIDForeignint 0User who created price discount.
priceStatus bit 0Indicates whether price is currently active.
priceApproved bit 0Indicates whether price was approved by manager (to avoid accidental pricing errors).
priceDateApproved smalldatetime  Date/time that was price was approved by manager.
userID_approvedForeignint 0ID of user who approved price.
priceName varchar100''Internal reference name of price.
priceDescription varchar255''Description of price.
priceQuantityMinimumPerOrder decimal14.40Minimum quantity per order required to use price.
priceAppliedStatus bit 0Indicates whether price has ever been used.
priceQuantityMaximumAllCustomers decimal14.40Maximum quantity available to all customers at this price before it becomes inactive for all customers.
priceQuantityMaximumPerCustomer decimal14.40Maximum quantity each customer may purchase at this price before it becomes inactive for that customer.
priceBillingMethod varchar255''Billing method that must be used for price to apply. Will be changed to ID later insted of comma-delimited list.
priceDateBegin smalldatetime getdate()Date/time when price becomes active.
priceDateEnd smalldatetime  Date/time when price is no longer active.
priceID_parentForeignint 0ID of price from which this price was copied via either copy or update. Ensures each price is archived and not updated.
priceID_trend int 0ID of custom price that was original version, which could be several layers above parent ID. For reporting purposes. For original version, is priceID.
priceIsParent bit 0Indicates whether price has been updated, and thus copied. Does not apply if price is manually copied.
priceHasMultipleStages bit 0Indicates whether price has multiple stages, i.e, time periods. Used for subscriptions only.
priceSubscriptionUpdateExisting tinyint 0Indicates whether to update price of existing subscriptions using this price.
priceSubscriptionUpdateRenewal tinyint 0Indicates whether to update price when renewing subscriptions using parent price.
priceID_custom varchar50''Custom ID of price. For integration purposes.
priceDateCreated smalldatetime getdate()Date/time price was created.
priceDateUpdated smalldatetime 0Date/time price was last updated.
avPriceStage - A custom price can have 1 or more stages, but mutliple-stage prices are for subscriptions only. Defines the pricing during that stages. Useful for a free trial or introductory price that changes after a designated time interval.
priceStageIDPrimaryint  Identity field primary key for each price/stage combination. Only necessary for purposes of distinct select query.
priceIDPrimaryint  ID of price that this stage applies to.
priceStageOrder tinyint 0Order of price stage, i.e,. when pricing is used within subscription.
priceStageAmount money 0New price, dollar amount of discount off normal price or percentage discount off new price.
priceStageDollarOrPercent bit 0Indicates whether discount amount is based on percent or dollar figure savings.
priceStageNewOrDeduction bit 0Indicates whether discount is new price or discount off normal price (if not percent).
priceStageVolumeDiscount bit 0Indicates whether this price applies as a volume discount based on quantity instead of simple price discount.
priceStageVolumeDollarOrQuantity bit 0Indicates whether volume discount levels are based on dollar value of order (based on normal price) or quantity ordered.
priceStageVolumeStep bit 0Indicates whether price at quantity/price level is the price that applies to all quantity, or whether apply various prices at each step.
priceStageInterval smallint 0Number of intervals that the price is used during subscription.
priceStageIntervalType varchar5''Type of interval for how long price stage is used during subscription, e.g., day, week, month, year.
priceStageText varchar255''Text describing price stage that should be added to invoice line item text.
priceStageDescription varchar255''Internal description of price stage.
avPriceTarget - Stores which custom prices apply to which targets, e.g., companies, users, cobrands, groups, etc.
priceTargetIDPrimaryint  Identity field primary key for each price that applies to target.
priceIDForeignint 0ID of custom price.
primaryTargetIDForeignint 0Target type that this price applies to, e.g., user, company, group, cobrand, etc. Zero if all users.
targetID int 0ID of target type.
userIDPrimaryint  ID of user who granted price to target.
priceTargetStatus bit 0Indicates whether price is currently being applied for target.
priceTargetOrder int 0Order in which to apply price to target as opposed to other prices.
priceTargetDateCreated smalldatetime getdate()Date/time price was applied to target.
priceTargetDateUpdated smalldatetime getdate()Date/time price was made inactive for target.
avPriceVolumeDiscount - Stores the price discount levels for a custom price based on quantity or total dollar value of order of this product.
priceVolumeDiscountIDPrimaryint  Identity field primary key of volume discount.
priceStageIDForeignint 0ID of price stage that volume discount option is for.
priceVolumeDiscountQuantityMinimum decimal14.40Minimum quantity/total price for this level.
priceVolumeDiscountQuantityIsMaximum bit 0Indicates whether this is the maximum value for the price. (quantity = -1).
priceVolumeDiscountIsTotalPrice bit 0Indicates that price is total price for all quantity within quantity range. Enables $100 for up to 50 units, $40 for units 51-75, and $1 each for units 76 and above.
priceVolumeDiscountAmount money 0New price, dollar amount of unit price or percent discount off normal price at this level.
avPrimaryTarget - Stores tables and primary key fields of keys that are target ID used in other tables, such as contact mgmt, notes, and alerts where there are many possible targets of that item. Used instead of a large number of similar tables or empty int columns.
primaryTargetIDPrimaryint  Identity field primary key of target type.
userIDForeignint 0ID of user who created or last updated primary target.
primaryTargetTable varchar50''Name of table that has the target key field as the primary key
primaryTargetKey varchar50''Name of primary key field for the target table.
primaryTargetName varchar100''Name of primary target.
primaryTargetStatus bit 0Indicates whether primary target (table) is still being used.
primaryTargetDescription varchar255''Description of primary target.
primaryTargetDateCreated smalldatetime getdate()Date/time that table was added.
primaryTargetDateUpdated smalldatetime getdate()Date/time target was last updated.
avProduct - Stores each product and product bundle.
productIDPrimaryint  Identity field primary key for each product.
companyIDForeignint 0ID of company that product belongs to.
userID_authorForeignint 0ID of user who created product.
userID_managerForeignint 0Manager or contact person for this product.
vendorIDForeignint 0ID of vendor that is manufacturer of product.
productCode varchar20''Unique code identifying a product. Can be vendor code, used for display on invoice, etc.
productName varchar255''Name of product for internal purposes.
productPrice money 0Normal price of product if no custom pricing.
productPriceCallForQuote bit 0Indicates whether product price is displayed or message that customer should call for a quote instead.
productWeight decimal9.20Shpping weight of product in pounds.
productStatus bit 0Indicates whether product is currently being offered.
productListedOnSite bit 0Indicates whether product is listed on site.
productHasImage bit 0Indicates whether there are any images for this product.
productHasCustomPrice bit 0Indicates whether custom pricing options exist for this product.
productViewCount int 0Number of times this product has been viewed.
productInBundle bit 0Indicates whether product is included in at least one product bundle.
productIsBundle bit 0Indicates whether this product is really a bundle of products.
productIsBundleChanged bit 0For products that are bundles, used to indicate whether bundle contents have changed since product status was made inactive (necessary to edit bundle).
productIsRecommended bit 0Indicates whether product is recommended by other products.
productHasRecommendation bit 0Indicates whether product recommends other products. Suggests product cannot be added to cart, but not assumed.
productIsDateRestricted bit 0Indicates whether product availability is restricted by date.
productIsDateAvailable bit 0Indicates whether product is currently available if has date restrictions.
productID_custom varchar50''Internal custom reference number or code for this product. Especially used for integration.
templateFilename varchar50''Template file used to display product information.
productCatalogPageNumber smallint 0Page number on which this product appears in a paper catalog.
productID_parent int 0ID of product that is the parent of this product, i.e., this is a particular color or model of a product.
productHasChildren bit 0Indicates whether this product has any child products.
productChildOrder smallint 0Order in which product is listed on product page if child of another product.
productChildType tinyint 0Type of child product: 0=not child; 1=variation (e.g., size); 2=upgrade or add-on option.
productChildSeparate bit 0Indicates whether to begin a separate listing of chlid products with this product. Summary can be used for header before new listing.
productInWarehouse bit 0Indicates whether product is stored in warehouse.
productIsRegionRestricted bit 0Indicates whether product is restricted by region.
productHasSpec bit 0Indicates whether product has other specifications as listed in avProductSpec table.
productCanBePurchased bit 0Indicates whether that product can be purchased. Primarily for parent products where customer purchases child product, not parent and parent acts more like a category.
productDisplayChildren bit 0For parent products, indicates whether to display child products on product page.
productHasParameter bit 0Indicates whether product has a parameter option.
productHasParameterException bit 0Indicates whether product has exceptions to parameter combinations, such as unavailable combinations or price premium.
productDateCreated smalldatetime getdate()Date product was created.
productDateUpdated smalldatetime getdate()Date product was last updated.
productIsExported bit  Indicates whether record has been exported. Null = not exported; 0 = Exported but awaiting confirmation the record was imported; 1 = Exported and successfully imported.
productDateExported smalldatetime  Date/time that record export status was updated.
avProductBundle - Stores each version of the product bundle. A new bundle version is created each time the list or quantity of products is updated.
productBundleIDPrimaryint  Identity field primary key for each version of product bundle listing.
productIDForeignint 0ID of product that represents the product bundle.
productBundleVersion smallint 0Version number of overall product bundle. Starts at 1 and increments.
productBundleStatus bit 0Indicates whether this is the current version of the product bundle.
userIDForeignint 0ID of user who created this product bundle.
productBundleDateCreated smalldatetime getdate()Date/time that product was added to bundle.
productBundleDateUpdated smalldatetime getdate()Date/time that product bundle contents was updated.
avProductBundleProduct - Stores the products within a product bundle. Each version of the bundle is archived.
productBundleIDPrimaryint  ID of current version of product bundle.
productIDPrimaryint  ID of product included in bundle.
productBundleProductOrder smallint 0Order inwhich product is listed in bundle and on invoice.
productBundleProductQuantity decimal14.40Quantity of product included in bundle.
avProductCategory - Stores the category(s) that each product is listed in.
productIDPrimaryint  ID of product
categoryIDPrimaryint  ID of category that product is listed in.
productCategoryPrimary bit 0Indicates whether this is the primary category for the product.
productCategoryStatus bit 0Indicates whether product is currently listed in category.
productCategoryOrder int 0Order in which product is listed in category.
productCategoryPage int 0Category page number on which product is listed.
productCategoryDateCreated smalldatetime getdate()Date/time product was first added to category.
productCategoryDateUpdated smalldatetime getdate()Date/time product was removed or re-added to category.
userIDForeignint 0ID of user who added/removed product from category.
productCategoryDateBegin smalldatetime getdate()Date/time to begin listing product in category. Default = now.
productCategoryDateEnd smalldatetime  Optional end date/time for when product is listed in category.
avProductDate - Availiablity date ranges and normal price of products and product bundles.
productDateIDPrimaryint  Identity field primary key for each product availability date range.
productIDForeignint 0ID of product or product bundle.
regionIDForeignint 0ID of region where product is available.
productDateBegin smalldatetime getdate()Date/time that product became available.
productDateEnd smalldatetime  Date/time that product was no longer available.
productDateStatus bit 0Indicates whether this date range is currently active or already passed.
userIDForeignint 0ID of user who created availability period.
productDateDateCreated smalldatetime getdate()Date product date options were added.
productDateDateUpdated smalldatetime getdate()Date product date options were last updated.
avProductLanguage - Product name and description as displayed on public site and invoices in each language.
productLanguageIDPrimaryint  Identity field primary for each product / language combo.
productIDForeignint 0ID of product.
languageIDForeigntinyint 0ID of language.
productLanguageStatus bit 0Indicates whether this is the current version of the product name for this language.
userIDForeignint 0ID of user who created product language info.
productLanguageName nvarchar510''Public name of product displayed on site.
productLanguageName sysname ''Public name of product displayed on site.
productLanguageLineItemName nvarchar510''Name of product as listed on invoice.
productLanguageLineItemName sysname ''Name of product as listed on invoice.
productLanguageSummary nvarchar2000''Summary of product displayed on site.
productLanguageSummary sysname ''Summary of product displayed on site.
productLanguageSummaryHtml bit 0Indicates whether summary is in html format.
productLanguageLineItemDescription nvarchar2000''Summary of product as displayed on invoice.
productLanguageLineItemDescription sysname ''Summary of product as displayed on invoice.
productLanguageLineItemDescriptionHtml bit 0Indicates whether line item summary is in html format or simple text.
productLanguageDescription ntext ''Description of product as displayed on site.
productLanguageDescriptionHtml bit 0Indicates whether product description is in html format.
productLanguageDateCreated smalldatetime getdate()Date/time product info was updated for this language.
productLanguageDateUpdated smalldatetime getdate()Date/time product info was updated for this language.
avProductParameter - Product options available when purchasing, such as color or size.
productParameterIDPrimaryint  Identity field primary key of product parameter.
productIDForeignint 0ID of product that parameter is for.
productParameterName varchar100''Name of product parameter, for internal purposes.
productParameterText varchar100''Name of product parameter as displayed to customers.
productParameterDescription varchar255''Description of product parameter for internal purposes.
productParameterOrder tinyint 0Order in which product parameter is listed.
productParameterImage varchar100''Image that represents all product parameter options, such as image of all colors or relative sizes.
productParameterCodeStatus bit 0Indicates whether the code for this product parameter is added to the custom product ID.
productParameterCodeOrder tinyint 0Order in which product parameter custom ID is listed in productID_custom order.
productParameterStatus bit 0Indicates whether product parameter is currently being used.
productParameterRequired bit 0Indicates whether parameter is optional or required when purchasing product.
userIDForeignint 0ID of user who created or last updated this product parameter.
productParameterExportXml bit 0Indicates whether parameter should be included in XML export.
productParameterExportTab bit 0Indicates whether user-defined field should be included in tab-delimited export.
productParameterExportHtml bit 0Indicates whether user-defined field should be displayed in browser.
productParameterDateCreated smalldatetime getdate()Date/time this product parameter was created.
productParameterDateUpdated smalldatetime getdate()Date/time this product parameter was last updated.
avProductParameterException - Stores exceptions for combinations of product variant options that are either not available, have a custom price or some other issue. For instance, a size is not available in a particular color, or a size is more expensive.
productParameterExceptionIDPrimaryint  Identity field primary key for each exception to a cominatino of product parameter options.
productIDForeignint 0ID of product that parameter exception applies to.
productParameterOptionID1 int 0ID of first product parameter option.
productParameterOptionID2 int 0ID of second product parameter option, if applicable.
productParameterOptionID3 int 0ID of third product parameter option, if applicable.
productParameterOptionID4 int 0ID of fourth product parameter option, if applicable.
productParameterExceptionExcluded bit 0Indicates whether the combination of parameter options is not available.
productParameterExceptionPricePremium money 0Indicates price premium (or discount) for this particular combination.
productParameterExceptionText varchar255''Custom text displayed regarding combination, such as a shipping delay.
productParameterExceptionDescription varchar255''Internal description of exception.
productParameterExceptionStatus bit 0Indicates whether this exception still applies.
productParameterExceptionID_parent int 0ID of exception from which this exception was copied.
userIDForeignint 0ID of user who created this exception.
productParameterExceptionDateCreated smalldatetime getdate()Date/time this exception was created.
productParameterExceptionDateUpdated smalldatetime getdate()Date/time this exception was made inactive and replaced by updated version (if applicable).
avProductParameterOption - Options for each prroduct variant, such as the list of colors or sizes available.
productParameterOptionIDPrimaryint  Identity field primary key of product parameter option.
productParameterIDForeignint 0ID of product parameter.
productParameterOptionLabel varchar100''Name of product parameter option as displayed in list.
productParameterOptionValue varchar100''Value of product parameter option as stored in system.
productParameterOptionOrder smallint 0Order in which product parameter option is listed.
productParameterOptionImage varchar100''Image of parameter option, such as a color sample.
productParameterOptionCode varchar50''Optional code added to productID_custom for SKU to uniquely identify requested product with all parameter.
productParameterOptionStatus bit 0Indiccates whether this product parameter option is currently being offered.
productParameterOptionVersion smallint 0Version of the list of options for this parameter. Each version of the list is archived.
userIDForeignint 0ID of user who submitted this list of parameter options.
productParameterOptionDateCreated smalldatetime getdate()Date/time that this parameter option was created.
productParameterOptionDateUpdated smalldatetime getdate()Date/time this parameter option was made inactive when replaced by an updated list of variant options.
avProductRecommend - Lists recommended products for a particular target product. Product A being recommended for product B is not automatic 2-way.
productID_targetPrimaryint  ID of product that has recommended products for it.
productID_recommend int  ID of product being recommend for target product.
userIDForeignint 0ID of user who added recommendation.
productRecommendStatus bit 0Indicates whether product is currently being recommended.
productRecommendReverse bit 0Indicates whether target product is also recommended for the recommended product.
productRecommendDateCreated smalldatetime getdate()Date/time that product recommendation was added.
productRecommendDateUpdated smalldatetime getdate()Date/time that product recommendation was droped or re-added.
avProductRegion - Stores whether products are specifically available or not available in a particular region.
productIDPrimaryint  ID of product.
regionIDPrimaryint  ID of region.
productRegionStatus bit 0Indicates whether product is specifically available or not in region.
avProductSpec - For each products, store custom specifications that should be displayed on product page. Primarily intended for a table of specs for product children.
productSpecIDPrimaryint  Identity field primary identifying each specification for a product.
productIDForeignint 0ID of product the specification is for.
productSpecName varchar100''Name of product specification used in title.
productSpecValue varchar100''Value of product specification.
productSpecOrder tinyint 0Order in which product specification is listed.
productSpecStatus bit 0Indicates whether product specification is currently used or has been updated.
productSpecVersion smallint 0Version number of set of specifications. Starts at 1 and increments each time the specifications are updated.
productSpecHasImage bit 0Indicates whether the specification value is illustrated using an image, e.g., color samples.
languageIDForeigntinyint 0ID of language that specification is in.
userIDForeignint 0ID of user who added specification.
productSpecDateCreated smalldatetime getdate()Date/time that specification was created.
productSpecDateUpdated smalldatetime getdate()Date/time that specification was updated and thus no longer used.
avRegion - Stores regions used for custom pricing and searching users, customers, and other entities.
regionIDPrimaryint  Identity field primary key uniquely identifying each region.
companyIDForeignint 0ID of company that created region. 0 = default region.
userIDForeignint 0ID of user who created region. 0 = default region.
regionName varchar255''Name of region.
regionCity varchar100''Region city.
regionCounty varchar100''Region county.
regionState varchar100''Region state or province.
regionStateAbbr varchar2''Region state 2-letter abbreviation.
regionZipCode varchar10''Region zip of postal code.
regionZipCodeMax varchar50''Maximum zip code if zip code is a range.
regionCountry varchar100''Region country.
regionStatus bit 0Indicates whether the region is still used.
regionIsBundle bit 0Indicates whether region is a super-region that includes other regions.
regionInBundle bit 0Indicates whether region is included in a super region.
regionDescription varchar255''Description of region.
regionDateCreated smalldatetime getdate()Date/time the region was created.
regionDateUpdated smalldatetime getdate()Date/time the region was last updated.
avRegionBundle - Stores which regions are included in super regions.
regionID_isBundlePrimaryint  ID of super region that includes other regions.
regionID_inBundle int  ID of region included in super region.
avSalesCommission - Stores commission fees paid to salespeople and affiliates.
salesCommissionIDPrimaryint  Identity field primary key identifying each commission record.
commissionIDForeignint 0ID of commission type.
primaryTargetIDForeignint 0Target type that received this commission, e.g., user, company, affiliate, cobrand, etc.
targetID int 0ID of target type.
salesCommissionAmount money 0Amount of commission.
salesCommissionFinalized bit 0Indicates whether commisson amount is final value or temporary value until invoice is paid/closed.
salesCommissionDateFinalized smalldatetime  Date when commission calculation was finalized.
salesCommissionPaid bit 0Indicates whether the commission has been not paid (null), partially paid (0) or fully paid (1).
salesCommissionDatePaid smalldatetime  Date/time the commission has been paid.
salesCommissionStatus bit 0Indicates whether to ignore commission or if is active.
salesCommissionManual bit 0Indicates whether commission fee was added manually instead of automatically by system.
userID_authorForeignint 0User who last manually added/updated commission fee.
companyID_author int 0ID of company that is paying commission.
salesCommissionDateBegin smalldatetime  Optional begin date to period for which commission applies.
salesCommissionDateEnd smalldatetime  Optional end date to period for which commission applies.
salesCommissionBasisTotal money 0Total revenue that is the basis of this commission.
salesCommissionBasisQuantity decimal19.40Total quantity that is the basis of this commission.
commissionStageIDForeignint 0ID of commission stage of commission plan used for this sales commission.
commissionVolumeDiscountIDForeignint 0ID of volume discount commission option used for calculating sales commission of this sales commission.
salesCommissionCalculatedAmount money 0Amount of sales commission as calculated before applying minimum or maximum.
salesCommissionDateCreated smalldatetime getdate()Date/time commission was created.
salesCommissionDateUpdated smalldatetime getdate()Date/time commission was last updated.
salesCommissionIsExported bit  Indicates whether record has been exported. Null = not exported; 0 = Exported but awaiting confirmation the record was imported; 1 = Exported and successfully imported.
salesCommissionDateExported smalldatetime  Date/time that record export status was updated.
avSalesCommissionInvoice - For each sales commission calculated for a period instead of directly for an invoice, matches the invoice(s) and optional line item(s) used to calculate the sales commission.
salesCommissionIDForeignint 0ID of sales commission that this invoice / line item has been used to calculate.
invoiceIDForeignint 0ID of invoice that sales commission applies to.
invoiceLineItemIDForeignint 0IF of line item that sales commission applies to.
salesCommissionInvoiceAmount money 0Total amount of revenue from this invoice or line item used to calculate sales commission.
salesCommissionInvoiceQuantity decimal19.40Total quantity from this invoice or line item used to calculate sales commission.
commissionCustomerIDForeignint 0ID of salesperson / customer relationship that was basis for this sales commission. Equals 0 if affiliate, cobrand or vendor.
avScheduler - Records scripts that are scheduled to run in the ColdFusion Scheduler via CFSCHEDULE tag.
schedulerIDPrimaryint  Identity field primary key of scheduled script.
companyIDForeignint 0ID of company that script is running for.
userIDForeignint 0ID of user who created scheduled script.
schedulerStatus bit 0Indicates whether the scheduled script is still active.
schedulerName varchar150''Name of scheduled script. Must be unique for each server.
schedulerDescription varchar255''Description of scheduled script.
schedulerURL varchar255''URL of scheduled script.
schedulerDateBegin smalldatetime getdate()Date/time to begin running scheduled script.
schedulerDateEnd smalldatetime  Date/time to end scheduled script.
schedulerInterval varchar10''Interval between each time the script is run: # of seconds, days, months, etc.
schedulerRequestTimeOut smallint 0Number of seconds set as timeout period.
schedulerDateCreated smalldatetime getdate()Date/time scheduled script was created.
schedulerDateUpdated smalldatetime getdate()Date/time scheduled script was last updated or made inactive.
avShipping - Records products shipments sent to customer.
shippingIDPrimaryint  Identity field primary key of shipment.
userIDForeignint 0ID of user who recorded shipment.
shippingCarrier varchar50''Name of shipping company.
shippingMethod varchar100''Shipping method, e.g., overnight. May become foreign key later.
shippingWeight decimal9.20Total weight in pounds of shipment, including packaging.
shippingTrackingNumber varchar50''Tracking number from shipping company to track shipment.
shippingInstructions varchar255''Special shipping instructions.
shippingSent bit 0Indicates whether shipment has been sent.
shippingDateSent smalldatetime  Date/time that shipment was sent.
shippingReceived bit 0Indicates whether customer has received shipment.
shippingDateReceived smalldatetime  Date/time that shipment was received by customer.
shippingStatus bit 0Indicates whether shipment was made inactive, i.e., not intending to be sent (but not deleted).
shippingDateCreated smalldatetime getdate()Date/time shipment was recorded.
shippingDateUpdated smalldatetime getdate()Date/time shipment info was last updated.
avShippingInvoice - Stores which order(s) were included with which shipment. Enables an order to be split into multiple shipments or multiple orders to be combined into a single shipment.
invoiceIDPrimaryint  ID of order being shipped.
shippingIDPrimaryint  ID of shipment.
avShippingInvoiceLineItem - For purchases shipped separately, matches which items were shipped in each shipment.
shippingID int 0ID of shipping record.
invoiceLineItemID int 0ID of invoice line item record being shipped.
avStatus - Stores the status options for each company for each type of item that can have a status, including users, customers, invoices, commissions, etc.
statusIDPrimaryint  Identity field primary key for status of item.
companyIDForeignint 0ID of company that status applies to.
userIDForeignint 0ID of user who created or last updated status info.
primaryTargetIDForeignint 0ID of target type that status applies to, e.g., company, invoice, etc.
statusName varchar100''Internal name of status.
statusTitle varchar100''Status as displayed to customer, if applicable.
statusDisplayToCustomer bit 0Indicates whether customer may view this particular status.
statusDescription varchar255''Internal description of status.
statusOrder smallint 0Order in which status is listed.
statusStatus bit 0Indicates whether status setting is currently being used.
statusID_custom varchar50''Custom ID of status. For integration purposes.
statusDateCreated smalldatetime getdate()Date/time that status option was created.
statusDateUpdated smalldatetime getdate()Date/time that status option was last updated.
avStatusHistory - Records history of status changes for each type of item, such as users, companies, invoices, etc.
statusHistoryIDPrimaryint  Identity field primary key identifying changes to status.
userIDForeignint 0ID of user who updated status.
primaryTargetIDForeignint 0ID of primary target type that is the item whose status is being changed.
targetID int 0ID of target that had status changed.
statusHistoryStatus bit 0Indicates whether this is the current status of the object.
statusIDForeignint 0ID of new status that status was changed to.
statusHistoryManual bit 0Indicates that status was changed manually by user instead of automatically.
statusHistoryComment varchar255''Optional comment recorded by user when changing status.
statusHistoryDateCreated smalldatetime getdate()Date/time that status change was recorded.
statusHistoryDateUpdated smalldatetime getdate()Date/time that status updated after this status changed and this status no longer current.
avStatusTarget - For each company / target combination, stores the various export options for custom status.
companyIDPrimaryint  ID of company with custom status options.
primaryTargetIDPrimaryint  ID of target type of custom status.
statusTargetExportXmlStatus bit 0Indicates whether to include custom status in XML export.
statusTargetExportXmlName varchar100''Variable name to use when exporting custom status in XML export.
statusTargetExportTabStatus bit 0Indicates whether to include custom status in tab-delimited export.
statusTargetExportTabName varchar100''Column header when exporting custom status in tab-delimited export.
statusTargetExportHtmlStatus bit 0Indicates whether to include custom status when displaying in browser.
statusTargetExportHtmlName varchar100''Column header when exporting custom status when displaying in browser.
userIDForeignint 0ID of user who created or last updated settings.
statusTargetDateCreated smalldatetime getdate()Date/time that status options were created.
statusTargetDateUpdated smalldatetime getdate()Date/time that status options were last updated.
avSubscriber - Stores companyID/userID combination that is subscribed to various products. Enables a single customer to receive multiple invoices.
subscriberIDPrimaryint  Identity field primary key identifying each subscriber.
companyIDForeignint 0ID of company that is the subscriber.
userIDForeignint 0ID of user that is the contact for the subscriber company.
userID_authorForeignint 0ID of user who added or last updated subscriber.
userID_cancelForeignint 0ID of user who canceled subscriber.
companyID_authorForeignint 0ID of client that provides service to subscriber.
subscriberName varchar100''Internal reference name for subscriber (optional).
subscriberStatus bit 0Indicates whether subscriber is currently active.
subscriberCompleted bit 0Indicates whether subscriber has completed all current subscriptions.
subscriberDateProcessNext smalldatetime  Date/time when subscriber invoice is scheduled to be processed next.
subscriberDateProcessLast smalldatetime  Date/time when subscriber invoice was last processed.
subscriberID_custom varchar50''Custom ID of subscriber as defined by customer.
addressID_billingForeignint 0ID of billing address.
addressID_shippingForeignint 0ID of shipping address.
subscriberDateCreated smalldatetime getdate()Date/time that subscriber was created.
subscriberDateUpdated smalldatetime getdate()Date/time that subscriber was last updated.
subscriberIsExported bit  Indicates whether record has been exported. Null = not exported; 0 = Exported but awaiting confirmation the record was imported; 1 = Exported and successfully imported.
subscriberDateExported smalldatetime  Date/time that record export status was updated.
avSubscriberNotify - Notification options for each user for each subscription.
subscriberIDPrimaryint  ID of subscriber.
userIDPrimaryint  ID of user to notify.
userID_authorForeignint 0ID of user who added notification for this user.
userID_cancelForeignint 0ID of user who canceled notification for user.
addressIDForeignint 0ID of address to mail paper notification.
subscriberNotifyStatus bit 0Indicates whether user is still being notified.
subscriberNotifyEmail bit 0Indicates whether to notify user via email.
subscriberNotifyEmailHtml bit 0Indictes whether to send email in text or html format.
subscriberNotifyPdf bit 0Indicates whether to generate invoice in PDF format (which can be emailed if selected).
subscriberNotifyDoc bit 0Indicates whether to generate invoice in MS Word format (which can be emailed if selected).
phoneIDForeignint 0ID of fax number to fax notification to.
subscriberNotifyDateCreated smalldatetime getdate()Date/time that user was added to be notified.
subscriberNotifyDateUpdated smalldatetime getdate()Date/time that user notification options were last updated.
avSubscriberPayment - Stores automated billing option for subscriptions, if applicable.
subscriberPaymentIDPrimaryint  Identity field primary key of each payment option for subscription.
subscriberIDForeignint 0ID of subscriber.
creditCardIDForeignint 0ID of credit card that invoice should be billed to.
bankIDForeignint 0ID of bank account that invoice should be charged to via ACH transaction.
userID_authorForeignint 0ID of user who added payment information.
userID_cancelForeignint 0ID of user who cancelled this payment option, either by changing status to inactive or updating, and thus replacing.
subscriberPaymentStatus bit 0Indicates whether this billing method is currently active.
subscriberPaymentDateCreated smalldatetime getdate()Date/time this payment method was added.
subscriberPaymentDateUpdated smalldatetime getdate()Date/time that payment option was replaced and thus made inactive.
avSubscriberProcess - Stores the record each time the subscriber is processed. Used for tracking progress of current subscriptions and for historical reporting/auditing.
subscriberProcessIDPrimaryint  Identity field primary key for each time the subscriber is processed.
subscriberIDForeignint 0ID of subscriber being processed.
subscriberProcessDate smalldatetime getdate()Date that this subscriber processing began.
invoiceIDPrimaryint  ID of invoice for this subscriber processing.
subscriberProcessExistingInvoice bit 0Indicates whether invoice was created for subscriber processing or if invoice already existed (created manually).
subscriberProcessCurrent bit 0Indicates this is the current subscriber processing, i.e, for the latest invoice.
subscriberProcessStatus tinyint 0Current status of subscriber processing.
subscriberProcessAllQuantitiesEntered bit 0Indicates whether the quantity for all subscription with variable quantiy have been entered.
subscriberProcessDateCreated smalldatetime getdate()Date/time that subscriber processing entry was created. Can be before processing date if updating subscription quantities.
subscriberProcessDateUpdated smalldatetime getdate()Date/time that subscriber processing entry was last updated.
avSubscription - Stores the list of products (both custom and normal) to which subscriber is subscribed. All updates are created as new subscriptions for archive purposes.
subscriptionIDPrimaryint  Identity field primary key identifying each product subscription.
subscriberIDForeignint 0ID of subscriber that subscription is for.
userID_authorForeignint 0ID of user who added or last updated subscription for subscriber.
userID_cancelForeignint 0ID of user who canceled subscription.
subscriptionStatus bit 0Indicates whether subscription is still active or was cancelled.
subscriptionCompleted bit 0Indicates whether subscription completed successfully.
subscriptionDateBegin smalldatetime getdate()Date/time when subscription begins.
subscriptionDateEnd smalldatetime  Date/time when subscription ends.
subscriptionContinuesAfterEnd bit  Indicates whether subscription continues indefnitely beyond max applied number or end date. 1=yes, 0=no. Does not apply if subscriptions continues indefinitely anyway.
subscriptionEndByDateOrAppliedMaximum bit  Indicates whether subscription continues indefinitely (null), ends at a specified date (0) or ends after a maximum number of being applied (1).
subscriptionAppliedMaximum smallint 0Maximum number of times the subscription should be applied, i.e., added to invoice. Can be used instead of specifying dates. 0 = unlimited.
subscriptionAppliedCount smallint 0Number of times the subscription has been applied (i.e., added to invoice).
subscriptionIntervalType varchar5''Type of interval between subscription being added to invoice, e.g., day, week, month, year.
subscriptionInterval smallint 0Number of intervals between each subscription being applied to invoice.
subscriptionID_parent int 0ID of parent subscription used to create this subscription when parent was updated, i.e., replaced with this subscription.
subscriptionID_trend int 0ID of original parent subscription used to create this subscription when parent(s) was updated, i.e., replaced with this subscription.
productIDForeignint 0ID of product that subscription pertains to.
priceIDForeignint 0ID of custom price used for this subscription.
priceStageIDForeignint 0ID of price stage this subscription is currently using, meaning the first price stage that applies next time the subscription is processed.
regionIDForeignint 0ID of region for this subscription, typically for pricing purposes but could also be for categoring line items on invoice.
categoryIDForeignint 0ID of product category, if applicable. Primarily for reporting purposes, but also useful for pricing.
subscriptionProductID_custom varchar50''Custom productID for subscription entered manually, from real product or via ID defined by parameters.
productParameterExceptionIDForeignint 0ID of product parameter exception for subscriptions with parameters and an exception.
subscriptionQuantity decimal10.40Quantity of product to be included in subscription.
subscriptionQuantityVaries bit 0Indicates whether subscription quantity is set or varies for each billing period.
subscriptionOrder smallint 0Order in which subscription is listed on invoice.
subscriptionName nvarchar510''Name of subscribed product as listed on invoice.
subscriptionName sysname ''Name of subscribed product as listed on invoice.
subscriptionDescription nvarchar2000''Description of subscribed product as listed on invoice.
subscriptionDescription sysname ''Description of subscribed product as listed on invoice.
subscriptionDescriptionHtml bit 0Indicates whether description is in html format.
subscriptionPriceNormal money 0Normal price of product.
subscriptionPriceUnit money 0Unit price of product, depending on volume discount custom pricing.
subscriptionDiscount money 0Discount applied to subscription (subtracted from price * quantity).
subscriptionDateProcessNext smalldatetime  Date that subscription should be added to next invoice.
subscriptionDateProcessLast smalldatetime  Date that subscription was last added to invoice.
subscriptionProRate bit 0Indicates whether to pro-rate subscription in partial billing periods (mainly first billing period).
subscriptionID_custom varchar50''Custom ID of subscription as defined by customer.
subscriptionCategoryMultiple bit 0Indicates whether subscription is for a product listed in more than one category. Not currently enabled.
subscriptionDateCreated smalldatetime getdate()Date/time that subscription was added.
subscriptionDateUpdated smalldatetime getdate()Date/time that subscription was last updated, i.e., made inactive and replaced by child subscription.
avSubscriptionParameter - For subscription with parameters, stores the selected parameter option(s) for each subscription.
subscriptionIDPrimaryint  ID of subscription.
productParameterOptionIDPrimaryint  ID of product parameter option.
avSubscriptionProcess - For each time the subscriber is processed, stores the quantity for each variable-quantity subscription. Allows client to periodically update quantities. Rows are deleted after invoice is generated.
subscriberProcessIDPrimaryint  ID of subscriber processing.
subscriptionIDPrimaryint  ID of subscription.
priceStageIDForeignint 0ID of price stage that subscription quantity for this billing period applies to. Necessary for billing periods that span more than one price stage.
subscriptionProcessQuantity decimal10.4 Quantity of subscription to add to invoice for variable-quantity subscriptions. Null means to use default quantity specified for subscription.
subscriptionProcessQuantityFinal bit 0Indicates the subscription quantity is finalized.
subscriptionProcessDateBegin smalldatetime  Beginning date/time of the time period for which this subscription quantity applies.
subscriptionProcessDateEnd smalldatetime  Ending date/time of the time period for which this subscription quantity applies.
avTask - Stores alerts, or calender of scheduled reminders, for users of the system.
taskIDPrimaryint  Identity field primary key for each alert.
userID_authorForeignint 0User who created the alert.
userID_agentForeignint 0User assigned to complete the task.
companyID_agentForeignint 0ID of company assigned to the task. Useful if not assigned to a particular user.
userID_targetForeignint 0ID of user the task is about.
companyID_targetForeignint 0ID of company the task is about.
primaryTargetIDForeignint 0Identifies subject type the alert is related to, e.g., user, company, invoice, etc.
targetID int 0Primary key of alert subject, e.g., user ID, company ID, invoice ID, etc.
taskStatus bit 0Yes/no indicator for whether alert is active or inactive, i.e., whether it should be ignored or not.
taskCompleted bit 0Yes/no indicator for whether alert has been successfully completed.
taskMessage varchar500''Text describing what the alert is about and the required action.
taskDateScheduled smalldatetime getdate()Date/time when the alert should be completed.
taskDateCreated smalldatetime getdate()Date/time when alert was created.
taskDateUpdated smalldatetime getdate()Date/time when alert was last updated.
avTax - Stores each tax charged to customers. Can apply as default tax to all primary companies or each company can create their own.
taxIDPrimaryint  Identity field primary key for each tax.
companyIDForeignint 0ID of primary company that tax applies to. If 0, applies to all companies.
userIDForeignint 0ID of user who created tax.
taxName varchar100''Name of tax.
taxDescription varchar255''Description of tax.
taxAmount money 0Dollar amount of tax if simple dollar amount.
taxPercentage decimal6.40Percentage of total used to calculate tax.
taxAmountOrPercentage bit 0Indicates whether to use tax amount or percentage.
taxAmountMinimum money 0Maximum dollar amount of tax. Applies if using percentage.
taxAmountMaximum money 0Maximum amount of tax. Applies if using percentage.
taxPurchaseMinimum money 0Minimum dollar amount of purchase that is taxable.
taxPurchaseMaximum money 0Maximum dollar amount of purchase that is taxable.
taxTypeID int 0Type of tax, basically category for classifying.
taxOrder int 0Order in which tax is listed for customer and when viewing list of taxes.
taxStatus bit 0Indicates whether tax is currently active.
taxIsRegional bit 0Indicates whether tax applies to specified regions only.
taxOnInvoiceOrLineItem bit 0Indicates whether tax applies to invoice instead of individual line item.
taxOnQuantityOrAmount bit 0Indicates whether tax is calculated based on quantity or dollar amount.
taxDateBegin smalldatetime  Date/time that tax beings being applied.
taxDateEnd smalldatetime  Date/time that tax is no longer applied.
taxExempt bit 0Indicates whether a tax-exempt company should be charged this tax.
taxID_parent int 0ID of parent tax used to create this tax, i.e., when tax was updated.
taxDateCreated smalldatetime getdate()Daet/time that tax was created.
taxDateUpdated smalldatetime getdate()Date/time that tax was last updated.
avTaxCategory - Stores which taxes are applied to all products in a particular category. Stores all instances in case tax is added or temporarily suspended.
taxCategoryIDPrimaryint  Identity field primary key for each instance tax was added to or removed from category.
taxIDForeignint 0ID of tax.
categoryIDForeignint 0ID of category.
companyIDForeignint 0ID of company that tax applies to.
userIDForeignint 0ID of user who added tax to category.
taxCategoryChildren bit 0Indicates whether tax applies to sub-categories of this category.
taxCategoryStatus bit 0Indicates tax is still active for all products in category.
taxCategoryDateBegin smalldatetime  Date/time tax begins applying to product category.
taxCategoryDateEnd smalldatetime  Date/time tax is no longer applied to category.
taxCategoryDateCreated smalldatetime getdate()Date/time tax was added to category.
taxCategoryDateUpdated smalldatetime getdate()Date/time tax was removed from category or re-enabled.
avTaxLanguage - For each tax/language combination for each company, stores the name of the tax as displayed to customers.
taxIDPrimaryint  ID of tax.
languageID tinyint  ID of language.
taxLanguageName nvarchar200''Name of tax as displayed to customer.
taxLanguageName sysname ''Name of tax as displayed to customer.
taxLanguageDescription nvarchar510''Description of tax as displayed to customer
taxLanguageDescription sysname ''Description of tax as displayed to customer
userIDForeignint 0ID of user who created tax description.
companyIDForeignint 0ID of company that tax description for this language applies to.
taxLanguageDateCreated smalldatetime getdate()Date/time tax description was created for this language.
taxLanguageDateUpdated smalldatetime getdate()Date/time tax description was last updated for this language.
avTaxProduct - Stores taxes that are applied to each product.
taxProductIDPrimaryint  Identity field primary key for each tax / product combo.
taxIDForeignint 0ID of tax.
companyIDForeignint 0ID of company that tax applies to.
productIDForeignint 0ID of product.
userIDForeignint 0ID of user who added tax for product.
taxProductChildren bit 0Indicates whether tax applies to product children.
taxProductStatus bit 0Indicates whether tax is currently applied to product.
taxProductDateBegin smalldatetime  Date/time tax begins applying to product.
taxProductDateEnd smalldatetime  Date/time tax is no longer applied to product.
taxProductDateCreated smalldatetime getdate()Date/time tax was added to product.
taxProductDateUpdated smalldatetime getdate()Date/time tax was disabled or last updated for product.
avTaxRegion - Stores which taxes apply to which region for each company. Enables some companies to be required to charge tax while others are not. If not specified, tax applies to all customers.
taxRegionIDPrimaryint  Identity field primary key linking tax to region and company.
taxIDForeignint 0ID of tax.
regionIDForeignint 0Region where tax applies.
taxRegionStatus bit 0Indicates whether tax is currently active in that region.
companyIDForeignint 0Company that tax region applies to.
userIDForeignint 0User who added tax to region for company.
taxRegionDateBegin smalldatetime  Date/time that tax begins being applies to region.
taxRegionDateEnd smalldatetime  Date/time that tax no longer applies for company in region.
taxRegionDateCreated smalldatetime getdate()Date/time that tax was added to region for company.
taxRegionDateUpdated smalldatetime getdate()Date/time that tax was disabled or region for locale.
avTaxType - For categorizing taxes into various types. Using type instead of category to avoid confusion with product categories.
taxTypeIDPrimaryint  Identity field primary key for tax type.
userIDForeignint 0User who created tax type.
companyIDForeignint 0ID of company that tax type applies to. If 0, applies to all companies.
taxTypeName varchar100''Name of tax type.
taxTypeLabel varchar100''Name of tax type as displayed to customers.
taxTypeDescription varchar2550Description of tax type.
taxTypeOrder int 0Order in which tax type is listed.
taxTypeDisplay bit 0Indicates whether to display tax type to customer.
taxTypeDateCreated smalldatetime getdate()Date/time tax type was created.
taxTypeDateUpdated smalldatetime getdate()Date/time tax type was last updated.
avTemplate - Stores the templates used by each company to display homepage, category page, product page, etc.
templateIDPrimaryint  Identity field primary key for each template.
companyID int 0Company that template is for.
userID int 0User who created template.
templateName varchar100''Internal name of template.
templateFilename varchar50''Filename of template.
templateType varchar50''Type of template (home, category, product)
templateDescription varchar255''Description of template. For internal purposes only.
templateStatus bit 0Indicates whether template is currently used.
templateDefault bit 0Indicates this is the default template for this template type.
templateXml text ''Stores the variables in XML format used to customize the template.
templateDateCreated smalldatetime getdate()Date and time the template was created.
templateDateUpdated smalldatetime getdate()Date/time the template was last updated.
avUser - Stores users.
userIDPrimaryint  Identity field primary key for each user.
companyIDForeignint 0ID of primary company for user.
username varchar50''Username of user for login. If blank, no login privilege.
password varchar100''Encrypted password. If blank, no login privilege.
userStatus bit 0Indicates whether user has permission to log in.
firstName varchar50''First name of user.
middleName varchar50''Middle name or initial of user.
lastName varchar50''Last name of user.
suffix varchar10''Suffix after last name, e.g, Sr.
salutation varchar10''How to address, e.g., Mr. or Ms.
email varchar100''Email address of user.
languageIDForeigntinyint 0ID of language used for customer, if different than company.
userID_custom varchar50''Internal ID for user. Useful for integration.
jobTitle varchar100''Job title of user.
jobDepartment varchar100''Company department of user.
jobDivision varchar100''Company division of user.
userID_authorForeignint 0ID of user who created this user. Equals 0 if user created self.
userNewsletterStatus bit 0Indicates whether user is subscribed to newsletter.
userNewsletterHtml bit 0Indicates whether user prefers to receive newsletter in text or html format.
userDateCreated smalldatetime getdate()Date/time user was created.
userDateUpdated smalldatetime getdate()Date/time user was last updated.
userIsExported bit  Indicates whether record has been exported. Null = not exported; 0 = Exported but awaiting confirmation the record was imported; 1 = Exported and successfully imported.
userDateExported smalldatetime  Date/time that record export status was updated.
userEmailVerified bit  Indicates whether user has verified their email: Null=Not Requested, 0=no, 1=yes.
userEmailVerifyCode varchar35''Code to uniquely identify user for verifying email.
userEmailDateVerified smalldatetime  Date/time that user verified their email.
avUserCompany - Stores which users are members of and have permissions for which companies.
userIDPrimaryint  ID of user who is member of company.
companyIDPrimaryint  ID of company.
userCompanyStatus bit 0Indicates whether user is currently member of company.
userCompanyDateCreated smalldatetime getdate()Date/time that user was added to company.
userCompanyDateUpdated smalldatetime getdate()Date/time that user was removed from company or re-added.
avUserProductFavorite - Stores favorite products for each user.
productIDForeignint 0ID of product that is favorite or ordered.
userIDForeignint 0ID of user who product is favorite for.
userProductFavoriteQuantity decimal14.40Quantity preferred.
userProductFavoriteStatus bit 0Indicates whether product is currently user favorite.
userProductFavoriteDateCreated smalldatetime getdate()Date/time that product was added as favorite.
userProductFavoriteDateUpdated smalldatetime getdate()Date/time that product was disabled as favorite.
avUserProductWishList - Stores products that a user wants to purchase later or wants other users to purchase for him/her.
productIDPrimaryint  ID of product.
userIDPrimaryint  ID of user.
userProductWishListQuantity decimal14.40Quantity of product requested.
userProductWishListStatus bit 0Indicates whether product is still on wish list.
userProductWishListFulfilled bit 0Indicates whether product has been ordered by/for customer.
invoiceLineItemIDForeignint 0ID of line item of purchase which fulfilled this wish list request.
userProductWishListComment varchar255''Customer comment about product on wish list.
userProductWishListRating tinyint 0Rating on scale of 1-5 about how much this person wants the item (1 being highest).
addressIDForeignint 0Shipping address for wish list product to be sent to.
userProductWishListDateCreated smalldatetime getdate()Date/time when item was added to wish list.
userProductWishListDateUpdated smalldatetime getdate()Date/time when item was last updated in wish list.
avVendor - Stores companies that are vendors to the primary company.
vendorIDPrimaryint  Identity field primary key identifying company as a vendor.
companyIDForeignint 0ID of company that is the vendor.
userIDForeignint 0ID of user that is the contact person for the vendor.
vendorCode varchar25''Unique code that identifies vendor. Useful for including in product codes, searching or integration.
companyID_authorForeignint 0ID of company that the vendor is a vendor for.
userID_authorForeignint 0User who added vendor.
vendorDescription varchar2000''Description of vendor that can be displayed to customers or used for internal purposes.
vendorDescriptionHtml bit 0Indicates whether vendor description should be displayed in text or html format.
vendorDescriptionDisplay bit 0Indicates whether to display vendor description to customer.
vendorURL varchar100''URL to vendor site.
vendorURLdisplay bit 0Indicates whether to display vendor URL to customer.
vendorName varchar255''Name of vendor.
vendorImage varchar100''URL to vendor logo image.
vendorStatus bit 0Indicates whether vendor is currently active.
vendorID_custom varchar50''Internal number used to refer to vendor. Useful for integration.
vendorDateCreated smalldatetime getdate()Date vendor was added.
vendorDateUpdated smalldatetime getdate()Date vendor was last updated.
vendorIsExported bit  Indicates whether record has been exported. Null = not exported; 0 = Exported but awaiting confirmation the record was imported; 1 = Exported and successfully imported.
vendorDateExported smalldatetime  Date/time that record export status was updated.