iVend Database Database : LoyPoints 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

PointsKey

Primary Key

VarWChar

50

StoreKey

Reference Key of Store. See also Store.

VarWChar

50

CustomerKey

Reference Key of Loyalty Card Information. See also Loyalty Card Information.

VarWChar

50

TransactionKey

Reference Key of Transaction. See also Transaction.

VarWChar

50

PlanDefinitionBasis

Refers to the type of Plan Definition Basis.

Valid values are:

EveryTransaction = 1,

CumulativeBasis = 2

Integer

4

TransactionDate

Hold the transaction date

DBTimeStamp

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

AwardedPoints

Hold awarded points for particular transaction

Integer

4

((0))

AwardedCurrency

Hold awarded currency for particular transaction

Numeric

9 (18,2)

((0))

RedeemedPoints

Hold redeemed points for particular transaction

Integer

4

((0))

RedeemedCurrency

Hold redeemed currency for particular transaction

Numeric

9 (18,2)

((0))

PointsBefore

Hold available points before the particular transaction

Integer

4

((0))

CurrencyBefore

Hold available currency before the particular transaction

Numeric

9 (18,2)

((0))

IsExpired

Value is true if points are expired.

Boolean

1

((0))

OriginalExpiryDate

Hold expiry date of awarded points.

DBTimeStamp

4

LastExpiryOverrideDate

Hold new expiry date of awarded points

DBTimeStamp

4

PointAwardTypeId

Hold points award Type Like Bonus points/Adjusted points etc.

Integer

4

AdjustmentJournalDetailsKey

Reference Key of Adjustment Journal Details. See also Adjustment Journal Details.

VarWChar

50

OverriddenBy

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

VarWChar

50

Modified

Date of last modification of this record

DBTimeStamp

4

(getdate())

ExpiryDate

Hold expiry date of awarded points.

DBTimeStamp

4

Created

Date of creation of this record

DBTimeStamp

4

(getdate())

MembershipGroupKey

Define the loyalty membership group key if loyalty customers is in loyalty membership group.

VarWChar

50

IsIntegratedWithPortal

Value is True if records is integrated on customer portal.

Boolean

1

LoyaltyConfigKey

Reference Key of CfgLoyaltyConfig. See also CfgLoyaltyConfig

VarWChar

50

((0))

ReferralSetupKey

Reference Key of CfgReferralSetup. See also CfgReferralSetup

VarWChar

50

((0))

ReferralKey

Refers to the key of the CfgReferralSetup.

VarWChar

50

Index

Description

Primary

Unique

PK_LoyPoints

Trigger

Description

tgr_LoyUpdatePointSummary

Database Object

Object Type

Description

Dep Level

Loy_GetMonthlyPointSummary

Stored Procedure

1

LoyUpdatePointSummary

Stored Procedure

1

tgr_LoyUpdatePointSummary

Trigger

1

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[LoyPoints](

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

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

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

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

[PlanDefinitionBasis] [int] NOT NULL,

[TransactionDate] [datetime] NOT NULL,

[ConflictResolutionOption] [int] NOT NULL,

[ConflictResolutionMethod] [int] NOT NULL,

[AwardedPoints] [int] NOT NULL,

[AwardedCurrency] [numeric](18, 2) NOT NULL,

[RedeemedPoints] [int] NOT NULL,

[RedeemedCurrency] [numeric](18, 2) NOT NULL,

[PointsBefore] [int] NOT NULL,

[CurrencyBefore] [numeric](18, 2) NOT NULL,

[IsExpired] [bit] NOT NULL,

[OriginalExpiryDate] [datetime] NULL,

[LastExpiryOverrideDate] [datetime] NULL,

[PointAwardTypeId] [int] NULL,

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

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

[Modified] [datetime] NOT NULL,

[ExpiryDate] [datetime] NULL,

[Created] [datetime] NOT NULL,

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

[IsIntegratedWithPortal] [bit] NULL,

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

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

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

CONSTRAINT [PK_LoyPoints] PRIMARY KEY CLUSTERED

(

[PointsKey] 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].[LoyPoints] ADD CONSTRAINT [DF_Points_AwardedPoints] DEFAULT ((0)) FOR [AwardedPoints]

ALTER TABLE [dbo].[LoyPoints] ADD CONSTRAINT [DF_Points_AwardedCurrency] DEFAULT ((0)) FOR [AwardedCurrency]

ALTER TABLE [dbo].[LoyPoints] ADD CONSTRAINT [DF_Points_RedeemedPoints] DEFAULT ((0)) FOR [RedeemedPoints]

ALTER TABLE [dbo].[LoyPoints] ADD CONSTRAINT [DF_Points_RedeemedCurrency] DEFAULT ((0)) FOR [RedeemedCurrency]

ALTER TABLE [dbo].[LoyPoints] ADD CONSTRAINT [DF_LoyPoints_PointsBefore] DEFAULT ((0)) FOR [PointsBefore]

ALTER TABLE [dbo].[LoyPoints] ADD CONSTRAINT [DF_LoyPoints_CurrencyBefore] DEFAULT ((0)) FOR [CurrencyBefore]

ALTER TABLE [dbo].[LoyPoints] ADD CONSTRAINT [DF_Points_IsExpired_1] DEFAULT ((0)) FOR [IsExpired]

ALTER TABLE [dbo].[LoyPoints] ADD CONSTRAINT [DF_Points_UpdatedOn] DEFAULT (getdate()) FOR [Modified]

ALTER TABLE [dbo].[LoyPoints] ADD CONSTRAINT [DF_Points_CreatedOn] DEFAULT (getdate()) FOR [Created]

ALTER TABLE [dbo].[LoyPoints] ADD CONSTRAINT [DF__LoyPoints__Loyal__6621099A] DEFAULT ((0)) FOR [LoyaltyConfigKey]

ALTER TABLE [dbo].[LoyPoints] ADD CONSTRAINT [DF__LoyPoints__Refer__67152DD3] DEFAULT ((0)) FOR [ReferralSetupKey]