iVend Database Database : GetMobilePOSDeliveryTransactions Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@WithPackage

In

Boolean

1

@TransactionKeys

In

Xml

1073741823

@SiteId

In

Integer

4

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

RtlStore

Table

Contains the details of Stores defined in the system.

1

CREATE PROCEDURE [dbo].[GetMobilePOSDeliveryTransactions]

(

@WithPackage bit,

@TransactionKeys XML,

@SiteId Int

)

AS

BEGIN

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

  --DECLARE @SiteId INT

  DECLARE @WarehouseKey NVARCHAR(50)

  --SELECT @SiteId = SiteId FROM CfgSiteInformation

  SELECT @WarehouseKey = WarehouseKey FROM RtlStore WHERE SiteId = @SiteId

  CREATE TABLE #TempTransaction

   (

 TransactionKey NVARCHAR(50),

 TransactionOrderKey NVARCHAR(50),

 PackegedQuantity DECIMAL,

 ProductKey NVARCHAR(50)

   )

  /*Temp table for only Transaction Keys*/

CREATE TABLE #TempTrans

(

 TransactionKey NVARCHAR(50),

 AmountCollected DECIMAL(18,5)

)

  IF(@WithPackage = 1)

  BEGIN

DECLARE @hDoc int

--Prepare input values as an XML documnet

EXEC sp_xml_preparedocument @hDoc OUTPUT, @TransactionKeys

INSERT INTO #TempTrans(TransactionKey,AmountCollected)

  SELECT TransactionKey, 0 FROM OPENXML (@hdoc, '/DocumentElement/TransactionKeys', 2)

  WITH (TransactionKey NVARCHAR(50))

EXEC sp_xml_removedocument @hDoc

INSERT INTO #TempTransaction(TransactionKey,TransactionOrderKey, PackegedQuantity, ProductKey)

SELECT Distinct A.TransactionKey,A.TransactionOrderKey,0,A.ProductKey

                      from TrxTransactionOrder A

                      inner join TrxTransaction B ON A.TransactionKey = B.TransactionKey

                      inner join #TempTrans I On I.TransactionKey = B.TransactionKey

                      Left outer join TrxDeliveryPackage H ON B.TransactionKey = H.TransactionKey

                      inner join InvProduct C On A.ProductKey = C.ProductKey

                      Where B.HasFulFillments = 1 AND B.HasDeliveries = 0 AND B.IsVoided = 0 AND B.HasOrders = 1 AND A.Status = 0

                      AND B.HasCancelledSalesOrder = 0 AND A.Type = 0 AND A.OpenQuantity > 0 AND A.DeliveryWarehouseKey = @WarehouseKey

                      AND

                       ((H.TransactionKey IS NULL AND A.TransactionKey IS NOT NULL)

                      OR

                       (A.OpenQuantity > (Select (ISNULL(SUM(X.Quantity),0)*A.UOMBaseQuantity) from TrxDeliveryPackageDetail X

          inner join TrxDeliveryPackage Y ON Y.DeliveryPackageKey = X.DeliveryPackageKey AND Y.Status = 0

          where Y.TransactionKey = A.TransactionKey AND X.ProductKey = A.ProductKey AND X.SourceKey = A.TransactionOrderKey) AND H.TransactionKey IS NOT NULL))

  END

  ELSE

  BEGIN

INSERT INTO #TempTransaction(TransactionKey,TransactionOrderKey,PackegedQuantity,ProductKey) SELECT Distinct A.TransactionKey,A.TransactionOrderKey,0,A.ProductKey

                      from TrxTransactionOrder A

                      inner join TrxTransaction B ON A.TransactionKey = B.TransactionKey

                      Left outer join TrxDeliveryPackage H ON B.TransactionKey = H.TransactionKey

                      inner join InvProduct C On A.ProductKey = C.ProductKey

                      Where B.HasFulFillments = 1 AND B.HasDeliveries = 0 AND B.IsVoided = 0 AND B.HasOrders = 1 AND A.Status = 0

                      AND B.HasCancelledSalesOrder = 0 AND A.Type = 0 AND A.OpenQuantity > 0 AND A.DeliveryWarehouseKey = @WarehouseKey

                      AND

                       ((H.TransactionKey IS NULL AND A.TransactionKey IS NOT NULL)

                      OR

                       (A.OpenQuantity > (Select (ISNULL(SUM(X.Quantity),0)*A.UOMBaseQuantity) from TrxDeliveryPackageDetail X

          inner join TrxDeliveryPackage Y ON Y.DeliveryPackageKey = X.DeliveryPackageKey AND Y.Status = 0

          where Y.TransactionKey = A.TransactionKey AND X.ProductKey = A.ProductKey AND X.SourceKey = A.TransactionOrderKey) AND H.TransactionKey IS NOT NULL))

/*Get distinct Transaction Keys.*/

INSERT INTO #TempTrans(TransactionKey) Select Distinct TransactionKey FROM #TempTransaction

  END

/*Common for Both selected Transaction and All Transaction*/

UPDATE A

  SET A.PackegedQuantity = (Select ISNULL(SUM(X.Quantity),0) from TrxDeliveryPackageDetail X

          inner join TrxDeliveryPackage Y ON Y.DeliveryPackageKey = X.DeliveryPackageKey AND Y.TransactionKey = A.TransactionKey AND Y.Status = 0

          where B.TransactionKey = A.TransactionKey AND X.ProductKey = B.ProductKey AND X.SourceKey = B.TransactionOrderKey)

  FROM #TempTransaction AS A

  inner join TrxTransactionOrder B On A.TransactionKey = B.TransactionKey AND A.TransactionOrderKey = B.TransactionOrderKey

/*Update Amount Already collected*/

UPDATE A

  SET A.AmountCollected = (Select ISNULL(SUM(X.PackageAmount),0) from TrxDeliveryPackage X

          where B.TransactionKey = X.TransactionKey AND X.Status = 0)

  FROM #TempTrans AS A

  inner join TrxTransactionStatus B On A.TransactionKey = B.TransactionKey

Select A.TransactionKey,A.TransactionId,ISNULL(B.Id,'') AS 'CustomerId', ISNULL(B.FirstName,'') AS 'CustomerName',ISNULL(C.Id,'') AS 'StoreId',ISNULL(D.Id,'') AS 'POSId', A.CustomerKey,

                      ISNULL(E.Id,'') AS 'UserId',(Select Top 1 PromisedDate from TrxTransactionFulfillment Where TransactionKey = A.TransactionKey) AS 'DeliveryDate',

                      CASE ISNULL(I.SpecialOrderBalanceAmount,0) WHEN 0 THEN 0 ELSE (I.SpecialOrderBalanceAmount-ISNULL(H.AmountCollected,0)) END AS 'BalanceAmount',

                      CASE ISNULL(I.SpecialOrderBalanceAmount,0) WHEN 0 THEN 0 ELSE (I.SpecialOrderBalanceAmount-ISNULL(H.AmountCollected,0)) END AS 'AmountToCollect',CAST(0 as bit) AS 'IsSelected',

                      '' AS 'LocationId',CAST(0 as NVARCHAR(50)) AS LocationKey

                      From TrxTransaction A

                      Inner join #TempTrans H ON A.TransactionKey = H.TransactionKey

                      left outer join TrxTransactionStatus I ON I.TransactionKey = A.TransactionKey

                      left outer Join CusCustomer B ON A.CustomerKey = B.CustomerKey

                      left outer join RtlStore C On A.StoreKey = C.StoreKey

                      left outer join RtlPOS D ON D.POSKey = A.POSKey

                      left outer join LbrUser E ON E.UserKey = A.UserKey

                      Where A.HasFulFillments = 1 AND A.HasDeliveries = 0 AND A.IsVoided = 0 AND A.HasOrders = 1

                      AND A.HasCancelledSalesOrder = 0

                      Select A.TransactionKey,A.TransactionOrderKey,C.Id AS 'ProductId',A.Quantity/A.UOMBaseQuantity AS 'Quantity',A.Price ,((A.OpenQuantity/A.UOMBaseQuantity)-ISNULL(H.PackegedQuantity,0)) AS 'OpenQuantity',

                       A.RefundedQuantity,(A.FullfilledQuantity/A.UOMBaseQuantity) AS 'FullfilledQuantity', A.WarehouseKey,A.ProductKey, ((A.OpenQuantity/A.UOMBaseQuantity)-ISNULL(H.PackegedQuantity,0)) AS 'PackageQuantity' ,CAST(0 as bit) AS 'IsSelected',

                      ISNULL(E.Id,'') AS 'UOMId', '' AS 'LocationId',CAST(0 as NVARCHAR(50)) AS LocationKey, C.AllowFractionalQuantity

                      from TrxTransactionOrder A

                      inner join TrxTransaction B ON A.TransactionKey = B.TransactionKey

                      Inner join #TempTransaction H ON A.TransactionKey = H.TransactionKey AND A.TransactionOrderKey = H.TransactionOrderKey AND A.ProductKey = H.ProductKey

                      inner join InvProduct C On A.ProductKey = C.ProductKey

                      LEFT OUTER JOIN InvUOMGroupDetail D ON D.UOMGroupDetailKey = A.UOMGroupDetailKey

                      LEFT OUTER JOIN InvUOM E ON E.UOMKey = D.UOMKey

                      Where B.HasFulFillments = 1 AND B.HasDeliveries = 0 AND B.IsVoided = 0 AND B.HasOrders = 1

                      AND B.HasCancelledSalesOrder = 0 AND A.Type = 0 AND A.DeliveryWarehouseKey = @WarehouseKey

                      AND A.Status = 0

                      /*Package Details*/

                      Select A.*,C.Id as 'LocationId' from TrxDeliveryPackage A

    inner join #TempTrans B ON A.TransactionKey = B.TransactionKey

    left outer join InvLocation C ON C.LocationKey = A.LocationKey

    Where A.status IN(0,2)

    Select A.*,B.Id AS 'ProductId',E.Id AS 'LocationId',B.AllowFractionalQuantity from TrxDeliveryPackageDetail A

    inner join InvProduct B On A.ProductKey = B.ProductKey

    inner join TrxDeliveryPackage C On C.DeliveryPackageKey = A.DeliveryPackageKey

    inner join #TempTrans D ON C.TransactionKey = D.TransactionKey

    left outer join InvLocation E ON E.LocationKey = A.LocationKey

    Where C.status IN(0,2)

    Select A.DeliveryPackageDetailKey, E.SerialNumber AS 'SerialBatch', 1 AS 'Quantity' from TrxDeliveryPackageSerial A inner join TrxDeliveryPackageDetail B On A.DeliveryPackageDetailKey = B.DeliveryPackageDetailKey

    inner join TrxDeliveryPackage C On C.DeliveryPackageKey = B.DeliveryPackageKey

    inner join #TempTrans D ON C.TransactionKey = D.TransactionKey

    inner join InvSerial E ON E.SerialKey = A.SerialKey Where C.status IN(0,2)

    Union All

    Select A.DeliveryPackageDetailKey, E.BatchNumber AS 'SerialBatch',A.Quantity from TrxDeliveryPackageBatch A inner join TrxDeliveryPackageDetail B On A.DeliveryPackageDetailKey = B.DeliveryPackageDetailKey

    inner join TrxDeliveryPackage C On C.DeliveryPackageKey = B.DeliveryPackageKey

    inner join #TempTrans D ON C.TransactionKey = D.TransactionKey

    inner join InvBatch E ON E.BatchKey = A.BatchKey Where C.status IN(0,2)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

RETURN

END