iVend Database Database : CfgLoyaltyConfig Table

Creation Date

4/13/2015 12:00 PM

File Group

PRIMARY

Text File Group

System Object

Published for Replication

Rows

0

Data Space Used

0.00 KB

Index Space Used

0.00 KB

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

LoyaltyConfigKey

Primary Key

VarWChar

50

RegistrationMethod

Refers to the type of Registration Method.

Valid values are:

CustomerCode = 1,

LoyaltyCard = 2,

LoyaltyCardAttachedToCustomerCode = 3

Integer

4

AwardBonusPointsOnRegistration

Specify the bonus points on registration

Integer

4

AwardBonusCurrencyUnitsOnRegistration

Specify the bonus currency on registration

Numeric

9 (18,2)

PlanDefinitionBasis

Refers to the type of Plan Definition Basis.

Valid values are:

EveryTransaction = 1,

CumulativeBasis = 2

Integer

4

SpecialDaysForCustomerSetting

Refers to the type of Special Days For Customer Setting.

Valid values are:

Day = 1,

Week = 2,

Month = 3,

DaysBackwardForward = 4

Integer

4

ConflictResolutionOption

Refers to the type of Conflict Resolution Option.

Valid values are:

Global = 1,

PlanGlobal = 2

Integer

4

ConflictResolutionMethod

Refers to the type of Conflict Resolution Method.

Valid values are:

Minimum = 1,

Maximum = 2,

SumTotal = 3,

Average = 4

Integer

4

MembershipSlabDeescalationMethod

Refers to the type of Membership Slab Deescalation Method.

Valid values are:

Manual = 1,

Automatic = 2,

AutomaticAfterXDays = 3

Integer

4

RedemptionMethod

Refers to the type of Redemption Method.

Valid values are:

CurrentSlab = 1,

FallBack = 2

Integer

4

RefundMethod

Refers to the type of Refund Method.

Valid values are:

AllRewards = 1,

OnProRateBasis = 2,

DoNotRefundRewards = 3

Integer

4

AgeingExpiryOverrideAllowed

Specifies loyalty configuration data.

Boolean

1

NumberOfDaysNotExceedingExpirationDate

Define the grace period in days after Points Expiry .

Integer

4

GracePeriodAfterOverride

Define the grace period in days after Points Expiry Override.

Integer

4

CardManagement

Refers to the type of Card Management.

Valid values are:

PrePrinted = 1,

AutoGenerated = 2,

UserDefined = 3

Integer

4

MinimumSlabValueCalculatedOn

Refers to the type of Minimum Slab Value Calculated On.

Valid values are:

TotalAvailablePoints = 1,

TotalAwardedPoints = 2,

TotalAvailableCurrency = 3,

TotalAwardedCurrency = 4

Integer

4

CreatedBy

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

VarWChar

50

Created

Date of creation of this record

DBTimeStamp

4

ModifiedBy

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

VarWChar

50

Modified

Date of last modification of this record

DBTimeStamp

4

IsSMSSetupEnabled

Value is True if the SMS setup is enabled.

Boolean

1

((0))

CustomerSpecialDaysBackward

Specify the value in days

Integer

4

CustomerSpecialDaysForward

Specify the value in days

Integer

4

AutomaticAfterXDays

Specify the value in days

Integer

4

EnableEmailNotification

Value is True if the Email setup is enabled.

Boolean

1

ExchangeLoyaltyDataBetweenStore

Value is True if the Loyalty Data can be exchanged between stores

Boolean

1

EmailSMSServiceInterval

Value is the interval for the Email and Sms service

Integer

4

((0))

IsNegativeBalanceInAdjustmentJournal

Value is true if the Adjustment journal can have negative balance

Boolean

1

IsPostHTMLRecipt

Value is true if post the transaction receipt on customer portal

Boolean

1

CustomerPortalAPIUrl

Stores the URL of the Customer portal

VarWChar

300

ReportKey

Reference Key of CfgReport. See also CfgReport

VarWChar

50

IsStandAloneLoyalty

Value is true if iVend retail is running in Loyalty stand alone mode

Boolean

1

((0))

AwardPointsOnLoyaltyTender

Value is true if award loyalty points/currency on loyalty type tender

Boolean

1

((0))

AwardBonusPointsOnReferralEnrollment

Award bonus points to referee when referred member get enrolled

Integer

4

AwardBonusCurrencyOnReferralEnrollment

Award bonus currency to referee when referred member get enrolled

Numeric

9 (18,4)

AwardBonusPointsOnReferralFirstTransaction

Award bonus points to referee when referred member do the first transaction

Integer

4

AwardBonusCurrencyOnReferralFirstTransaction

Award bonus currency to referee when referred member do the first transaction

Numeric

9 (18,4)

Id

Value is the id of the Loyalty config.

VarWChar

40

Description

Value is the description of the Loyalty config.

VarWChar

200

IsDeleted

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

Boolean

1

((0))

CompanyName

Store the company name for the stand alone loyalty program

VarWChar

100

iVendAPIURL

Stores the Url for the API hosted

VarChar

600

DoNotAwardPointWithRedeemption

Value is True if the do not award points/currency with redeemption

Boolean

1

((0))

IncludeTaxInAawrdPoint

Value is True if award the points/currecny including tax amount

Boolean

1

((0))

IsDefault

Flag to mark the default Loyalty Program.

Boolean

1

((0))

PointsAllocationMechanism

Point allocation machanism. Possible Value: Floor and Ceiling.

Integer

4

((1))

AwardPointsOnAccountTender

Boolean

1

((1))

Index

Description

Primary

Unique

PK_CfgLoyaltyConfig

Database Object

Object Type

Description

Dep Level

GetMobileReplicationData

Stored Procedure

1

Loyalty_ActiveBillValue

View

1

Loyalty_ActiveTransactionCount

View

1

Loyalty_GenderInfo

View

1

LoyUpdatePointSummary

Stored Procedure

1

RecalculateSlabForLoyaltyCustomer

Stored Procedure

1

Rpt_ActiveLoyalty

User Defined Function

1

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[CfgLoyaltyConfig](

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

[RegistrationMethod] [int] NOT NULL,

[AwardBonusPointsOnRegistration] [int] NULL,

[AwardBonusCurrencyUnitsOnRegistration] [numeric](18, 2) NULL,

[PlanDefinitionBasis] [int] NOT NULL,

[SpecialDaysForCustomerSetting] [int] NOT NULL,

[ConflictResolutionOption] [int] NOT NULL,

[ConflictResolutionMethod] [int] NOT NULL,

[MembershipSlabDeescalationMethod] [int] NOT NULL,

[RedemptionMethod] [int] NOT NULL,

[RefundMethod] [int] NOT NULL,

[AgeingExpiryOverrideAllowed] [bit] NOT NULL,

[NumberOfDaysNotExceedingExpirationDate] [int] NULL,

[GracePeriodAfterOverride] [int] NULL,

[CardManagement] [int] NOT NULL,

[MinimumSlabValueCalculatedOn] [int] NOT NULL,

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

[Created] [datetime] NOT NULL,

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

[Modified] [datetime] NOT NULL,

[IsSMSSetupEnabled] [bit] NOT NULL,

[CustomerSpecialDaysBackward] [int] NULL,

[CustomerSpecialDaysForward] [int] NULL,

[AutomaticAfterXDays] [int] NULL,

[EnableEmailNotification] [bit] NULL,

[ExchangeLoyaltyDataBetweenStore] [bit] NULL,

[EmailSMSServiceInterval] [int] NULL,

[IsNegativeBalanceInAdjustmentJournal] [bit] NULL,

[IsPostHTMLRecipt] [bit] NULL,

[CustomerPortalAPIUrl] [nvarchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

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

[IsStandAloneLoyalty] [bit] NULL,

[AwardPointsOnLoyaltyTender] [bit] NULL,

[AwardBonusPointsOnReferralEnrollment] [int] NULL,

[AwardBonusCurrencyOnReferralEnrollment] [decimal](18, 4) NULL,

[AwardBonusPointsOnReferralFirstTransaction] [int] NULL,

[AwardBonusCurrencyOnReferralFirstTransaction] [decimal](18, 4) NULL,

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

[Description] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[IsDeleted] [bit] NULL,

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

[iVendAPIURL] [varchar](600) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DoNotAwardPointWithRedeemption] [bit] NULL,

[IncludeTaxInAawrdPoint] [bit] NULL,

[IsDefault] [bit] NOT NULL,

[PointsAllocationMechanism] [int] NOT NULL,

[AwardPointsOnAccountTender] [bit] NULL,

CONSTRAINT [PK_CfgLoyaltyConfig] PRIMARY KEY CLUSTERED

(

[LoyaltyConfigKey] 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].[CfgLoyaltyConfig] ADD CONSTRAINT [DF_CfgLoyaltyConfig_IsSMSSetupEnabled] DEFAULT ((0)) FOR [IsSMSSetupEnabled]

ALTER TABLE [dbo].[CfgLoyaltyConfig] ADD CONSTRAINT [DF__CfgLoyalt__Email__3D1EF407] DEFAULT ((0)) FOR [EmailSMSServiceInterval]

ALTER TABLE [dbo].[CfgLoyaltyConfig] ADD CONSTRAINT [DF_CfgLoyaltyConfig_IsStandAloneLoyalty] DEFAULT ((0)) FOR [IsStandAloneLoyalty]

ALTER TABLE [dbo].[CfgLoyaltyConfig] ADD CONSTRAINT [DF__CfgLoyalt__Award__3F3C46A3] DEFAULT ((0)) FOR [AwardPointsOnLoyaltyTender]

ALTER TABLE [dbo].[CfgLoyaltyConfig] ADD CONSTRAINT [DF__CfgLoyalt__IsDel__625078B6] DEFAULT ((0)) FOR [IsDeleted]

ALTER TABLE [dbo].[CfgLoyaltyConfig] ADD CONSTRAINT [DF__CfgLoyalt__DoNot__1B3EDD4F] DEFAULT ((0)) FOR [DoNotAwardPointWithRedeemption]

ALTER TABLE [dbo].[CfgLoyaltyConfig] ADD CONSTRAINT [DF__CfgLoyalt__Inclu__1D2725C1] DEFAULT ((0)) FOR [IncludeTaxInAawrdPoint]

ALTER TABLE [dbo].[CfgLoyaltyConfig] ADD CONSTRAINT [DF__CfgLoyalt__IsDef__108C44B2] DEFAULT ((0)) FOR [IsDefault]

ALTER TABLE [dbo].[CfgLoyaltyConfig] ADD CONSTRAINT [DF__CfgLoyalt__Point__3D5EEB29] DEFAULT ((1)) FOR [PointsAllocationMechanism]

ALTER TABLE [dbo].[CfgLoyaltyConfig] ADD CONSTRAINT [DF_CfgLoyaltyConfig_AwardPointsOnAccountTender] DEFAULT ((1)) FOR [AwardPointsOnAccountTender]