iVend Database Database : GetValidPromotionsForProduct Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@ProductKey

In

VarWChar

50

@StoreKey

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

2

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

2

GetCompanyDateTime

User Defined Function

1

InvProduct

Table

Stores the product related details.

1

ProPromotionApplicabilityDetails

Table

Stores the details for a promotion applicability

1

ProPromotionApplicabilityMaster

Table

Stores the values for a promotion applicablity on special days or time

1

ProPromotionBonusBuysMaster

Table

Stores the master data for the Promotion defined

1

ProPromotionBuySide

Table

Stores the Buy side data for a promotion

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

-- =============================================

-- Author:  Abinesh Agarwal

-- Create date: 4th-Aug-2014

-- Description: This procedure finds the promotions that are valid for the scanned product at the POS

-- =============================================

CREATE PROCEDURE [dbo].[GetValidPromotionsForProduct]

@ProductKey nvarchar(50),

@StoreKey nvarchar(50)

AS

BEGIN

SET NOCOUNT ON;

  DECLARE @PricingStoreGroupKey nvarchar(50),

  @ProductGroupKey nvarchar(50),

  @SystemDateTime As DateTime

Select @SystemDateTime = dbo.GetCompanyDateTime()

SELECT @PricingStoreGroupKey = PricingStoreGroupKey

From RtlStore

Where StoreKey = @StoreKey

SELECT @ProductGroupKey =ProductGroupKey From InvProduct Where ProductKey = @ProductKey

  --CHECK IF THE PROMOTION IS VALID FOR THE STORE AND FOR THE SPECIFIED DATETIME

SELECT DISTINCT PromotionKey INTO #TempProPromotionBonusBuysMaster

FROM ProPromotionBonusBuysMaster A

LEFT OUTER JOIN ProPromotionApplicabilityMaster B

ON A.PromotionKey = B.SourceKey

AND B.SourceType = 0

LEFT OUTER JOIN ProPromotionApplicabilityDetails C

ON B.PromotionApplicabilityKey = C.PromotionApplicabilityKey

WHERE CONVERT(NVARCHAR, @SystemDateTime,112) BETWEEN ISNULL(CONVERT(NVARCHAR, A.StartDate,112) ,CONVERT(NVARCHAR, @SystemDateTime,112)) AND ISNULL(CONVERT(NVARCHAR, A.EndDate, 112) ,CONVERT(NVARCHAR, @SystemDateTime,112))

AND IsDeleted = 0

AND Active = 1

AND A.DiscountPercent =0

AND

(

 (C.SourceType = 0 AND C.SourceKey = @StoreKey)

OR

 (C.SourceType = 1 AND C.SourceKey = @PricingStoreGroupKey)

)

--DELETE FROM LIST OF THE PRODUCT IS PART OF EXCLUSION LIST

DELETE FROM #TempProPromotionBonusBuysMaster Where  PromotionKey in

(

SELECT PromotionKey

FROM ProPromotionExcludedItem

WHERE SourceType= 0

AND SourceKey = @ProductKey

)

--DELETE FROM LIST OF THE PRODUCT IS PART OF EXCLUSION LIST

DELETE FROM #TempProPromotionBonusBuysMaster

WHERE  PromotionKey in

(

SELECT PromotionKey

FROM ProPromotionExcludedItem A , InvProduct B

Where B.ProductKey = @ProductKey

AND  A.SourceType = 1

AND  A.SourceKey = B.ProductGroupKey

)

--GET THE LIST OF PROMOTIONS WHICH ARE APPLICABLE ON ALL THE ITEMS

SELECT PromotionKey INTO #TempApplicable

FROM ProPromotionBuySide

WHERE SOURCETYPE = 3

AND (Quantity + Amount > 0)

--GET THE LIST OF PROMOTIONS WHICH ARE FOR THIS SCANNED ITEM

INSERT INTO #TempApplicable

SELECT PromotionKey

FROM ProPromotionBuySide

WHERE SOURCETYPE = 0

AND SourceKey = @ProductKey

--GET THE LIST OF PROMOTIONS WHICH ARE FOR THIS SCANNED ITEM GROUP

INSERT INTO #TempApplicable

SELECT PromotionKey

FROM ProPromotionBuySide

WHERE SOURCETYPE = 1

AND SourceKey = @ProductGroupKey

--GET THE LIST OF PROMOTIONS WHICH HAS THE SCANNED ITEM CATEGORY IN THE BUY SIDE

INSERT INTO #TempApplicable

SELECT PromotionKey

FROM ProPromotionBuySide

WHERE SOURCETYPE = 2

AND SourceKey IN (SELECT ProductCategoryKey FROM InvProductCategoryProduct wHERE ProductKey = @ProductKey)

--GET THE LIST OF PROMOTIONS WHICH HAS THE SCANNED ITEM IN THE GET SIDE

INSERT INTO #TempApplicable

SELECT PromotionKey

FROM ProPromotionGetSide

WHERE SOURCETYPE = 0

AND SourceKey = @ProductKey

--GET THE LIST OF PROMOTIONS WHICH HAS THE SCANNED ITEM GROUP IN THE GET SIDE

INSERT INTO #TempApplicable

SELECT PromotionKey

FROM ProPromotionGetSide

WHERE SOURCETYPE = 1

AND SourceKey = @ProductGroupKey

--GET THE LIST OF PROMOTIONS WHICH HAS THE SCANNED ITEM CATEGORY IN THE GET SIDE

INSERT INTO #TempApplicable

SELECT PromotionKey

FROM ProPromotionGetSide

WHERE SOURCETYPE = 2

AND SourceKey IN (SELECT ProductCategoryKey FROM InvProductCategoryProduct wHERE ProductKey = @ProductKey)

SELECT * FROM #TempProPromotionBonusBuysMaster WHERE PROMOTIONKEY IN (SELECT PromotionKey FROM #TempApplicable)

END