iVend Database Database > Stored Procedures

Navigation: iVend Database Database > Stored Procedures >

iVend 6.6 Database Help

MRP Stored Procedure

Collapse All Expand All

iVend Database Database : MRP Stored Procedure

Properties

Creation Date	4/13/2015 12:00 PM
Encrypted
Ansi Nulls

Parameters

Parameter	Direction	Description	Data Type	Size
@PlanningScenarioKey	In		VarWChar	50
@pSiteId	In	to identify the store	Integer	4
@RETURN_VALUE	Return Value		Integer	4

Objects that MRP depends on

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.	1
InvPlanningScenario	Table	Defines the planning scenario of a product for forecasting	1
InvPlanningScenarioDetails	Table	Defines the detailed information for the Inventory planning	1
InvProduct	Table	Stores the product related details.	1
InvProductCategory	Table	Defines the categories under which the products can be put for logical classification.	1
InvProductGroup	Table	Defined the various groups under which the products can be categorised.	1
InvWarehouse	Table	Defines a list of all warehouses defined in the system.	1
MRP_CalculateInOut	Stored Procedure		1
MRP_GenerateDemandForKit	Stored Procedure		1
RtlStore	Table	Contains the details of Stores defined in the system.	1

Procedure Source Code

CREATE PROCEDURE [dbo].[MRP]

(

@PlanningScenarioKey nvarchar(50),

@pSiteId INT

)

exec MRP 10000000000000001, 1

Select * From InvPlanningScenario

Select * from InvWarehouse HO - 10000000000000005, Store - 10000000000000004

Begin

Purchase Order - 1

Inventory Transfer - 2

Kitting - 3

Stock Transfer Shipment - 4

Stock Transfer Request - 5

Special Order - 6

Layaways - 7

Minimum Quantity - 8

Kit Explode - 9

Forecast - 10

Set NoCount On

Declare @FromDate varchar(8),

@ToDate varchar(8),

@BasedOn smallint,

@FromID nvarchar(40),

@ToID nvarchar(40),

@SelfWarehouseKey nvarchar(50),

--@ForecastMethod Smallint,

--@ForecastSubMethod Smallint,

@RecommendPurchase bit,

@PurchaseRecommendType int,

@RecommendStockTransfer bit,

@TimeRange int, --0 - Within Planning Horizon, 1- Include Historical Data,

@ConsiderDefaultLocation Bit,

@ConsiderSaleLocation Bit,

@ConsiderRefundLocation Bit,

@ConsiderFulfilmentLocation Bit,

@ConsiderGoodsReceiveLocation Bit,

@ConsiderStockTransferLocation Bit,

@ConsiderGoodsIssueLocation Bit,

@ConsiderGoodsReturnLocation Bit,

@ConsiderGoodsFreezeLocation Bit,

@ConsiderStorageLocation Bit,

@ERPSystemType smallint

Select @ERPSystemType = isNull(ERPSystemType, 0) From CfgEnterprise

Select @FromDate = Convert(Varchar, StartDate, 112),

@ToDate = Convert(Varchar, EndDate, 112),

@BasedOn = isNull(BasedOn, 1),

--@ForecastMethod = isNull(ForecastMethod, 0),

--@ForecastSubMethod = isNull(ForecastSubMethod, 0),

@RecommendPurchase = isNull(RecommendPurchase, 1),

@PurchaseRecommendType = isNull(PurchaseRecommendType, 1), --LocalPurchase = 1, AllPurchase = 2

@RecommendStockTransfer = isNull(RecommendStockTransfer, 1),

@TimeRange = isNull(TimeRange, 0),

@ConsiderDefaultLocation = ConsiderDefaultLocation,

@ConsiderSaleLocation = ConsiderSaleLocation,

@ConsiderRefundLocation = ConsiderRefundLocation,

@ConsiderFulfilmentLocation = ConsiderFulfilmentLocation,

@ConsiderGoodsReceiveLocation = ConsiderGoodsReceiveLocation,

@ConsiderStockTransferLocation = ConsiderStockTransferLocation,

@ConsiderGoodsIssueLocation = ConsiderGoodsIssueLocation,

@ConsiderGoodsReturnLocation = ConsiderGoodsReturnLocation,

@ConsiderGoodsFreezeLocation = ConsiderGoodsFreezeLocation,

@ConsiderStorageLocation = ConsiderStorageLocation

From InvPlanningScenario

Where PlanningScenarioKey = @PlanningScenarioKey

Select @SelfWarehouseKey = WarehouseKey From RtlStore Where SiteID = @pSiteId -- (Select SiteID From cfgSiteInformation)

Create Table #ReceiptsDemands

(

RowNum int identity(1, 1),

DueDate Datetime,

ProductKey nvarchar(50),

WarehouseKey nvarchar(50),

Quantity Decimal(20, 5),

HeaderKey nvarchar(50),

LineKey nvarchar(50),

ObjectId smallint,

Category smallint,

StockType char(1),

TfrLevel smallint,

--TfrSequenceSkipped Bit,

OrderDate Datetime Null,

OrderQty Decimal(20, 5) Default 0,

ToWarehouseKey nvarchar(50) Default 0,

ToWarehouseDueDate Datetime,

RecordAddedByMRP Bit Default 0

)

Create Table #StockInOut

(

RowNum int identity(1, 1),

DueDate Datetime,

ProductKey nvarchar(50),

WarehouseKey nvarchar(50),

QtyOB Decimal(20, 5) Default 0,

QtyOnHand Decimal(20, 5) Default 0,

QtyIn Decimal(20, 5) Default 0,

QtyOut Decimal(20, 5) Default 0,

QtyCB Decimal(20, 5) Default 0

)

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

BEGIN --Get All the warehouses for which the MRP needs to run.

Select InvPlanningScenarioDetails.WarehouseKey, InvWarehouse.IsLocationEnabled, InvWarehouse.SubsidiaryKey

Into #Warehouses

From InvPlanningScenarioDetails Inner Join InvWarehouse On InvPlanningScenarioDetails.WarehouseKey = InvWarehouse.WarehouseKey

Where PlanningScenarioKey = @PlanningScenarioKey

And InvPlanningScenarioDetails.IsSelected = 1

And InvPlanningScenarioDetails.IsDeleted = 0

END

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

BEGIN --Get All the items for which MRP needs to run.

Create Table #Items(ProductKey nvarchar(50), ProductGroupKey nvarchar(50), IsKit Bit)

If @BasedOn = 1

Begin

Select @FromID = InvProduct.Id,

@ToID = InvProduct1.Id

From InvPlanningScenario Inner Join InvProduct On InvPlanningScenario.FromKey = InvProduct.ProductKey

Inner Join InvProduct As InvProduct1 On InvPlanningScenario.ToKey = InvProduct1.ProductKey

Where PlanningScenarioKey = @PlanningScenarioKey

Insert Into #Items(ProductKey, ProductGroupKey)

Select ProductKey, ProductGroupKey

From InvProduct

Where ((Id Between @FromID And @ToID) Or (Id Between @ToID And @FromID))

And InvProduct.IsDeleted = 0

And InvProduct.IsNonStock = Case InvProduct.IsAssembly When 1 Then 1 Else 0 End

And InvProduct.IsOnHold = 0

And InvProduct.IsPurchasable = Case When InvProduct.IsKit = 1 Then InvProduct.IsPurchasable Else 1 End

And InvProduct.IsGiftCertificate = 0

And InvProduct.IsMatrixItem = 0

And InvProduct.IsStoreCredit = 0

End

Else If @BasedOn = 2

Begin

Select @FromID = InvProductCategory.Id,

@ToID = InvProductCategory1.Id

From InvPlanningScenario Inner Join InvProductCategory On InvPlanningScenario.FromKey = InvProductCategory.ProductCategoryKey

Inner Join InvProductCategory As InvProductCategory1 On InvPlanningScenario.ToKey = InvProductCategory1.ProductCategoryKey

Where PlanningScenarioKey = @PlanningScenarioKey

Insert Into #Items(ProductKey, ProductGroupKey)

Select InvProduct.ProductKey, InvProduct.ProductGroupKey

From InvProductCategoryProduct Inner Join InvProductCategory On InvProductCategoryProduct.ProductCategoryKey = InvProductCategory.ProductCategoryKey

Inner Join InvProduct On InvProductCategoryProduct.ProductKey = InvProduct.ProductKey

Where ((InvProductCategory.Id Between @FromID And @ToID) Or (InvProductCategory.Id Between @ToID And @FromID))

And InvProduct.IsNonStock = Case InvProduct.IsAssembly When 1 Then 1 Else 0 End

And InvProduct.IsOnHold = 0

And InvProduct.IsDeleted = 0

And InvProduct.IsPurchasable = Case When InvProduct.IsKit = 1 Then InvProduct.IsPurchasable Else 1 End

And InvProduct.IsGiftCertificate = 0

And InvProduct.IsMatrixItem = 0

And InvProduct.IsStoreCredit = 0

End

Else If @BasedOn = 3

Begin

Select @FromID = InvProductGroup.Id,

@ToID = InvProductGroup1.Id

From InvPlanningScenario Inner Join InvProductGroup On InvPlanningScenario.FromKey = InvProductGroup.ProductGroupKey

Inner Join InvProductGroup As InvProductGroup1 On InvPlanningScenario.ToKey = InvProductGroup1.ProductGroupKey

Where PlanningScenarioKey = @PlanningScenarioKey

Insert Into #Items(ProductKey, ProductGroupKey)

Select InvProduct.ProductKey, InvProduct.ProductGroupKey

From InvProduct Inner Join InvProductGroup On InvProduct.ProductGroupKey = InvProductGroup.ProductGroupKey

Where ((InvProductGroup.Id Between @FromID And @ToID) Or (InvProductGroup.Id Between @ToID And @FromID))

And InvProduct.IsNonStock = Case InvProduct.IsAssembly When 1 Then 1 Else 0 End

And InvProduct.IsOnHold = 0

And InvProduct.IsDeleted = 0

And InvProduct.IsPurchasable = Case When InvProduct.IsKit = 1 Then InvProduct.IsPurchasable Else 1 End

And InvProduct.IsGiftCertificate = 0

And InvProduct.IsMatrixItem = 0

And InvProduct.IsStoreCredit = 0

End

--exec MRP 10000000000000003, 1

--Select * From #Items

--Return

Update Items

Set IsKit = InvProduct.IsKit

From #Items As Items Inner Join InvProduct On Items.ProductKey = InvProduct.ProductKey

--Get all the child items of the above Kit Item (if any)

Insert Into #Items(ProductKey, ProductGroupKey)

Select InvKitComponent.ProductKey, InvProduct.ProductGroupKey

From InvKitComponent Inner Join #Items As temp On InvKitComponent.ParentProductKey = temp.ProductKey

Inner Join InvProduct on InvKitComponent.ProductKey = InvProduct.ProductKey And InvProduct.IsDeleted = 0 And InvProduct.IsNonStock = 0 And InvProduct.IsOnHold = 0

Except Select ProductKey, ProductGroupKey From #Items

--Get all the child items of the above Assembly Item

Insert Into #Items(ProductKey, ProductGroupKey)

Select InvAssemblyComponent.ProductKey, InvProduct.ProductGroupKey

From InvAssemblyComponent Inner Join #Items As temp On InvAssemblyComponent.ParentProductKey = temp.ProductKey

Inner Join InvProduct on InvAssemblyComponent.ProductKey = InvProduct.ProductKey And InvProduct.IsDeleted = 0 And InvProduct.IsNonStock = 0 And InvProduct.IsOnHold = 0

Except Select ProductKey, ProductGroupKey From #Items

END

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

BEGIN --Get All the item warehouse combination for which MRP needs to run.

Select Warehouses.WarehouseKey, InvInventoryItem.ProductKey, InvInventoryItem.MinimumStockLevel,

Case Warehouses.IsLocationEnabled When 'TRUE' Then isNull(InventoryLocation.InStockQuantity, 0) Else InvInventoryItem.InStockQuantity End As InStockQuantity,

--InvInventoryItem.InStockQuantity,

InvInventoryItem.LeadTime, InvInventoryItem.MinimumOrderQuantity,

InvInventoryItem.ReplenishmentMethod, InvInventoryItem.FulfillmentWarehouseKey, Items.ProductGroupKey, 0 As TfrLevel, Warehouses.SubsidiaryKey

Into #ItemWarehouse

From #Items As Items Cross Join #Warehouses As Warehouses

Inner Join InvInventoryItem On Items.ProductKey = InvInventoryItem.ProductKey And Warehouses.WarehouseKey = InvInventoryItem.WarehouseKey And isLocked = 0

Left Outer Join (

Select InvInventoryLocation.ProductKey, InvInventoryLocation.WarehouseKey, Sum(InvInventoryLocation.InStockQuantity) As InStockQuantity

From InvInventoryLocation Inner Join InvLocation On InvInventoryLocation.LocationKey = InvLocation.LocationKey And InvLocation.IsDeleted = 0 And InvLocation.IsActive = 1

Where (

(InvLocation.IsDefault = 1 And @ConsiderDefaultLocation = 1)

(InvLocation.Sales = 1 And @ConsiderSaleLocation = 1)

(InvLocation.Refund = 1 And @ConsiderRefundLocation = 1)

(InvLocation.Fulfilment = 1 And @ConsiderFulfilmentLocation = 1)

(InvLocation.GoodsReceive = 1 And @ConsiderGoodsReceiveLocation = 1)

(InvLocation.StockTransfer = 1 And @ConsiderStockTransferLocation = 1)

(InvLocation.GoodsIssue = 1 And @ConsiderGoodsIssueLocation = 1)

(InvLocation.GoodsReturn = 1 And @ConsiderGoodsReturnLocation = 1)

(InvLocation.Freeze = 1 And @ConsiderGoodsFreezeLocation = 1)

(InvLocation.IsDefault = 0 And InvLocation.Sales = 0 And InvLocation.Refund = 0 And InvLocation.Fulfilment = 0 And InvLocation.GoodsReceive = 0 And InvLocation.StockTransfer = 0 And InvLocation.GoodsIssue = 0 And InvLocation.GoodsReturn = 0 And InvLocation.Freeze = 0 And @ConsiderStorageLocation = 1)

)

Group By InvInventoryLocation.ProductKey, InvInventoryLocation.WarehouseKey

) As InventoryLocation On Items.ProductKey = InventoryLocation.ProductKey And Warehouses.WarehouseKey = InventoryLocation.WarehouseKey

Where InvInventoryItem.ConsiderInMRP = 1

--Select * From #ItemWarehouse

--Return

;with CTE As

(

Select ProductKey,WarehouseKey,ReplenishmentMethod,FulfillmentWarehouseKey,0 As TfrLevel from #ItemWarehouse Where ReplenishmentMethod in (1, 3)

Union All

Select T1.ProductKey,T1.WarehouseKey,T1.ReplenishmentMethod,T1.FulfillmentWarehouseKey,T2.TfrLevel+1 As TfrLevel

From #ItemWarehouse T1 Join CTE T2 On T1.FulfillmentWarehouseKey = T2.WarehouseKey And T1.ProductKey = T2.ProductKey And T1.ReplenishmentMethod = 2

)

Update ItemWarehouse

Set TfrLevel = CTE.TfrLevel

From #ItemWarehouse As ItemWarehouse Inner Join CTE On ItemWarehouse.ProductKey = CTE.ProductKey And ItemWarehouse.WarehouseKey = CTE.WarehouseKey

Update ItemWarehouse

Set TfrLevel = TfrLevel + (Select Max(TfrLevel) From #ItemWarehouse)

From #ItemWarehouse as ItemWarehouse

Inner Join #Items as Items on Items.ProductKey=ItemWarehouse.ProductKey And Items.IsKit=1

--Delete those item warehouse combination where item does not belong to the corresponding warehouse subsidiary

Delete ItemWarehouse

From #ItemWarehouse as ItemWarehouse Left Outer Join SubSubsidiaryItem On ItemWarehouse.ProductKey = SubSubsidiaryItem.SourceKey And SubSubsidiaryItem.SourceType = 46

And ItemWarehouse.SubsidiaryKey = SubSubsidiaryItem.SubsidiaryKey

Where SubSubsidiaryItem.SubsidiaryItemKey is Null

And ItemWarehouse.SubsidiaryKey > '0'

END

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

BEGIN --Delete those Item Warehouse combination Where Product is on hold or Locked

Delete ItemWarehouse

From #ItemWarehouse As ItemWarehouse Inner Join InvInventoryItem On ItemWarehouse.ProductKey = InvInventoryItem.ProductKey

And ItemWarehouse.WarehouseKey = InvInventoryItem.WarehouseKey

Where InvInventoryItem.IsOnHold = 1

Or InvInventoryItem.IsLocked = 1

END

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

BEGIN --Historic Receipts

--Purchase Order

Select PurPurchaseOrderDetail.ProductKey As ProductKey, PurPurchaseOrderDetail.WarehouseKey As WarehouseKey, (PurPurchaseOrderDetail.Quantity - PurPurchaseOrderDetail.QuantityReceived) As Quantity,

Convert(Varchar, PurPurchaseOrder.DeliveryDate, 112) As DueDate, PurPurchaseOrder.PurchaseOrderKey As HeaderKey, PurPurchaseOrderDetail.PurchaseOrderDetailKey As LineKey,

1 As Category, 97 As ObjectId, 'R' As StockType

Into #tempHistoricReceipts

From PurPurchaseOrder Inner Join PurPurchaseOrderDetail On PurPurchaseOrder.PurchaseOrderKey = PurPurchaseOrderDetail.PurchaseOrderKey

Inner Join #ItemWarehouse As ItemWarehouse On PurPurchaseOrderDetail.ProductKey = ItemWarehouse.ProductKey

And PurPurchaseOrderDetail.WarehouseKey = ItemWarehouse.WarehouseKey

Where PurPurchaseOrder.Status = 0

And PurPurchaseOrder.IsDeleted = 0

--And PurPurchaseOrder.IsAuthorized = 1

And PurPurchaseOrderDetail.Status = 0

And PurPurchaseOrderDetail.IsDeleted = 0

And (PurPurchaseOrderDetail.Quantity - PurPurchaseOrderDetail.QuantityReceived) > 0

And Convert(Varchar, PurPurchaseOrder.DeliveryDate, 112) < @FromDate

And @TimeRange = 1 --Include Historical Data

UNION ALL

--Stock Transfer Shipment (To be received at the receiving warehouse)

Select InvStockTransferDetail.ProductKey, InvStockTransferDetail.ToWarehouseKey, InvStockTransferDetail.QuantityOpen,

Convert(Varchar, InvStockTransfer.RequestedDate + ItemWarehouse.LeadTime, 112), InvStockTransfer.StockTransferKey, InvStockTransferDetail.StockTransferDetailKey,

4 As Category, 53 As ObjectID, 'R' As StockType

From InvStockTransfer Inner Join InvStockTransferDetail On InvStockTransfer.StockTransferKey = InvStockTransferDetail.StockTransferKey

Inner Join #ItemWarehouse As ItemWarehouse On InvStockTransferDetail.ProductKey = ItemWarehouse.ProductKey

And InvStockTransferDetail.ToWarehouseKey = ItemWarehouse.WarehouseKey

Where InvStockTransfer.Status = 0

And InvStockTransferDetail.Status = 0

And InvStockTransferDetail.QuantityOpen > 0

And Convert(Varchar, InvStockTransfer.RequestedDate + isNull(ItemWarehouse.LeadTime, 0), 112) < @FromDate

And @TimeRange = 1 --Include Historical Data

UNION ALL

--Stock Transfer Request (To be received at the requesting warehouse)

Select InvStockTransferRequestDetail.ProductKey, InvStockTransferRequest.FromWarehouseKey, InvStockTransferRequestDetail.QuantityOpen As OpenQty,

InvStockTransferRequest.RequestedDate As DueDate, InvStockTransferRequest.StockTransferRequestKey, InvStockTransferRequestDetail.StockTransferRequestDetailKey,

5 As Category, 215 As ObjectID, 'R' As StockType

From InvStockTransferRequest Inner Join InvStockTransferRequestDetail On InvStockTransferRequest.StockTransferRequestKey = InvStockTransferRequestDetail.StockTransferRequestKey

Inner Join #ItemWarehouse As ItemWarehouse On InvStockTransferRequestDetail.ProductKey = ItemWarehouse.ProductKey

And InvStockTransferRequest.FromWarehouseKey = ItemWarehouse.WarehouseKey

Where InvStockTransferRequest.Status = 0

And InvStockTransferRequest.SourceType = 1

And InvStockTransferRequestDetail.Status = 0

And InvStockTransferRequestDetail.QuantityOpen > 0

And Convert(Varchar, InvStockTransferRequest.RequestedDate, 112) < @FromDate

And @TimeRange = 1 --Include Historical Data

And (@ERPSystemType <> 0 Or InvStockTransferRequest.AcceptanceStatus in (2,3)) --Allow on Accepted=2 or PartialAccepted=3 Request

Insert Into #ReceiptsDemands(DueDate, ProductKey, WarehouseKey, Quantity, HeaderKey, LineKey, ObjectId, Category, StockType)

Select DueDate, ProductKey, WarehouseKey, Quantity, HeaderKey, LineKey, ObjectId, Category, StockType

From #tempHistoricReceipts

END

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

BEGIN --Historic Demands

--Sales Order

Select TrxTransactionOrder.ProductKey As ProductKey, TrxTransactionOrder.DeliveryWarehouseKey As WarehouseKey, (TrxTransactionFulfillmentDetail.Quantity - TrxTransactionFulfillmentDetail.QuantityFulfilled) As Quantity,

Convert(Varchar, TrxTransactionFulfillmentDetail.ProductPromisedDate, 112) As DueDate, TrxTransaction.TransactionKey As HeaderKey, TrxTransactionOrder.TransactionOrderKey As LineKey,

6 As Category, 160 As ObjectID, 'I' As StockType, 0 As ToWarehouseKey

Into #tempHistoricDemands

From TrxTransactionOrder Inner Join TrxTransaction On TrxTransactionOrder.TransactionKey = TrxTransaction.TransactionKey

Inner Join TrxTransactionFulfillment On TrxTransaction.TransactionKey = TrxTransactionFulfillment.TransactionKey

Inner Join TrxTransactionFulfillmentDetail On TrxTransaction.TransactionKey = TrxTransactionFulfillmentDetail.TransactionKey And TrxTransactionOrder.ProductKey = TrxTransactionFulfillmentDetail.ProductKey

Inner Join #ItemWarehouse As ItemWarehouse On TrxTransactionOrder.ProductKey = ItemWarehouse.ProductKey

And TrxTransactionOrder.DeliveryWarehouseKey = ItemWarehouse.WarehouseKey

Where TrxTransaction.IsSuspended = 0

And TrxTransaction.HasOrders = 1

And TrxTransaction.HasFulFillments = 1

And TrxTransaction.HasCancelledSalesOrder = 0

And TrxTransactionOrder.Status = 0

And TrxTransactionFulfillment.Status = 0

And TrxTransactionFulfillment.IsDeleted = 0

And TrxTransactionFulfillmentDetail.SourceType = 1

And TrxTransactionFulfillmentDetail.IsDeleted = 0

And (TrxTransactionFulfillmentDetail.Quantity - TrxTransactionFulfillmentDetail.QuantityFulfilled) > 0

And Convert(Varchar, TrxTransactionFulfillmentDetail.ProductPromisedDate, 112) < @FromDate

And @TimeRange = 1 --Include Historical Data

UNION ALL

--Layaway With Delivery Fulfillment

Select TrxTransactionLayaway.ProductKey, TrxTransactionLayaway.DeliveryWarehouseKey, (TrxTransactionFulfillmentDetail.Quantity - TrxTransactionFulfillmentDetail.QuantityFulfilled) As OpenQty,

Convert(Varchar, TrxTransactionFulfillmentDetail.ProductPromisedDate, 112) As DueDate, TrxTransaction.TransactionKey, TrxTransactionLayaway.TransactionLayawayKey,

7 As Category, 148 As ObjectID, 'I' As StockType, 0 As ToWarehouseKey

From TrxTransactionLayaway Inner Join TrxTransaction On TrxTransactionLayaway.TransactionKey = TrxTransaction.TransactionKey

Inner Join TrxTransactionFulfillment On TrxTransaction.TransactionKey = TrxTransactionFulfillment.TransactionKey

Inner Join FulFulfillmentPlan On TrxTransactionFulfillment.FulfillmentPlanKey = FulFulfillmentPlan.FulfillmentPlanKey And FulFulfillmentPlan.InventoryAllocationMethod = 1

Inner Join TrxTransactionFulfillmentDetail On TrxTransaction.TransactionKey = TrxTransactionFulfillmentDetail.TransactionKey And TrxTransactionLayaway.ProductKey = TrxTransactionFulfillmentDetail.ProductKey

Inner Join #ItemWarehouse As ItemWarehouse On TrxTransactionLayaway.ProductKey = ItemWarehouse.ProductKey

And TrxTransactionLayaway.DeliveryWarehouseKey = ItemWarehouse.WarehouseKey

Where TrxTransaction.IsSuspended = 0

And TrxTransaction.HasLayaways = 1

And TrxTransaction.HasFulFillments = 1

And TrxTransaction.HasLayawaysCancellations = 0

And TrxTransactionLayaway.Status = 0

And TrxTransactionFulfillment.Status = 0

And TrxTransactionFulfillment.IsDeleted = 0

And TrxTransactionFulfillmentDetail.SourceType = 2

And TrxTransactionFulfillmentDetail.IsDeleted = 0

And (TrxTransactionFulfillmentDetail.Quantity - TrxTransactionFulfillmentDetail.QuantityFulfilled) > 0

And Convert(Varchar, TrxTransactionFulfillmentDetail.ProductPromisedDate, 112) < @FromDate

And @TimeRange = 1 --Include Historical Data

UNION ALL

--Stock Transfer Request (Requesting Warehouse)

Select InvStockTransferRequestDetail.ProductKey, InvStockTransferRequest.ToWarehouseKey, InvStockTransferRequestDetail.QuantityOpen As OpenQty,

(InvStockTransferRequest.RequestedDate - ItemWarehouse.LeadTime) As DueDate, InvStockTransferRequest.StockTransferRequestKey, InvStockTransferRequestDetail.StockTransferRequestDetailKey,

5 As Category, 215 As ObjectID, 'I' As StockType, InvStockTransferRequest.ToWarehouseKey As ToWarehouseKey

From InvStockTransferRequest Inner Join InvStockTransferRequestDetail On InvStockTransferRequest.StockTransferRequestKey = InvStockTransferRequestDetail.StockTransferRequestKey

Inner Join #ItemWarehouse As ItemWarehouse On InvStockTransferRequestDetail.ProductKey = ItemWarehouse.ProductKey

And InvStockTransferRequest.ToWarehouseKey = ItemWarehouse.WarehouseKey

Where InvStockTransferRequest.Status = 0

And InvStockTransferRequest.SourceType = 1

And InvStockTransferRequestDetail.Status = 0

And InvStockTransferRequestDetail.QuantityOpen > 0

And Convert(Varchar, (InvStockTransferRequest.RequestedDate - ItemWarehouse.LeadTime), 112) < @FromDate

And @TimeRange = 1 --Include Historical Data

And (@ERPSystemType <> 0 Or InvStockTransferRequest.AcceptanceStatus in (2,3)) --Allow on Accepted=2 or PartialAccepted=3 Request

Insert Into #ReceiptsDemands(DueDate, ProductKey, WarehouseKey, Quantity, HeaderKey, LineKey, ObjectId, Category, StockType, ToWarehouseKey)

Select DueDate, ProductKey, WarehouseKey, Quantity, HeaderKey, LineKey, ObjectId, Category, StockType, ToWarehouseKey From #tempHistoricDemands

END

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

BEGIN --Past Due Receipts

--Purchase Order

Select PurPurchaseOrderDetail.ProductKey, PurPurchaseOrderDetail.WarehouseKey, (PurPurchaseOrderDetail.Quantity - PurPurchaseOrderDetail.QuantityReceived) As Quantity,

Convert(Varchar, PurPurchaseOrder.DeliveryDate, 112) As DueDate, PurPurchaseOrder.PurchaseOrderKey As HeaderKey, PurPurchaseOrderDetail.PurchaseOrderDetailKey As LineKey,

1 As Category, 97 As ObjectID, 'R' As StockType

Into #tempPastDueReceipts

From PurPurchaseOrder Inner Join PurPurchaseOrderDetail On PurPurchaseOrder.PurchaseOrderKey = PurPurchaseOrderDetail.PurchaseOrderKey

Inner Join #ItemWarehouse As ItemWarehouse On PurPurchaseOrderDetail.ProductKey = ItemWarehouse.ProductKey

And PurPurchaseOrderDetail.WarehouseKey = ItemWarehouse.WarehouseKey

Where PurPurchaseOrder.Status = 0

And PurPurchaseOrder.IsDeleted = 0

--And PurPurchaseOrder.IsAuthorized = 1

And PurPurchaseOrderDetail.Status = 0

And PurPurchaseOrderDetail.IsDeleted = 0

And (PurPurchaseOrderDetail.Quantity - PurPurchaseOrderDetail.QuantityReceived) > 0

And Convert(Varchar, PurPurchaseOrder.DeliveryDate, 112) Between @FromDate And Convert(Varchar, dbo.GetCompanyDateTime() - 1, 112)

UNION ALL

--Stock Transfer Shipment (To be received at the receiving warehouse)

Select InvStockTransferDetail.ProductKey, InvStockTransferDetail.ToWarehouseKey, InvStockTransferDetail.QuantityOpen,

Convert(Varchar, InvStockTransfer.RequestedDate + ItemWarehouse.LeadTime, 112), InvStockTransfer.StockTransferKey, InvStockTransferDetail.StockTransferDetailKey,

4 As Category, 53 As ObjectID, 'R' As StockType

From InvStockTransfer Inner Join InvStockTransferDetail On InvStockTransfer.StockTransferKey = InvStockTransferDetail.StockTransferKey

Inner Join #ItemWarehouse As ItemWarehouse On InvStockTransferDetail.ProductKey = ItemWarehouse.ProductKey

And InvStockTransferDetail.ToWarehouseKey = ItemWarehouse.WarehouseKey

Where InvStockTransfer.Status = 0

And InvStockTransferDetail.Status = 0

And InvStockTransferDetail.QuantityOpen > 0

And Convert(Varchar, InvStockTransfer.RequestedDate + isNull(ItemWarehouse.LeadTime, 0), 112) Between @FromDate And Convert(Varchar, dbo.GetCompanyDateTime() - 1, 112)

UNION ALL

--Stock Transfer Request (To be received at the requesting warehouse)

Select InvStockTransferRequestDetail.ProductKey, InvStockTransferRequest.FromWarehouseKey, InvStockTransferRequestDetail.QuantityOpen As OpenQty,

InvStockTransferRequest.RequestedDate As DueDate, InvStockTransferRequest.StockTransferRequestKey, InvStockTransferRequestDetail.StockTransferRequestDetailKey,

5 As Category, 215 As ObjectID, 'R' As StockType

From InvStockTransferRequest Inner Join InvStockTransferRequestDetail On InvStockTransferRequest.StockTransferRequestKey = InvStockTransferRequestDetail.StockTransferRequestKey

Inner Join #ItemWarehouse As ItemWarehouse On InvStockTransferRequestDetail.ProductKey = ItemWarehouse.ProductKey

And InvStockTransferRequest.FromWarehouseKey = ItemWarehouse.WarehouseKey

Where InvStockTransferRequest.Status = 0

And InvStockTransferRequest.SourceType = 1

And InvStockTransferRequestDetail.Status = 0

And InvStockTransferRequestDetail.QuantityOpen > 0

And Convert(Varchar, InvStockTransferRequest.RequestedDate, 112) Between @FromDate And Convert(Varchar, dbo.GetCompanyDateTime() - 1, 112)

And (@ERPSystemType <> 0 Or InvStockTransferRequest.AcceptanceStatus in (2,3)) --Allow on Accepted=2 or PartialAccepted=3 Request

Insert Into #ReceiptsDemands(DueDate, ProductKey, WarehouseKey, Quantity, HeaderKey, LineKey, ObjectId, Category, StockType)

Select DueDate, ProductKey, WarehouseKey, Quantity, HeaderKey, LineKey, ObjectId, Category, StockType From #tempPastDueReceipts

END

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

BEGIN --Past Due Demands

--Sales Order

Select TrxTransactionOrder.ProductKey, TrxTransactionOrder.DeliveryWarehouseKey As WarehouseKey, (TrxTransactionFulfillmentDetail.Quantity - TrxTransactionFulfillmentDetail.QuantityFulfilled) As Quantity,

Convert(Varchar, TrxTransactionFulfillmentDetail.ProductPromisedDate, 112) As DueDate, TrxTransaction.TransactionKey As HeaderKey, TrxTransactionOrder.TransactionOrderKey As LineKey,

6 As Category, 160 As ObjectID, 'I' As StockType, 0 As ToWarehouseKey

Into #tempPastDueDemands

From TrxTransactionOrder Inner Join TrxTransaction On TrxTransactionOrder.TransactionKey = TrxTransaction.TransactionKey

Inner Join TrxTransactionFulfillment On TrxTransaction.TransactionKey = TrxTransactionFulfillment.TransactionKey

Inner Join #ItemWarehouse As ItemWarehouse On TrxTransactionOrder.ProductKey = ItemWarehouse.ProductKey

And TrxTransactionOrder.DeliveryWarehouseKey = ItemWarehouse.WarehouseKey

Where TrxTransaction.IsSuspended = 0

And TrxTransaction.HasOrders = 1

And TrxTransaction.HasFulFillments = 1

And TrxTransaction.HasCancelledSalesOrder = 0

And TrxTransactionOrder.Status = 0

And TrxTransactionFulfillment.Status = 0

And TrxTransactionFulfillment.IsDeleted = 0

And TrxTransactionFulfillmentDetail.SourceType = 1

And TrxTransactionFulfillmentDetail.IsDeleted = 0

And (TrxTransactionFulfillmentDetail.Quantity - TrxTransactionFulfillmentDetail.QuantityFulfilled) > 0

And Convert(Varchar, TrxTransactionFulfillmentDetail.ProductPromisedDate, 112) Between @FromDate And Convert(Varchar, dbo.GetCompanyDateTime() - 1, 112)