iVend Database Database : GenerateLabelData Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pMachineName

In

Machine name on which the labels are being generated

VarWChar

100

@pDebug

In

Whether this procedure has to be executed in Debug mode or not

Boolean

1

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CustomeLabelReport

Stored Procedure

1

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

GenerateLabelData_Custom

Stored Procedure

1

GetCompanyDateTime

User Defined Function

1

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

1

InvProduct

Table

Stores the product related details.

1

PrnLabelPrintField

Table

Store the fields to be used in label printing

1

PrnLabelPrinting

Table

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

--  exec GenerateLabelData   10000000000001,'CXS202','11/21/2013 12:00:00 AM'

CREATE PROCEDURE [dbo].[GenerateLabelData]

(

--@pStoreKey   nvarchar(50),

@pMachineName NVarChar(100),

--@pEffectiveDate  DateTime,

@pDebug   BIT = FALSE

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @lErrorDesc   VARCHAR(255),

  @lCursorStatus CHAR(1),

  @lProductKey nvarchar(50),

  @lQuantity   DECIMAL(20, 0),

  @lPrice   DECIMAL(20, 5),

  @lRetailPrice   DECIMAL(20, 5),

  @lCounter   DECIMAL(20, 0),

  @lProductId   nVarchar(20),

  @lProductDesc nVarchar(100),

  @lUPCCode   nVarchar(100),

  @lUPC   nVarchar(100),

  @lWeight DECIMAL(20, 2),

  @UOMId nVarchar(100),

  @lSerialBatchNumber nvarchar(50),

  @lStoreKey nvarchar(50) ,

  @lEffectiveDate DATETIME  ,

  @lGoodsReceiptKey nvarchar(50)

/*

 Logic

 1) Create a Temp Table (ProductId, Description, Price, UPC Code)

 2) Get all unique prodcut key from PrnLabelPrinting for the machineName

 3) For each product get the Id, Description and UPC Code from InvProduct

 4) For each product get the Discounted Price from the Pricing Procedure.

 5) Depending on the quantity to print, Insert records in the temp table with the information from above

 6) Once done, dump the records

*/

Create Table #tmp_LabelPrinting

(

 ProductKey nvarchar(50),

 ProductId   NVarChar(20),

 ProductDescription NVarChar(100),

 UPCCode   NVarChar(100),

 Price   Decimal(20, 5),

 RetailPrice   Decimal(20, 5),

 Weight   Decimal(20, 2),

 UOMId NVarChar(100),

 SerialBatchNumber NVARCHAR(50),

 GoodsReceiptKey   nvarchar(50)

)

SELECT @lCursorStatus = 'N'

DECLARE ProductCursor CURSOR FORWARD_ONLY FOR

SELECT ProductKey, Sum(QuantityToPrint), UPC, Price, RetailPrice, Weight,UOMId, SerialBatchNumber, GoodsReceiptKey

FROM PrnLabelPrinting

WHERE MachineName = @pMachineName

GROUP BY ProductKey, UPC, LabelPrintingKey, Price, RetailPrice, Weight, UOMId,SerialBatchNumber, GoodsReceiptKey

Order By LabelPrintingKey

IF(@@ERROR <>0)

BEGIN

  SET @lErrorDesc = 'Error while creating the cursor'

  GOTO ERRORHANDLER

END

Select @lCursorStatus = 'D'

select top 1 @lStoreKey = StoreKey from PrnLabelPrinting Where MachineName = @pMachineName

Select @lEffectiveDate = BusinessDate

From RtlStore

Where StoreKey = @lStoreKey

UPDATE T0

SET LastLabelPrintingDate = dbo.GetCompanyDateTime()

FROM InvInventoryItem T0, PrnLabelPrinting  T1

WHERE T0.ProductKey = T1.ProductKey

AND T1.MachineName = @pMachineName

AND T0.WarehouseKey = (SELECT WarehouseKey From RtlStore Where StoreKey = @lStoreKey)

IF @pDebug = 'TRUE'

BEGIN

SELECT ProductKey, Sum(QuantityToPrint), UPC, Price, RetailPrice, Weight,UOMId, SerialBatchNumber

FROM PrnLabelPrinting

WHERE MachineName = @pMachineName

GROUP BY ProductKey, UPC, Price, RetailPrice, Weight,UOMId, SerialBatchNumber

END

OPEN ProductCursor

IF(@@ERROR <>0)

BEGIN

  SET @lErrorDesc = 'Error while opening the cursor'

  GOTO ERRORHANDLER

END

Select @lCursorStatus = 'O'

FETCH NEXT FROM ProductCursor INTO @lProductKey, @lQuantity, @lUPC, @lPrice, @lRetailPrice, @lWeight, @UOMId , @lSerialBatchNumber, @lGoodsReceiptKey

WHILE @@FETCH_STATUS=0

BEGIN

Select @lProductId = Id, @lProductDesc = Description, @lUPCCode = @lUPC

From InvProduct

Where ProductKey = @lProductKey

Select @lCounter = 1

if(@lPrice = 0)

BEGIN

DECLARE @pDiscountedPrice NUMERIC(20,5),

@pDiscountType INT,

@pDiscount NUMERIC(20,5)

Exec uspGetItemPrice @pProductKey = @lProductKey, @pCustomerKey = '0', @pQuantity = 1.0,

      @pStoreKey = @lStoreKey, @pDebug = @pDebug,

      @pEffectiveDate = @lEffectiveDate, @pUOMGroupDetailKey = '0',

      @pDiscount= @pDiscount OUT, @pDiscountType = @pDiscountType OUT

 , @pDiscountedPrice = @pDiscountedPrice OUT, @pItemPrice = @lPrice out

END

WHILE (@lCounter <= @lQuantity)

BEGIN

  Insert Into #tmp_LabelPrinting (ProductKey,ProductId,ProductDescription, UPCCode, Price, RetailPrice, Weight,UOMId, SerialBatchNumber, GoodsReceiptKey)

  Values (@lProductKey,@lProductId, @lProductDesc, case when @lUPCCode = '' then '0' else @lUPCCode end , @lPrice, @lRetailPrice, @lWeight,@UOMId, @lSerialBatchNumber, @lGoodsReceiptKey)

  IF(@@ERROR <>0)

  BEGIN

  SET @lErrorDesc = 'Error while inserting into LabelPrinting'

  GOTO ERRORHANDLER

  END

  Select @lCounter = @lCounter + 1

  END

FETCH NEXT FROM ProductCursor INTO @lProductKey, @lQuantity, @lUPC, @lPrice, @lRetailPrice, @LWeight,@UOMId, @lSerialBatchNumber, @lGoodsReceiptKey

END

CLOSE ProductCursor

DEALLOCATE ProductCursor

SELECT @lCursorStatus = 'N'

-- Dump  the resultset

--Select * From #tmp_LabelPrinting

Declare @query1 nvarchar(Max)

Declare @query2 nvarchar(Max)

SELECT @query1 = STUFF

(

(

  SELECT ',' + Field + ' as '+ ''''+ Caption +''''

  FROM PrnLabelPrintField

  ORDER BY Caption FOR XML PATH('')

   )

, 1, 1, '')

---give the tempLabelPrinting to custom procedure and work on its return

exec GenerateLabelData_Custom

if(@query1 is not null)

Begin

    set @query2 = 'SELECT '+ @query1 + ' from #tmp_LabelPrinting tempTable  inner join InvProduct Product

    on tempTable.ProductKey = Product.ProductKey Inner Join InvProductGroup ProductGroup

 on Product.ProductGroupKey = ProductGroup.ProductGroupKey

 LEFT OUTER JOIN PurVendor Vendor on Product.PreferedVendorKey = Vendor.VendorKey

 LEFT OUTER JOIN InvManufacturer Manufacturer on Product.ManufacturerKey = Manufacturer.ManufacturerKey

 LEFT OUTER JOIN InvProductClass ProductClass on product.ProductClassKey = ProductClass.ProductClassKey

 LEFT OUTER JOIN TaxTaxCode TaxCode on Product.SalesTaxCodeKey = TaxCode.TaxCodeKey

 LEFT OUTER JOIN CfgEnterprise Enterprise ON 1=1

 LEFT OUTER JOIN vw_AddressDetails EnterpriseAddress ON Enterprise.AddressKey = EnterpriseAddress.AddressKey'

EXECUTE(@query2)

    End

Else

Select * From #tmp_LabelPrinting

RETURN

ERRORHANDLER:

If @lCursorStatus = 'O'

BEGIN

CLOSE ProductCursor

DEALLOCATE ProductCursor

END

If @lCursorStatus = 'D'

BEGIN

DEALLOCATE ProductCursor

END

RAISERROR('%s',16,-1,@lErrorDesc)

RETURN

END