iVend Database Database : CusCustomer Table

Creation Date

4/13/2015 12:00 PM

File Group

PRIMARY

Text File Group

System Object

Published for Replication

Rows

205

Data Space Used

64.00 KB

Index Space Used

80.00 KB

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

CustomerKey

System generated Primary key of the table.

VarWChar

50

Id

Refers to the customer code

VarWChar

20

FirstName

Refers to the customer's first name.

VarWChar

100

MiddleName

Refers to the customer's middle name.

VarWChar

50

LastName

Refers to the customer's last name.

VarWChar

40

Salutation

Refers to the salutation.

VarWChar

50

Title

Refers to the title.

VarWChar

50

NickName

Refers to the customer's nick name.

VarWChar

50

CompanyName

Refers to the company to which the customer is associated.

VarWChar

50

AddressKey

Refers to the key of the customer's address.See also Address

VarWChar

50

BillingAddressKey

Refers to the key of the customer's billing address.See also Address

VarWChar

50

ShippingAddressKey

Refers to the key of the customer's shipping address.See also Address

VarWChar

50

HomeCurrencyKey

For future use

VarWChar

50

Email

Refers to the customer's email id.

VarWChar

100

WebPage

Refers to the customer's webpage.

VarWChar

100

LoyalityId

For future use

VarWChar

40

AccountingID

Maps to the ERP system unique id. It is a system generated number.

VarWChar

50

IsTaxExempt

Flag to check whether the customer is tax exempted or not.

Boolean

1

TaxNumber

Customer tax number or Federal tax id

VarWChar

40

CustomerGroupKey

Refers to the customer group.See also Customer Group

VarWChar

50

SalesTaxCodeKey

Refers to the sales tax code key.See also Tax

VarWChar

50

StoreKey

Refers to the store key.See also Store

VarWChar

50

IsActive

Flag to check whether the customer is active or not.

Boolean

1

OnHold

Flag to check whether the customer is on hold or not.

Boolean

1

IsMarketing

To check that the customer is only for the marketing purposes.

Boolean

1

PhoneNumber

Refers to the phone number

VarWChar

50

AlternatePhoneNumber

Refers to any alternate phone numbers.

VarWChar

50

MobilePhone

Refers to the mobile phone number.

VarWChar

50

FaxNumber

Refers to the fax number.

VarWChar

50

ShippingTypeKey

Refers to the shipping type.

VarWChar

50

PrimaryContactKey

Refers to the primary contact(in case the customer is not a single person but a company etc.). See also Contact

VarWChar

50

IsTemplate

For future use

Boolean

1

RequireRenter

For future use

Boolean

1

RequireSignature

For future use

Boolean

1

CreditCardKey

Refers to the default credit card of the customer. See also Credit Card

VarWChar

50

PriceListKey

Refers to the pricelist applicable to the customer.See also Price List

VarWChar

50

MultiCurrency

For future use

Boolean

1

((1))

HasLoyaltyId

For future use

Boolean

1

CustomerDiscount

Specifes the default sale discount for the customer.

Numeric

9 (20,5)

Created

Date of creation of this record

DBTimeStamp

4

CreatedBy

Reference of user who has created this record. See also Security User

VarWChar

50

Modified

Date of last modification of this record

DBTimeStamp

4

ModifiedBy

Reference of user who has modified this record. See also Security User

VarWChar

50

IsDeleted

Value is True if the record has been deleted from the system. Record is always Soft Deleted

Boolean

1

((0))

PaymentTypeKey

Specify the default paymet method associated with the customer. Please refer to Payment Type also

VarWChar

50

ElectronicId

Specify the Customer Electronic Card Id. Swiping this card would populate the customer in the transaction at the POS

VarWChar

40

Gender

Specify the Gender of Customer (Male / Female)

VarWChar

50

BirthDate

Stores the Customer's Birth Date.

DBTimeStamp

4

AnniversaryDate

Stores the Customer's Anniversary Date.

DBTimeStamp

4

IsLoyaltyMember

The True value specifies that the customer is a loyalty member and can take advantage of loyalty benefits provided by organization.

Boolean

1

((0))

CanOrderItems

The value is True if the special orders are allowed from this customer.

Boolean

1

((1))

TaxCompanyType

Stores the value for the Vendor Type

Valid values are

Company = 0

Private = 1

Integer

4

((0))

CustomerDiscountGroupKey

Refers to the CustomerDiscountGroup applicable to the customer.See also CustomerDiscountGroup

VarWChar

50

CustomerPriceGroupKey

Refers to the CustomerPriceGroup applicable to the customer.See also CusCustomerPriceGroup

VarWChar

50

IsTaxFree

Value is true if the customer is tax free

Boolean

1

((0))

IsGDPR

Mark Customer as Gdpred

Boolean

1

((0))

Index

Description

Primary

Unique

IX_CusCustomer

IX_CusCustomer_1

PK_CusCustomer

Database Object

Object Type

Description

Dep Level

BarCodeResolution

Stored Procedure

1

GetMobileReplicationData

Stored Procedure

1

GetNextAutoGeneratedCode

Stored Procedure

Generates the next code depending on the Source Type

1

Integration_Consolidated_Transaction

Stored Procedure

1

Integration_Consolidated_TransactionPayment

Stored Procedure

1

Integration_GoodsReceipt

Stored Procedure

1

Integration_StockTransfer

Stored Procedure

1

Integration_TransactionPayment

Stored Procedure

1

Loy_GetTransactionHeader

Stored Procedure

1

Loyalty_ActiveBillValue

View

1

Loyalty_ActiveTransactionCount

View

1

Loyalty_GenderInfo

View

1

Rpt_ActiveLoyalty

User Defined Function

1

rpt_LayAway

Stored Procedure

1

rptSalesData

User Defined Function

1

rptSalesTransactionData

User Defined Function

1

rptSalesTransactionDataWithSalesPerson

User Defined Function

1

SalesAnalysisbySalesPersons

Stored Procedure

2

SalesDetail

User Defined Function

1

Trg_TrxScannedDataHeader

Trigger

1

TrxCompleteTransactionUpdates

Stored Procedure

1

uspGetItemPrice

Stored Procedure

1

uspGetItemPriceForNAV

Stored Procedure

1

uspValidateCustomerTaxNumber_Ecuador

Stored Procedure

1

vw_CusCustomer

View

1

vw_GrossProfit

View

1

vw_SalesReportData

View

1

vwAnalytics_Sales

View

1

vwAnalytics_SalesTransaction

View

1

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[CusCustomer](

[CustomerKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Id] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[FirstName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[MiddleName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[LastName] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Salutation] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Title] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[NickName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CompanyName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[AddressKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[BillingAddressKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[ShippingAddressKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[HomeCurrencyKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Email] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[WebPage] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[LoyalityId] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[AccountingID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[IsTaxExempt] [bit] NOT NULL,

[TaxNumber] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CustomerGroupKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[SalesTaxCodeKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[StoreKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[IsActive] [bit] NOT NULL,

[OnHold] [bit] NOT NULL,

[IsMarketing] [bit] NOT NULL,

[PhoneNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[AlternatePhoneNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[MobilePhone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[FaxNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ShippingTypeKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[PrimaryContactKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[IsTemplate] [bit] NOT NULL,

[RequireRenter] [bit] NOT NULL,

[RequireSignature] [bit] NOT NULL,

[CreditCardKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[PriceListKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[MultiCurrency] [bit] NOT NULL,

[HasLoyaltyId] [bit] NOT NULL,

[CustomerDiscount] [decimal](20, 5) NOT NULL,

[Created] [datetime] NOT NULL,

[CreatedBy] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Modified] [datetime] NOT NULL,

[ModifiedBy] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[IsDeleted] [bit] NOT NULL,

[PaymentTypeKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[ElectronicId] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Gender] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[BirthDate] [datetime] NULL,

[AnniversaryDate] [datetime] NULL,

[IsLoyaltyMember] [bit] NOT NULL,

[CanOrderItems] [bit] NOT NULL,

[TaxCompanyType] [int] NOT NULL,

[CustomerDiscountGroupKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CustomerPriceGroupKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[IsTaxFree] [bit] NOT NULL,

[IsGDPR] [bit] NOT NULL,

CONSTRAINT [PK_CusCustomer] PRIMARY KEY CLUSTERED

(

[CustomerKey] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [dbo].[CusCustomer] ADD CONSTRAINT [DF_CusCustomer_MultiCurrrency] DEFAULT ((1)) FOR [MultiCurrency]

ALTER TABLE [dbo].[CusCustomer] ADD CONSTRAINT [DF_CusCustomer_IsDeleted] DEFAULT ((0)) FOR [IsDeleted]

ALTER TABLE [dbo].[CusCustomer] ADD CONSTRAINT [DF_CusCustomer_IsLoyaltyMember] DEFAULT ((0)) FOR [IsLoyaltyMember]

ALTER TABLE [dbo].[CusCustomer] ADD CONSTRAINT [DF_CusCustomer_CanOrderItems] DEFAULT ((1)) FOR [CanOrderItems]

ALTER TABLE [dbo].[CusCustomer] ADD CONSTRAINT [DF_CusCustomer_TaxCompanyType] DEFAULT ((0)) FOR [TaxCompanyType]

ALTER TABLE [dbo].[CusCustomer] ADD CONSTRAINT [DF_CusCustomer_IsTaxFree] DEFAULT ((0)) FOR [IsTaxFree]

ALTER TABLE [dbo].[CusCustomer] ADD CONSTRAINT [DF__CusCustom__IsGDP__54425081] DEFAULT ((0)) FOR [IsGDPR]