iVend Database Database : GetFulfilmentTransactions Stored Procedure

Creation Date

8/20/2019 8:48 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@FulfilmentPlanId

In

VarWChar

50

@NoPromisedate

In

Boolean

1

@PaidandReadyForPicking

In

Boolean

1

@ExcludeAlreadyPicked

In

Boolean

1

@SiteId

In

Integer

4

@CustomerId

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

FulFulfillmentPlan

Table

Defines the plan through which the sold quantities can be fulfilled later to the customer.

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

Create PROCEDURE [dbo].[GetFulfilmentTransactions]

(

@FulfilmentPlanId NVARCHAR(50),

@NoPromisedate BIT,

@PaidandReadyForPicking BIT,

@ExcludeAlreadyPicked BIT,

@SiteId INT,

@CustomerId NVARCHAR(50)

)

AS

BEGIN

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

  DECLARE @WarehouseKey NVARCHAR(50)

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

DECLARE @FulfilmentPlanKey NVARCHAR(50)

  SELECT @FulfilmentPlanKey = FulfillmentPlanKey FROM FulFulfillmentPlan WHERE Id = @FulfilmentPlanId

CREATE TABLE #TempTransaction

   (

 FulfillmentKey NVARCHAR(50),

 FulfillmentDetailKey NVARCHAR(50),

 ProductId NVARCHAR(50),

 ProductDescription NVARCHAR(200),

 ProductKey NVARCHAR(50),

 Quantity DECIMAL(20,5),

 PickingQuantity DECIMAL(20,5),

 PickedQuantity DECIMAL(20,5),

 LocationId NVARCHAR(50),

 LocationKey NVARCHAR(50),

 IsSelected BIT,

 AllowFractionalQuantity BIT,

 PickingStatus NVARCHAR(50),

 PickingKey NVARCHAR(50),

 PickingDetailKey NVARCHAR(50),

 TransactionKey NVARCHAR(50),

 TransactionOrderKey NVARCHAR(50),

 TransactionSaleItemKey NVARCHAR(50),

 UOMGroupDetailKey NVARCHAR(50),

 UOMQuantity DECIMAL(20,5),

 UOMPickingQuantity DECIMAL(20,5),

 UOMPickedQuantity DECIMAL(20,5),

 UOMId NVARCHAR(50)

   )

INSERT INTO #TempTransaction SELECT FD.FulfillmentKey, FD.FulfillmentDetailKey, P.Id AS 'ProductId',P.Description AS 'ProductDescription', P.ProductKey, FD.Quantity, FD.PickingQuantity, FD.PickedQuantity,

    '' AS 'LocationId',CAST(0 as NVARCHAR(50)) AS LocationKey, CAST(0 as bit) AS 'IsSelected', P.AllowFractionalQuantity,

    PD.PickingStatus, PD.PickingKey, PD.PickingDetailKey, O.TransactionKey, O.TransactionOrderKey, 0, O.UOMGroupDetailKey, O.UOMQuantity, PD.UOMPickingQuantity, PD.UOMPickedQuantity, U.Id

FROM TrxTransactionFulfillmentDetail FD

LEFT JOIN TrxTransactionOrder O ON FD.SourceDetailKey = O.TransactionOrderKey

JOIN TrxTransactionFulfillment F ON FD.FulfillmentKey = F.FulfillmentKey

LEFT JOIN InvProduct P ON FD.ProductKey = P.ProductKey

LEFT JOIN InvUOMGroupDetail UG ON UG.UOMGroupDetailKey = O.UOMGroupDetailKey

LEFT JOIN InvUOM U ON U.UOMKey = UG.UOMKey

LEFT JOIN InvPickingDetail PD ON FD.FulfillmentDetailKey = PD.FulfillmentDetailKey AND (PD.IsDeleted = 0 OR PD.IsDeleted IS NULL)

WHERE FD.Quantity > FD.QuantityFulfilled AND O.Status IS NOT NULL AND O.Status NOT IN (1,2)

AND F.FulfillmentPlanKey = CASE WHEN @FulfilmentPlanKey IS NULL OR @FulfilmentPlanKey = '' THEN F.FulfillmentPlanKey ELSE @FulfilmentPlanKey END

AND (NOT(@ExcludeAlreadyPicked = 1 AND FD.PickedQuantity > 0 AND FD.PickingQuantity = 0) OR @ExcludeAlreadyPicked = 0)

AND ((@PaidandReadyForPicking = 1 AND FD.QuantityFulfilled < FD.Quantity) OR @PaidandReadyForPicking = 0);

INSERT INTO #TempTransaction SELECT FD.FulfillmentKey, FD.FulfillmentDetailKey, P.Id AS 'ProductId',P.Description AS 'ProductDescription', P.ProductKey, FD.Quantity, FD.PickingQuantity, FD.PickedQuantity,

    '' AS 'LocationId',CAST(0 as NVARCHAR(50)) AS LocationKey, CAST(0 as bit) AS 'IsSelected', P.AllowFractionalQuantity,

    PD.PickingStatus, PD.PickingKey, PD.PickingDetailKey, TI.TransactionKey,0 ,TI.TransactionItemKey, TI.UOMGroupDetailKey, TI.UOMQuantity, PD.UOMPickingQuantity, PD.UOMPickedQuantity, U.Id

FROM TrxTransactionFulfillmentDetail FD

JOIN TrxTransactionSaleItem TI ON FD.SourceDetailKey = TI.TransactionItemKey

JOIN TrxTransactionFulfillment F ON FD.FulfillmentKey = F.FulfillmentKey

LEFT JOIN InvProduct P ON FD.ProductKey = P.ProductKey

LEFT JOIN InvUOMGroupDetail UG ON UG.UOMGroupDetailKey = TI.UOMGroupDetailKey

LEFT JOIN InvUOM U ON U.UOMKey = UG.UOMKey

LEFT JOIN InvPickingDetail PD ON FD.FulfillmentDetailKey = PD.FulfillmentDetailKey AND (PD.IsDeleted = 0 OR PD.IsDeleted IS NULL)

WHERE FD.Quantity > FD.QuantityFulfilled

AND F.InventoryAllocationMethod = 1

AND F.FulfillmentPlanKey = CASE WHEN @FulfilmentPlanKey IS NULL OR @FulfilmentPlanKey = '' THEN F.FulfillmentPlanKey ELSE @FulfilmentPlanKey END

AND (NOT(@ExcludeAlreadyPicked = 1 AND FD.PickedQuantity > 0 AND FD.PickingQuantity = 0) OR @ExcludeAlreadyPicked = 0)

AND ((@PaidandReadyForPicking = 1 AND FD.QuantityFulfilled < FD.Quantity) OR @PaidandReadyForPicking = 0);

WITH CTE AS

(

SELECT F.FulfillmentKey, T.TransactionId, T.TransactionKey, FP.Id AS 'FulfilmentPlanId', ISNULL(C.Id,'') AS 'CustomerId', ISNULL(C.FirstName,'') AS 'CustomerName', ISNULL(S.Id,'') AS 'StoreId',

ISNULL(P.Id,'') AS 'POSId', ISNULL(U.Id,'') AS 'UserId', F.PromisedDate AS 'DeliveryDate',

CASE

  WHEN T.HasOrders = 1 THEN ISNULL(TS.SpecialOrderBalanceAmount,0)

  WHEN T.HasLayaways = 1 THEN ISNULL(TS.LayawayBalanceAmount,0)

  ELSE TS.BalanceAmount

END AS 'BalanceAmount',

CASE

  WHEN T.HasOrders = 1 THEN ISNULL(TS.SpecialOrderAmount,0)

  WHEN T.HasLayaways = 1 THEN ISNULL(TS.LayawayPayableAmount,0)

  ELSE TS.SaleAmount

END AS 'TotalAmount',

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

FROM TrxTransaction T

JOIN TrxTransactionFulfillment F ON T.TransactionKey = F.TransactionKey

LEFT JOIN CusCustomer C ON T.CustomerKey = C.CustomerKey

LEFT JOIN RtlStore S ON T.StoreKey = S.StoreKey

LEFT JOIN RtlPOS P ON T.POSKey = P.POSKey

LEFT JOIN SecSecurityUser U ON T.UserKey = U.UserKey

LEFT JOIN TrxTransactionStatus TS ON T.TransactionKey = TS.TransactionKey

LEFT JOIN FulFulfillmentPlan FP ON F.FulfillmentPlanKey = FP.FulfillmentPlanKey

WHERE --S.WarehouseKey = @WarehouseKey

T.IsVoided = 0 AND T.IsDeleted = 0

AND FP.InventoryAllocationMethod = 1

AND FP.FulfillmentPlanKey = CASE WHEN @FulfilmentPlanKey IS NULL OR @FulfilmentPlanKey = '' THEN FP.FulfillmentPlanKey ELSE @FulfilmentPlanKey END

AND F.PromisedDate = CASE @NoPromisedate WHEN 1 THEN NULL ELSE F.PromisedDate END

AND F.FulfillmentKey IN (SELECT FulfillmentKey FROM #TempTransaction)

AND T.HasFulFillments = 1

AND C.Id = CASE WHEN @CustomerId IS NULL OR @CustomerId = '' THEN C.Id ELSE @CustomerId END

)

SELECT * FROM CTE

WHERE((@PaidandReadyForPicking = 1 AND BalanceAmount <= 0) OR @PaidandReadyForPicking = 0)

--WHERE BalanceAmount = CASE @PaidandReadyForPicking WHEN 1 THEN 0 ELSE BalanceAmount END;

SELECT * FROM #TempTransaction

DROP TABLE #TempTransaction

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

RETURN

END