iVend Database Database : rpt_Delivery Stored Procedure

Creation Date

8/12/2019 6:09 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@FromDate

In

From Date (Part of Date Range in YYYYMMDD Format)

VarChar

8

@ToDate

In

To Date (Part of Date Range in YYYYMMDD Format)

VarChar

8

@ProductGroup

In

Product Group (Prefixed and Postfixed with '?' character )

VarChar

4000

@FromProduct

In

From Product (Part of Product Range )

VarChar

40

@ToProduct

In

To Product (Part of Product Range )

VarChar

40

@CustomerGroup

In

Customer Group (Prefixed and Postfixed with '?' character)

VarChar

4000

@FromCustomer

In

From Customer (Part of Customer Range )

VarChar

40

@ToCustomer

In

To Customer (Part of Customer Range )

VarChar

40

@Store

In

Prefixed and Postfixed with '?' character

VarChar

4000

@Subsidiary

In

Prefixed and Postfixed with '?' character

VarChar

4000

@POS

In

Prefixed and Postfixed with '?' character

VarChar

4000

@BookStore

In

VarWChar

200

@DelStore

In

VarWChar

200

@IncludeLayAway

In

Specify Whether to include LayAway Records in Report.

Boolean

1

@IncludeSale

In

Specify Whether to include Sale Records in Report.

Boolean

1

@IncludeOrder

In

Specify Whether to include Order Records in Report.

Boolean

1

@RETURN_VALUE

Return Value

Integer

4

--  this procedure Delivery Report is generated.

-- Case1 ( If @IncludeLayAway = 1 )

--     Then LayAway Records are included in the delivery Report.

-- Case2 (If @IncludeSale = 1)

--     Then Sales Records are included in the delivery Report.

-- Case3 (If @IncludeOrder = 1)

--      Then Order Records are included in the delivery Report.

CREATE PROCEDURE [dbo].[rpt_Delivery](

@FromDate VARCHAR(8),

@ToDate   VARCHAR(8),

@ProductGroup VARCHAR(4000),

@FromProduct VARCHAR(40),

@ToProduct VARCHAR(40),

@CustomerGroup VARCHAR(4000),

@FromCustomer VARCHAR(40),

@ToCustomer VARCHAR(40),

@Store   VARCHAR(4000),

@Subsidiary   VARCHAR(4000),

@POS   VARCHAR(4000),

@BookStore NVARCHAR(200),

@DelStore NVARCHAR(200),

@IncludeLayAway BIT,

@IncludeSale BIT,

@IncludeOrder BIT

   )

AS

BEGIN

Set NoCount On

/*

Exec [rpt_Delivery]  '', '',  --FromDate, ToDate

      '',       --ProductGroup

      '', '',      --FromProduct , ToProduct

      '',       --CustomerGroup

      '', '',      --FromCustomer, ToCustomer

      '',       --Store

      0,       --IncludeLayAway

   1,       --IncludeSale

   0       --IncludeOrder

*/

CREATE TABLE #temp

(

ProductPromisedDate NVARCHAR(50),

TransactionId   NVARCHAR(50),

Cust     NVARCHAR(40),

CustFirstName   NVARCHAR(200),

CustMiddleName   NVARCHAR(100),

CustLastName   NVARCHAR(80),

CustPhone   NVARCHAR(40),

StoreId   NVARCHAR(40),

StoreDesc   NVARCHAR(200),

SubsidiaryId   NVARCHAR(40),

SubsidiaryDesc   NVARCHAR(200),

Item     NVARCHAR(40),

ItemDescription NVARCHAR(200),

Quantity   DECIMAL(20, 5),

OpenQuantity   DECIMAL(20, 5),

FullfilledQty   DECIMAL(20, 5),

SourceType   NVARCHAR(20),

FulfillmentKey   NVARCHAR(50),

UOM     NVARCHAR(200),

POSId       NVARCHAR(40) ,

BookingStore   NVARCHAR(200),

DeliveryStore   NVARCHAR(200)

)

If @IncludeSale = 1

BEGIN

INSERT INTO #temp

SELECT CONVERT(VARCHAR(50), ProductPromisedDate, 106) AS ProductPromisedDate, TransactionId, Cust.Id AS Cust, Cust.FirstName AS CustFirstName,

Cust.MiddleName AS CustMiddleName, Cust.LastName AS CustLastName, Cust.PhoneNumber AS CustPhone, RtlStore.Id, RtlStore.Description AS StoreDescriotion,  RTLSUBSIDIARY.Id, RTLSUBSIDIARY.Description AS SubsidiaryDescription,

   Prod.Id AS Item, TrxnSale.Description AS ItemDescription, TrxnSale.Quantity/ISNULL(TrxnSale.UOMBaseQuantity,1) AS Quantity,

TrxnSale.OpenQuantity/ISNULL(TrxnSale.UOMBaseQuantity,1) AS OpenQuantity, TrxnSale.FullfilledQuantity/ISNULL(TrxnSale.UOMBaseQuantity,1) AS FullfilledQty,

   (CASE WHEN FulDet.SourceType = 0 THEN 'Sale' WHEN FulDet.SourceType = 1 THEN 'ORDER' WHEN FulDet.SourceType = 2 THEN 'Layaway' END) AS SourceType,

   FulDet.FulfillmentKey, ISNULL(UOM.Description,'') as UOM, RtlPOS.Id, BookStore.Id AS BookingStore, DelStore.Id AS DeliveryStore

From TrxTransaction AS Trxn WITH (NOLOCK)

  INNER JOIN TrxTransactionSaleItem AS TrxnSale WITH (NOLOCK) ON Trxn.TransactionKey = TrxnSale.TransactionKey

  LEFT JOIN InvUOMGroupDetail  UOMD WITH (NOLOCK) ON TrxnSale.UOMGroupDetailKey=UOMD.UOMGroupDetailKey

  LEFT JOIN InvUOM UOM WITH (NOLOCK) ON UOMD.UOMKey=UOM.UOMKey

  INNER JOIN TrxTransactionFulfillmentDetail AS FulDet WITH (NOLOCK) ON Trxn.TransactionKey = FulDet.TransactionKey And TrxnSale.TransactionItemKey = FulDet.SourceDetailKey

  INNER JOIN CusCustomer AS Cust WITH (NOLOCK) ON Trxn.CustomerKey = Cust.CustomerKey

  INNER JOIN InvProduct AS Prod WITH (NOLOCK) ON TrxnSale.ProductKey = Prod.ProductKey

  INNER JOIN InvProductGroup AS ProdGrp WITH (NOLOCK) ON ProdGrp.ProductGroupKey = Prod.ProductGroupKey

  INNER JOIN CusCustomerGroup AS CustGrp WITH (NOLOCK) ON CustGrp.CustomerGroupKey = Cust.CustomerGroupKey

  INNER JOIN RtlStore WITH (NOLOCK) ON Trxn.StoreKey = RtlStore.StoreKey

  LEFT OUTER JOIN RtlSubsidiary WITH (NOLOCK) ON RtlStore.SubsidiaryKey = RtlSubsidiary.SubsidiaryKey

  LEFT OUTER JOIN RtlPOS WITH (NOLOCK) ON Trxn.POSKey = RtlPOS.POSKey

  LEFT OUTER JOIN RtlStore DelStore WITH (NOLOCK) ON DelStore.WarehouseKey = TrxnSale.DeliveryWarehouseKey

  LEFT OUTER JOIN RtlStore bookStore WITH (NOLOCK) ON bookStore.WarehouseKey = TrxnSale.BookingWarehouseKey

Where

  CONVERT(VARCHAR, FulDet.ProductPromisedDate, 112) Between Case When @FromDate = '' Then CONVERT(VARCHAR, FulDet.ProductPromisedDate, 112) else @FromDate End And Case When @ToDate = '' then CONVERT(VARCHAR, FulDet.ProductPromisedDate, 112) else @ToDate

end --Date Filter                          --Date Filter

  And  ((Prod.Id Between Case When @FromProduct = '' Then Prod.Id Else @FromProduct End And Case When @ToProduct = '' Then Prod.Id Else @ToProduct End)

  OR

  (Prod.Id Between Case When @FromProduct = '' Then Prod.Id Else @ToProduct End And Case When @ToProduct = '' Then Prod.Id Else @FromProduct End))                       --Product Filter

  And  ((Cust.Id Between Case When @FromCustomer = '' Then Cust.Id Else @FromCustomer End And Case When @ToCustomer = '' Then Cust.Id Else @ToCustomer End)

  OR                 --Customer Filter

  (Cust.Id Between Case When @FromCustomer = '' Then Cust.Id Else @ToCustomer End And Case When @ToCustomer = '' Then Cust.Id Else @FromCustomer End))

  AND ISNULL (RtlSubsidiary.Id,'') = Case When @Subsidiary <> '' Then @Subsidiary Else ISNULL(RtlSubsidiary.Id,'') End

AND (RtlStore.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@Store)) OR RtlStore.Id IN (CASE WHEN @Store='' THEN RtlStore.Id END))

AND (ISNULL(RtlPOS.Id,'') IN (SELECT DataString FROM [dbo].[fnSplitValues](@POS)) OR ISNULL(RtlPOS.Id,'') IN (CASE WHEN @POS='' THEN ISNULL(RtlPOS.Id,'') END))

AND (ProdGrp.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@ProductGroup)) OR ProdGrp.Id IN (CASE WHEN @ProductGroup='' THEN ProdGrp.Id END))

AND (CustGrp.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@CustomerGroup)) OR CustGrp.Id IN (CASE WHEN @CustomerGroup='' THEN CustGrp.Id END))

And  FulDet.SourceType = 0 AND FulDet.IsDeleted = 0 AND Trxn.IsVoided='False' AND Trxn.IsSuspended =0

AND (DelStore.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@DelStore)) OR DelStore.Id IN (CASE WHEN @DelStore='' THEN DelStore.Id END))

AND (bookStore.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@BookStore)) OR bookStore.Id IN (CASE WHEN @BookStore='' THEN bookStore.Id END))

End

If @IncludeOrder = 1

BEGIN

INSERT INTO #temp

SELECT CONVERT(VARCHAR(50), ProductPromisedDate, 106) AS ProductPromisedDate, TransactionId,

   Cust.Id AS Cust, Cust.FirstName AS CustFirstName, Cust.MiddleName AS CustMiddleName, Cust.LastName AS CustLastName, Cust.PhoneNumber AS CustPhone,

   RtlStore.Id, RtlStore.Description AS StoreDescriotion,  RTLSUBSIDIARY.Id, RTLSUBSIDIARY.Description AS SubsidiaryDescription,

   Prod.Id AS Item,

   TrxnOrder.Description AS ItemDescription, TrxnOrder.Quantity/ISNULL(TrxnOrder.UOMBaseQuantity,1) AS Quantity, TrxnOrder.OpenQuantity/ISNULL(TrxnOrder.UOMBaseQuantity,1) AS OpenQuantity, TrxnOrder.FullfilledQuantity/ISNULL(TrxnOrder.UOMBaseQuantity,1)

AS FullfilledQty,

   (CASE WHEN FulDet.SourceType = 0 THEN 'Sale' WHEN FulDet.SourceType = 1 THEN 'ORDER' WHEN FulDet.SourceType = 2 THEN 'Layaway' END) AS SourceType,

   FulDet.FulfillmentKey, ISNULL(UOM.Description,'') as UOM, RtlPOS.Id, BookStore.Id AS BookingStore, DelStore.Id AS DeliveryStore

From TrxTransaction AS Trxn WITH (NOLOCK)

INNER JOIN TrxTransactionOrder AS TrxnOrder WITH (NOLOCK) ON Trxn.TransactionKey = TrxnOrder.TransactionKey

LEFT JOIN InvUOMGroupDetail  UOMD WITH (NOLOCK) ON TrxnOrder.UOMGroupDetailKey=UOMD.UOMGroupDetailKey

LEFT JOIN InvUOM UOM WITH (NOLOCK) ON UOMD.UOMKey=UOM.UOMKey

INNER JOIN TrxTransactionFulfillmentDetail AS FulDet WITH (NOLOCK) ON Trxn.TransactionKey = FulDet.TransactionKey And TrxnOrder.TransactionOrderKey = FulDet.SourceDetailKey And  FulDet.SourceType = 1 AND FulDet.IsDeleted = 0

INNER JOIN CusCustomer AS Cust WITH (NOLOCK) ON Trxn.CustomerKey = Cust.CustomerKey

INNER JOIN InvProduct AS Prod WITH (NOLOCK) ON TrxnOrder.ProductKey = Prod.ProductKey

INNER JOIN InvProductGroup AS ProdGrp WITH (NOLOCK) ON ProdGrp.ProductGroupKey = Prod.ProductGroupKey

INNER JOIN CusCustomerGroup AS CustGrp WITH (NOLOCK) ON CustGrp.CustomerGroupKey = Cust.CustomerGroupKey

INNER JOIN RtlStore WITH (NOLOCK) ON Trxn.StoreKey = RtlStore.StoreKey

  LEFT OUTER JOIN RtlSubsidiary WITH (NOLOCK) ON RtlStore.SubsidiaryKey = RtlSubsidiary.SubsidiaryKey

LEFT OUTER JOIN RtlPOS WITH (NOLOCK) ON Trxn.POSKey = RtlPOS.POSKey

LEFT OUTER JOIN RtlStore DelStore WITH (NOLOCK) ON DelStore.WarehouseKey = TrxnOrder.DeliveryWarehouseKey

LEFT OUTER JOIN RtlStore bookStore WITH (NOLOCK) ON bookStore.WarehouseKey = TrxnOrder.BookingWarehouseKey

Where

CONVERT(VARCHAR, FulDet.ProductPromisedDate, 112) Between Case When @FromDate = '' Then CONVERT(VARCHAR, FulDet.ProductPromisedDate, 112) else @FromDate End And Case When @ToDate = '' then CONVERT(VARCHAR, FulDet.ProductPromisedDate, 112) else @ToDate end --Date Filter                          --Date Filter

And  ((Prod.Id Between Case When @FromProduct = '' Then Prod.Id Else @FromProduct End And Case When @ToProduct = '' Then Prod.Id Else @ToProduct End)

    OR

 (Prod.Id Between Case When @FromProduct = '' Then Prod.Id Else @ToProduct End And Case When @ToProduct = '' Then Prod.Id Else @FromProduct End))                       --Product Filter

And  ((Cust.Id Between Case When @FromCustomer = '' Then Cust.Id Else @FromCustomer End And Case When @ToCustomer = '' Then Cust.Id Else @ToCustomer End)

  OR

 (Cust.Id Between Case When @FromCustomer = '' Then Cust.Id Else @ToCustomer End And Case When @ToCustomer = '' Then Cust.Id Else @FromCustomer End))

AND ISNULL (RtlSubsidiary.Id,'') = Case When @Subsidiary <> '' Then @Subsidiary Else ISNULL(RtlSubsidiary.Id,'') End

AND Trxn.IsVoided='False' AND Trxn.IsSuspended =0

AND (RtlStore.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@Store)) OR RtlStore.Id IN (CASE WHEN @Store='' THEN RtlStore.Id END))

AND (ISNULL(RtlPOS.Id,'') IN (SELECT DataString FROM [dbo].[fnSplitValues](@POS)) OR ISNULL(RtlPOS.Id,'') IN (CASE WHEN @POS='' THEN ISNULL(RtlPOS.Id,'') END))

AND (ProdGrp.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@ProductGroup)) OR ProdGrp.Id IN (CASE WHEN @ProductGroup='' THEN ProdGrp.Id END))

AND (CustGrp.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@CustomerGroup)) OR CustGrp.Id IN (CASE WHEN @CustomerGroup='' THEN CustGrp.Id END))

AND (DelStore.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@DelStore)) OR DelStore.Id IN (CASE WHEN @DelStore='' THEN DelStore.Id END))

  AND (bookStore.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@BookStore)) OR bookStore.Id IN (CASE WHEN @BookStore='' THEN bookStore.Id END))

End

If @IncludeLayAway = 1

BEGIN

INSERT INTO #temp

SELECT CONVERT(VARCHAR(50), ProductPromisedDate, 106) AS ProductPromisedDate, TransactionId,

   Cust.Id AS Cust, Cust.FirstName AS CustFirstName, Cust.MiddleName AS CustMiddleName, Cust.LastName AS CustLastName, Cust.PhoneNumber AS CustPhone,

   RtlStore.Id, RtlStore.Description AS StoreDescriotion, RTLSUBSIDIARY.Id, RTLSUBSIDIARY.Description AS SubsidiaryDescription,

   Prod.Id AS Item,

   TrxnLayaway.Description AS ItemDescription, TrxnLayaway.Quantity/ISNULL(TrxnLayaway.UOMBaseQuantity,1) AS Quantity, TrxnLayaway.OpenQuantity/ISNULL(TrxnLayaway.UOMBaseQuantity,1) AS OpenQuantity, TrxnLayaway.FullfilledQuantity/ISNULL(TrxnLayaway.UOMBaseQuantity,1) AS FullfilledQty,

   (CASE WHEN FulDet.SourceType = 0 THEN 'Sale' WHEN FulDet.SourceType = 1 THEN 'ORDER' WHEN FulDet.SourceType = 2 THEN 'Layaway' END) AS SourceType,

   FulDet.FulfillmentKey, ISNULL(UOM.Description,'') as UOM, RtlPOS.Id, BookStore.Id AS BookingStore, DelStore.Id AS DeliveryStore

From TrxTransaction AS Trxn WITH (NOLOCK)

INNER JOIN TrxTransactionLayaway AS TrxnLayaway WITH (NOLOCK) ON Trxn.TransactionKey = TrxnLayaway.TransactionKey

LEFT JOIN InvUOMGroupDetail  UOMD WITH (NOLOCK) ON TrxnLayaway.UOMGroupDetailKey=UOMD.UOMGroupDetailKey

LEFT JOIN InvUOM UOM WITH (NOLOCK) ON UOMD.UOMKey=UOM.UOMKey

INNER JOIN TrxTransactionFulfillmentDetail AS FulDet WITH (NOLOCK) ON Trxn.TransactionKey = FulDet.TransactionKey And TrxnLayaway.TransactionLayawayKey = FulDet.SourceDetailKey And FulDet.SourceType = 2 AND FulDet.IsDeleted=0

INNER JOIN CusCustomer AS Cust WITH (NOLOCK) ON Trxn.CustomerKey = Cust.CustomerKey

INNER JOIN InvProduct AS Prod WITH (NOLOCK) ON TrxnLayaway.ProductKey = Prod.ProductKey

INNER JOIN InvProductGroup AS ProdGrp WITH (NOLOCK) ON ProdGrp.ProductGroupKey = Prod.ProductGroupKey

INNER JOIN CusCustomerGroup AS CustGrp WITH (NOLOCK) ON CustGrp.CustomerGroupKey = Cust.CustomerGroupKey

INNER JOIN  RtlStore WITH (NOLOCK) ON Trxn.StoreKey = RtlStore.StoreKey

LEFT OUTER JOIN RtlSubsidiary WITH (NOLOCK) ON RtlStore.SubsidiaryKey = RtlSubsidiary.SubsidiaryKey

LEFT OUTER JOIN RtlPOS WITH (NOLOCK) ON Trxn.POSKey = RtlPOS.POSKey

LEFT OUTER JOIN RtlStore DelStore WITH (NOLOCK) ON DelStore.WarehouseKey = TrxnLayaway.DeliveryWarehouseKey

LEFT OUTER JOIN RtlStore bookStore WITH (NOLOCK) ON bookStore.WarehouseKey = TrxnLayaway.BookingWarehouseKey

Where CONVERT(VARCHAR, FulDet.ProductPromisedDate, 112) Between Case When @FromDate = '' Then CONVERT(VARCHAR, FulDet.ProductPromisedDate, 112) else @FromDate End And Case When @ToDate = '' then CONVERT(VARCHAR, FulDet.ProductPromisedDate, 112) else @ToDate end --Date Filter                          --Date Filter

And  ((Prod.Id Between Case When @FromProduct = '' Then Prod.Id Else @FromProduct End And Case When @ToProduct = '' Then Prod.Id Else @ToProduct End)

    OR

 (Prod.Id Between Case When @FromProduct = '' Then Prod.Id Else @ToProduct End And Case When @ToProduct = '' Then Prod.Id Else @FromProduct End))                       --Product Filter

And  ((Cust.Id Between Case When @FromCustomer = '' Then Cust.Id Else @FromCustomer End And Case When @ToCustomer = '' Then Cust.Id Else @ToCustomer End)

  OR                 --Customer Filter

 (Cust.Id Between Case When @FromCustomer = '' Then Cust.Id Else @ToCustomer End And Case When @ToCustomer = '' Then Cust.Id Else @FromCustomer End))

AND ISNULL (RtlSubsidiary.Id,'') = Case When @Subsidiary <> '' Then @Subsidiary Else ISNULL(RtlSubsidiary.Id,'') End

AND Trxn.IsVoided='False' AND Trxn.IsSuspended =0

AND (RtlStore.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@Store)) OR RtlStore.Id IN (CASE WHEN @Store='' THEN RtlStore.Id END))

AND (ISNULL(RtlPOS.Id,'') IN (SELECT DataString FROM [dbo].[fnSplitValues](@POS)) OR ISNULL(RtlPOS.Id,'') IN (CASE WHEN @POS='' THEN ISNULL(RtlPOS.Id,'') END))

AND (ProdGrp.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@ProductGroup)) OR ProdGrp.Id IN (CASE WHEN @ProductGroup='' THEN ProdGrp.Id END))

AND (CustGrp.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@CustomerGroup)) OR CustGrp.Id IN (CASE WHEN @CustomerGroup='' THEN CustGrp.Id END))

AND (DelStore.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@DelStore)) OR DelStore.Id IN (CASE WHEN @DelStore='' THEN DelStore.Id END))

  AND (bookStore.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@BookStore)) OR bookStore.Id IN (CASE WHEN @BookStore='' THEN bookStore.Id END))

End

Set NoCount Off

SELECT * From #temp

End