
/*
If there are no inventory transfers on this database, 
	1) Create a new store to be the Transfer From store
	2) Add three items to the new store (Filet, Waffle Fries, Nuggets)
	3) Create a received transfer for one items (Nuggets)
	4) Create an open transfer for two items (Filet, Waffles Fries)

*/

/*
	Item							Itemid
	CFA Filet - Freezer					16
	Waffle Fries, Large - Freezer		883
	Nuggets (4) - Freezer				18
*/



begin tran
IF NOT EXISTS (select TransactionTransferId from tbTransactionTransfer)
BEGIN
	
	DECLARE @TransferStoreName VARCHAR(50) = 'TransferStore'
	DECLARE @TrainingNameSearch VARCHAR(50) = '%InForm%'
			
	-- 1) Create a new transfer store
	IF NOT EXISTS (select entityId from tbEntity where entity like @TransferStoreName and entityTypeId = 4)
	BEGIN
		INSERT INTO tbEntity  (Entity,	  EntityTypeId, OwnerEntityId, Address1,				  Address2, City, [State], Postcode, Country, Telephone,	 Fax, MailAddress1, MailAddress2, MailCity, MailState, MailPostcode, MailCountry, ReportsTo, Style, Class, Status, Territory, Ownership, AreaManager, CurrentPeriod, Currency, TaxCode, ApplyTax_Product, FiscalYear, ABN, LegalOwner, FirstName, LastName, Contact_Phone, Contact_Fax, Contact_Email, Contact_Mobile1, FirstName2, LastName2, Contact_Phone2, Contact_Fax2, Contact_Mobile2, FirstName3, LastName3, Contact_Phone3, Contact_Fax3, Contact_Email3, Contact_Mobile3, OpenDate, CloseDate, DistributorId, ForecastNewStoreEstimateType, ForecastSimilarStoreID, ForecastNewStorePercent, ForecastStoreForecastPercent, LocationId, SiteType, ReturnType, PerformForecast, RoyaltyModel, PrincipalRoyalty, PayNote, PayFeesInstruction, PayAdFundInstruction, IncludeInStoreSales, IncldeInPurchaseReports, IncludeInBudgetReports, IncludeInManageReports, UseGPManager, UseSpeedOfService, OpenTime, CloseTime, OpenSegment, CloseSegment, StoreTitle, StoreNumber, KPICOGS, DefaultAccountLinkID, GLAssignmentCode, GLCostCentreCode, AccSystemID, PurchaseJournal, PayrollCompanyID, PayrollDivisionID, PayrollDepartmentID, TimeZoneOffsetHours, LanguageId, QuatroID, LongDateFormat, ShortDateFormat, CurrentBalance, IsActive, DefaultRegisterFloat, OrderRouting, CultureId, WebSiteId, StoreChangeFund, ExportStartDate, DefaultStoreTotalFloat, TargetStoreFloat, NumberOfRegisterFloats, SkinId, GLN, IsFeeTaxable, Description2, ViewRightsEntityID, GrossArea, SellingArea, TempCloseDate, ReopenDate, TrafficCounterID, CalendarID, TestMarketID, PricingGroupID, StoreConfigurationID, OperatorTeamID, ServiceTeamID, StoreEmail, Latitude, Longitude, TimeZone, LiveStatusId, StoreIPAddress, LiveStatus, ACN, PromotionPathID, EDIInterchangeID, County, GlobalStoreNumber, EntityOwnershipID, EquipmentTypeID, OutdoorArea, OutdoorSeats, IndoorArea, IndoorSeats, MeetingTables, GeographicCityID, PreferredDistributionCenterId, CanZeroSuggestedQty, CanEditBuildTo )
			select top 1 'TransferStore', EntityTypeId, OwnerEntityId, '5010 Montgomery Blvd NE', Address2, City, [State], Postcode, Country, '505-8844172', Fax, MailAddress1, MailAddress2, MailCity, MailState, MailPostcode, MailCountry, ReportsTo, Style, Class, Status, Territory, Ownership, AreaManager, CurrentPeriod, Currency, TaxCode, ApplyTax_Product, FiscalYear, ABN, LegalOwner, FirstName, LastName, Contact_Phone, Contact_Fax, Contact_Email, Contact_Mobile1, FirstName2, LastName2, Contact_Phone2, Contact_Fax2, Contact_Mobile2, FirstName3, LastName3, Contact_Phone3, Contact_Fax3, Contact_Email3, Contact_Mobile3, OpenDate, CloseDate, DistributorId, ForecastNewStoreEstimateType, ForecastSimilarStoreID, ForecastNewStorePercent, ForecastStoreForecastPercent, LocationId, SiteType, ReturnType, PerformForecast, RoyaltyModel, PrincipalRoyalty, PayNote, PayFeesInstruction, PayAdFundInstruction, IncludeInStoreSales, IncldeInPurchaseReports, IncludeInBudgetReports, IncludeInManageReports, UseGPManager, UseSpeedOfService, OpenTime, CloseTime, OpenSegment, CloseSegment, StoreTitle, '11111',	   KPICOGS, DefaultAccountLinkID, GLAssignmentCode, GLCostCentreCode, AccSystemID, PurchaseJournal, PayrollCompanyID, PayrollDivisionID, PayrollDepartmentID, TimeZoneOffsetHours, LanguageId, QuatroID, LongDateFormat, ShortDateFormat, CurrentBalance, IsActive, DefaultRegisterFloat, OrderRouting, CultureId, WebSiteId, StoreChangeFund, ExportStartDate, DefaultStoreTotalFloat, TargetStoreFloat, NumberOfRegisterFloats, SkinId, GLN, IsFeeTaxable, Description2, ViewRightsEntityID, GrossArea, SellingArea, TempCloseDate, ReopenDate, TrafficCounterID, CalendarID, TestMarketID, PricingGroupID, StoreConfigurationID, OperatorTeamID, ServiceTeamID, StoreEmail, Latitude, Longitude, TimeZone, LiveStatusId, StoreIPAddress, LiveStatus, ACN, PromotionPathID, EDIInterchangeID, County, GlobalStoreNumber, EntityOwnershipID, EquipmentTypeID, OutdoorArea, OutdoorSeats, IndoorArea, IndoorSeats, MeetingTables, GeographicCityID, PreferredDistributionCenterId, CanZeroSuggestedQty, CanEditBuildTo
		FROM tbEntity
		WHERE entitytypeid = 4
	END
			  
	DECLARE @TransferEntityId bigint = (select entityId from tbEntity where entity like @TransferStoreName and entityTypeId = 4)	
	DECLARE @ReceivedStatus varchar(15) = 'Received'
	DECLARE @OpenStatus varchar(15) = 'Transfered'
	DECLARE @OpenTransferDate datetime = '2020-01-16 10:25:33.000'
	DECLARE @ReceivedTransferDate datetime = '2020-01-15 12:25:33.000'
	DECLARE @NameBase varchar(30)  = 'InFORM Training'
	DECLARE @InitiatedBy varchar(30)  = 'Transfer User'
	DECLARE @TransferReasonId bigint = 4
	DECLARE @OpenItemId1 bigint = 16
	DECLARE @OpenItemId2 bigint = 883
	DECLARE @ReceivedItemId bigint = 18
	DECLARE @VendorId bigint = 10
	DECLARE @TransferId bigint
	
	DECLARE toEntity_cursor CURSOR FOR   
		SELECT EntityId FROM tbEntity 
		WHERE Entity LIKE @TrainingNameSearch AND [Status] = 'Open'
	OPEN toEntity_cursor  
    DECLARE @ToEntity bigint
    FETCH NEXT FROM toEntity_cursor INTO @ToEntity  
    
	-- 2a) Create Vendor Entity relationship for transfer store
	DECLARE @VendorEntityId bigint = (select top 1 VendorEntityId from tbVendorEntity where vendorId = @VendorId and entityId = @toEntity)
	IF NOT EXISTS  (select vendorId from tbVendorEntity where VendorId = @VendorId and EntityId = @TransferEntityId)
	INSERT INTO tbVendorEntity (VendorID, EntityID, DaysRequiredIn, EntityAccountCode, LeadTime, AutoClearBackOrder, InvoiceVendor, IncludeVendorInvoiceInRemit, OrderFrequency, BlanketOrderRequired, QuatroID, CreateQuatroOrder, OrderItemCode, DistribCode, GenerateCreditNote, OverWriteTax, FreightTaxable, ScheduledOrderExtraDays, Split, EmailOrder, DistributionCenterId, AutoReceive, AutoReceiveDelayMinutes, SuppressOrderDetails, NormalDeliveryTime, PreviousVendorId)
		select VendorID, @TransferEntityId, DaysRequiredIn, EntityAccountCode, LeadTime, AutoClearBackOrder, InvoiceVendor, IncludeVendorInvoiceInRemit, OrderFrequency, BlanketOrderRequired, QuatroID, CreateQuatroOrder, OrderItemCode, DistribCode, GenerateCreditNote, OverWriteTax, FreightTaxable, ScheduledOrderExtraDays, Split, EmailOrder, DistributionCenterId, AutoReceive, AutoReceiveDelayMinutes, SuppressOrderDetails, NormalDeliveryTime, PreviousVendorId 
		from tbVendorEntity where VendorEntityId = @VendorEntityId

	-- 2b) Create Entity Items relationship for new store
	IF NOT EXISTS  (select entityId from tbEntityItem where ItemId = @OpenItemId1 and EntityId = @TransferEntityId)
	INSERT INTO tbEntityItem (EntityID, ItemID, InventoryUnitCost, InUse, IntroductionDate, DiscontinueDate, ForecastSimilarItemID, DistributorID, SalesUnitPrice, SpotCheck, Waste, StocktakeSortOrder, VarianceCheck, LastPriceChange, LastPriceChangeUpdatedBy, ApplyPriceChangeToPOS, AverageCost, LastCost, LastPurchaseDate, LastVendor, StandardCost, LastSalesDate, LastSalesReturnDate, LastPurchaseReturnDate, CountTypeDefault, CountTypeSpecific, CostChangeUpdate, SortOrder, ClearProduction, EasyOrderFlag, PerformStockCount, StocktakeUnit1ID, StocktakeUnit2ID, ReportingUnitID, LastAuthoriseOrderQty, LastAuthoriseOrderDate, RestrictedOrder, Transferable, OrderCategoryID, LastTransferDate, LastStockAdjustDate, LastStockCountDate, UPT, UPTAutoCalc, UPTQty, NewItemForCountLocation, AutoStockCount, ItemState, ZeroLineOnDeliveryFlag, AutoOrderTypeID, NewItemDate, SafetyStockNumber, SafetyStockUnit, ReceiveUnitID, Locked, TaxableFlag, TaxEffectiveFrom, LastVendorImportCost, TaxCodeID, TaxDiscontinueFrom, DisableCountStkUnit1, DisableCountStkUnit2, DisableCountStkInvUnit, StocktakeUnit4ID, DisableCountStkUnit4, SafetyStockAutoCalc, CanSample, YieldCorrectionFactor, IsSuggestedItem, IsSuggestedItemByHeadOffice, IsMaintenanceFavorite, DefaultOrderQuantity)
		select @TransferEntityId, ItemID, InventoryUnitCost, InUse, IntroductionDate, DiscontinueDate, ForecastSimilarItemID, DistributorID, SalesUnitPrice, SpotCheck, Waste, StocktakeSortOrder, VarianceCheck, LastPriceChange, LastPriceChangeUpdatedBy, ApplyPriceChangeToPOS, AverageCost, LastCost, LastPurchaseDate, LastVendor, StandardCost, LastSalesDate, LastSalesReturnDate, LastPurchaseReturnDate, CountTypeDefault, CountTypeSpecific, CostChangeUpdate, SortOrder, ClearProduction, EasyOrderFlag, PerformStockCount, StocktakeUnit1ID, StocktakeUnit2ID, ReportingUnitID, LastAuthoriseOrderQty, LastAuthoriseOrderDate, RestrictedOrder, Transferable, OrderCategoryID, LastTransferDate, LastStockAdjustDate, LastStockCountDate, UPT, UPTAutoCalc, UPTQty, NewItemForCountLocation, AutoStockCount, ItemState, ZeroLineOnDeliveryFlag, AutoOrderTypeID, NewItemDate, SafetyStockNumber, SafetyStockUnit, ReceiveUnitID, Locked, TaxableFlag, TaxEffectiveFrom, LastVendorImportCost, TaxCodeID, TaxDiscontinueFrom, DisableCountStkUnit1, DisableCountStkUnit2, DisableCountStkInvUnit, StocktakeUnit4ID, DisableCountStkUnit4, SafetyStockAutoCalc, CanSample, YieldCorrectionFactor, IsSuggestedItem, IsSuggestedItemByHeadOffice, IsMaintenanceFavorite, DefaultOrderQuantity
		from tbEntityItem where entityID =  @toEntity and itemId = @OpenItemId1

	IF NOT EXISTS  (select entityId from tbEntityItem where ItemId = @OpenItemId2 and EntityId = @TransferEntityId)
	INSERT INTO tbEntityItem (EntityID, ItemID, InventoryUnitCost, InUse, IntroductionDate, DiscontinueDate, ForecastSimilarItemID, DistributorID, SalesUnitPrice, SpotCheck, Waste, StocktakeSortOrder, VarianceCheck, LastPriceChange, LastPriceChangeUpdatedBy, ApplyPriceChangeToPOS, AverageCost, LastCost, LastPurchaseDate, LastVendor, StandardCost, LastSalesDate, LastSalesReturnDate, LastPurchaseReturnDate, CountTypeDefault, CountTypeSpecific, CostChangeUpdate, SortOrder, ClearProduction, EasyOrderFlag, PerformStockCount, StocktakeUnit1ID, StocktakeUnit2ID, ReportingUnitID, LastAuthoriseOrderQty, LastAuthoriseOrderDate, RestrictedOrder, Transferable, OrderCategoryID, LastTransferDate, LastStockAdjustDate, LastStockCountDate, UPT, UPTAutoCalc, UPTQty, NewItemForCountLocation, AutoStockCount, ItemState, ZeroLineOnDeliveryFlag, AutoOrderTypeID, NewItemDate, SafetyStockNumber, SafetyStockUnit, ReceiveUnitID, Locked, TaxableFlag, TaxEffectiveFrom, LastVendorImportCost, TaxCodeID, TaxDiscontinueFrom, DisableCountStkUnit1, DisableCountStkUnit2, DisableCountStkInvUnit, StocktakeUnit4ID, DisableCountStkUnit4, SafetyStockAutoCalc, CanSample, YieldCorrectionFactor, IsSuggestedItem, IsSuggestedItemByHeadOffice, IsMaintenanceFavorite, DefaultOrderQuantity)
		select @TransferEntityId, ItemID, InventoryUnitCost, InUse, IntroductionDate, DiscontinueDate, ForecastSimilarItemID, DistributorID, SalesUnitPrice, SpotCheck, Waste, StocktakeSortOrder, VarianceCheck, LastPriceChange, LastPriceChangeUpdatedBy, ApplyPriceChangeToPOS, AverageCost, LastCost, LastPurchaseDate, LastVendor, StandardCost, LastSalesDate, LastSalesReturnDate, LastPurchaseReturnDate, CountTypeDefault, CountTypeSpecific, CostChangeUpdate, SortOrder, ClearProduction, EasyOrderFlag, PerformStockCount, StocktakeUnit1ID, StocktakeUnit2ID, ReportingUnitID, LastAuthoriseOrderQty, LastAuthoriseOrderDate, RestrictedOrder, Transferable, OrderCategoryID, LastTransferDate, LastStockAdjustDate, LastStockCountDate, UPT, UPTAutoCalc, UPTQty, NewItemForCountLocation, AutoStockCount, ItemState, ZeroLineOnDeliveryFlag, AutoOrderTypeID, NewItemDate, SafetyStockNumber, SafetyStockUnit, ReceiveUnitID, Locked, TaxableFlag, TaxEffectiveFrom, LastVendorImportCost, TaxCodeID, TaxDiscontinueFrom, DisableCountStkUnit1, DisableCountStkUnit2, DisableCountStkInvUnit, StocktakeUnit4ID, DisableCountStkUnit4, SafetyStockAutoCalc, CanSample, YieldCorrectionFactor, IsSuggestedItem, IsSuggestedItemByHeadOffice, IsMaintenanceFavorite, DefaultOrderQuantity
		from tbEntityItem where entityID =  @toEntity and itemId = @OpenItemId2

	IF NOT EXISTS  (select entityId from tbEntityItem where ItemId = @ReceivedItemId and EntityId = @TransferEntityId)
	INSERT INTO tbEntityItem (EntityID, ItemID, InventoryUnitCost, InUse, IntroductionDate, DiscontinueDate, ForecastSimilarItemID, DistributorID, SalesUnitPrice, SpotCheck, Waste, StocktakeSortOrder, VarianceCheck, LastPriceChange, LastPriceChangeUpdatedBy, ApplyPriceChangeToPOS, AverageCost, LastCost, LastPurchaseDate, LastVendor, StandardCost, LastSalesDate, LastSalesReturnDate, LastPurchaseReturnDate, CountTypeDefault, CountTypeSpecific, CostChangeUpdate, SortOrder, ClearProduction, EasyOrderFlag, PerformStockCount, StocktakeUnit1ID, StocktakeUnit2ID, ReportingUnitID, LastAuthoriseOrderQty, LastAuthoriseOrderDate, RestrictedOrder, Transferable, OrderCategoryID, LastTransferDate, LastStockAdjustDate, LastStockCountDate, UPT, UPTAutoCalc, UPTQty, NewItemForCountLocation, AutoStockCount, ItemState, ZeroLineOnDeliveryFlag, AutoOrderTypeID, NewItemDate, SafetyStockNumber, SafetyStockUnit, ReceiveUnitID, Locked, TaxableFlag, TaxEffectiveFrom, LastVendorImportCost, TaxCodeID, TaxDiscontinueFrom, DisableCountStkUnit1, DisableCountStkUnit2, DisableCountStkInvUnit, StocktakeUnit4ID, DisableCountStkUnit4, SafetyStockAutoCalc, CanSample, YieldCorrectionFactor, IsSuggestedItem, IsSuggestedItemByHeadOffice, IsMaintenanceFavorite, DefaultOrderQuantity)
		select @TransferEntityId, ItemID, InventoryUnitCost, InUse, IntroductionDate, DiscontinueDate, ForecastSimilarItemID, DistributorID, SalesUnitPrice, SpotCheck, Waste, StocktakeSortOrder, VarianceCheck, LastPriceChange, LastPriceChangeUpdatedBy, ApplyPriceChangeToPOS, AverageCost, LastCost, LastPurchaseDate, LastVendor, StandardCost, LastSalesDate, LastSalesReturnDate, LastPurchaseReturnDate, CountTypeDefault, CountTypeSpecific, CostChangeUpdate, SortOrder, ClearProduction, EasyOrderFlag, PerformStockCount, StocktakeUnit1ID, StocktakeUnit2ID, ReportingUnitID, LastAuthoriseOrderQty, LastAuthoriseOrderDate, RestrictedOrder, Transferable, OrderCategoryID, LastTransferDate, LastStockAdjustDate, LastStockCountDate, UPT, UPTAutoCalc, UPTQty, NewItemForCountLocation, AutoStockCount, ItemState, ZeroLineOnDeliveryFlag, AutoOrderTypeID, NewItemDate, SafetyStockNumber, SafetyStockUnit, ReceiveUnitID, Locked, TaxableFlag, TaxEffectiveFrom, LastVendorImportCost, TaxCodeID, TaxDiscontinueFrom, DisableCountStkUnit1, DisableCountStkUnit2, DisableCountStkInvUnit, StocktakeUnit4ID, DisableCountStkUnit4, SafetyStockAutoCalc, CanSample, YieldCorrectionFactor, IsSuggestedItem, IsSuggestedItemByHeadOffice, IsMaintenanceFavorite, DefaultOrderQuantity
		from tbEntityItem where entityID =  @toEntity and itemId = @ReceivedItemId

	-- 2c) Create Vendor Entity Items relationship for new store
	IF NOT EXISTS  (select vendorEntityItemId from tbVendorEntityItem where vendorid = @vendorId and entityId = @TransferEntityId and itemId = @OpenItemId1)
	INSERT INTO tbVendorEntityItem (VendorID, EntityID, ItemID, PurchasePrice, StandardOrderQty, IncludeInStandardOrder, UpdateWithLastPurchasePrice, PurchaseUnit, VendorCode, VendorDescription, OrderRatio, PerformStockCount, StockCountUnit2ID, MinOrderQty, MaxOrderQty, LastPurchaseDate, IntroductionDate, DiscontinueDate, RestrictedOrder, BillingVendorID, BillingVendorCode, LastPurchaseQty, DiscontinueDateOverrideEnabled, DiscontinueDateOverridden, SortOrder)
		select VendorID, @TransferEntityId, ItemID, PurchasePrice, StandardOrderQty, IncludeInStandardOrder, UpdateWithLastPurchasePrice, PurchaseUnit, VendorCode, VendorDescription, OrderRatio, PerformStockCount, StockCountUnit2ID, MinOrderQty, MaxOrderQty, LastPurchaseDate, IntroductionDate, DiscontinueDate, RestrictedOrder, BillingVendorID, BillingVendorCode, LastPurchaseQty, DiscontinueDateOverrideEnabled, DiscontinueDateOverridden, SortOrder
		from tbVendorEntityItem where vendorid = @vendorId and entityId = @toEntity and itemId = @OpenItemId1

	IF NOT EXISTS  (select vendorEntityItemId from tbVendorEntityItem where vendorid = @vendorId and entityId = @TransferEntityId and itemId = @OpenItemId2)
	INSERT INTO tbVendorEntityItem (VendorID, EntityID, ItemID, PurchasePrice, StandardOrderQty, IncludeInStandardOrder, UpdateWithLastPurchasePrice, PurchaseUnit, VendorCode, VendorDescription, OrderRatio, PerformStockCount, StockCountUnit2ID, MinOrderQty, MaxOrderQty, LastPurchaseDate, IntroductionDate, DiscontinueDate, RestrictedOrder, BillingVendorID, BillingVendorCode, LastPurchaseQty, DiscontinueDateOverrideEnabled, DiscontinueDateOverridden, SortOrder)
		select VendorID, @TransferEntityId, ItemID, PurchasePrice, StandardOrderQty, IncludeInStandardOrder, UpdateWithLastPurchasePrice, PurchaseUnit, VendorCode, VendorDescription, OrderRatio, PerformStockCount, StockCountUnit2ID, MinOrderQty, MaxOrderQty, LastPurchaseDate, IntroductionDate, DiscontinueDate, RestrictedOrder, BillingVendorID, BillingVendorCode, LastPurchaseQty, DiscontinueDateOverrideEnabled, DiscontinueDateOverridden, SortOrder
		from tbVendorEntityItem where vendorid = @vendorId and entityId = @toEntity and itemId = @OpenItemId2
	
	IF NOT EXISTS  (select vendorEntityItemId from tbVendorEntityItem where vendorid = @vendorId and entityId = @TransferEntityId and itemId = @ReceivedItemId)
	INSERT INTO tbVendorEntityItem (VendorID, EntityID, ItemID, PurchasePrice, StandardOrderQty, IncludeInStandardOrder, UpdateWithLastPurchasePrice, PurchaseUnit, VendorCode, VendorDescription, OrderRatio, PerformStockCount, StockCountUnit2ID, MinOrderQty, MaxOrderQty, LastPurchaseDate, IntroductionDate, DiscontinueDate, RestrictedOrder, BillingVendorID, BillingVendorCode, LastPurchaseQty, DiscontinueDateOverrideEnabled, DiscontinueDateOverridden, SortOrder)
		select VendorID, @TransferEntityId, ItemID, PurchasePrice, StandardOrderQty, IncludeInStandardOrder, UpdateWithLastPurchasePrice, PurchaseUnit, VendorCode, VendorDescription, OrderRatio, PerformStockCount, StockCountUnit2ID, MinOrderQty, MaxOrderQty, LastPurchaseDate, IntroductionDate, DiscontinueDate, RestrictedOrder, BillingVendorID, BillingVendorCode, LastPurchaseQty, DiscontinueDateOverrideEnabled, DiscontinueDateOverridden, SortOrder
		from tbVendorEntityItem where vendorid = @vendorId and entityId = @toEntity and itemId = @ReceivedItemId
	
	-- 2d) Create item quantity record for new store
	IF NOT EXISTS  (select ItemID from tbItemQty where EntityId = @TransferEntityId and itemId = @OpenItemId1)
	INSERT INTO tbItemQty (ItemID, EntityID, iStatus_typ, sBin_num, MinLevel_qty, MaxLevel_qty, ReorderLevel_qty, fOnOrder_qty, fOnHand_qty, fCommToOrder_qty, fCommToInvoice_qty, fCommToDM_qty, fCommToTransfer_qty, fInTransferOut_qty, fInTransferIn_qty, fAvailable_qty, iLastPhysInv_dt, mOrderCost_amt, mCarryCost_amt, sComment, dtLastUpdate_dt, sLastUpdate_id, VendorShippedQty, BuildToLevel_qty)
		select ItemID, @TransferEntityId, iStatus_typ, sBin_num, MinLevel_qty, MaxLevel_qty, ReorderLevel_qty, fOnOrder_qty, fOnHand_qty, fCommToOrder_qty, fCommToInvoice_qty, fCommToDM_qty, fCommToTransfer_qty, fInTransferOut_qty, fInTransferIn_qty, fAvailable_qty, iLastPhysInv_dt, mOrderCost_amt, mCarryCost_amt, sComment, dtLastUpdate_dt, sLastUpdate_id, VendorShippedQty, BuildToLevel_qty 
		from tbItemQty where entityId = @toEntity and itemId = @OpenItemId1
	
	IF NOT EXISTS  (select ItemID from tbItemQty where EntityId = @TransferEntityId and itemId = @OpenItemId1)
	INSERT INTO tbItemQty (ItemID, EntityID, iStatus_typ, sBin_num, MinLevel_qty, MaxLevel_qty, ReorderLevel_qty, fOnOrder_qty, fOnHand_qty, fCommToOrder_qty, fCommToInvoice_qty, fCommToDM_qty, fCommToTransfer_qty, fInTransferOut_qty, fInTransferIn_qty, fAvailable_qty, iLastPhysInv_dt, mOrderCost_amt, mCarryCost_amt, sComment, dtLastUpdate_dt, sLastUpdate_id, VendorShippedQty, BuildToLevel_qty)
		select ItemID, @TransferEntityId, iStatus_typ, sBin_num, MinLevel_qty, MaxLevel_qty, ReorderLevel_qty, fOnOrder_qty, fOnHand_qty, fCommToOrder_qty, fCommToInvoice_qty, fCommToDM_qty, fCommToTransfer_qty, fInTransferOut_qty, fInTransferIn_qty, fAvailable_qty, iLastPhysInv_dt, mOrderCost_amt, mCarryCost_amt, sComment, dtLastUpdate_dt, sLastUpdate_id, VendorShippedQty, BuildToLevel_qty 
		from tbItemQty where entityId = @toEntity and itemId = @OpenItemId2

	IF NOT EXISTS  (select ItemID from tbItemQty where EntityId = @TransferEntityId and itemId = @OpenItemId1)
	INSERT INTO tbItemQty (ItemID, EntityID, iStatus_typ, sBin_num, MinLevel_qty, MaxLevel_qty, ReorderLevel_qty, fOnOrder_qty, fOnHand_qty, fCommToOrder_qty, fCommToInvoice_qty, fCommToDM_qty, fCommToTransfer_qty, fInTransferOut_qty, fInTransferIn_qty, fAvailable_qty, iLastPhysInv_dt, mOrderCost_amt, mCarryCost_amt, sComment, dtLastUpdate_dt, sLastUpdate_id, VendorShippedQty, BuildToLevel_qty)
		select ItemID, @TransferEntityId, iStatus_typ, sBin_num, MinLevel_qty, MaxLevel_qty, ReorderLevel_qty, fOnOrder_qty, fOnHand_qty, fCommToOrder_qty, fCommToInvoice_qty, fCommToDM_qty, fCommToTransfer_qty, fInTransferOut_qty, fInTransferIn_qty, fAvailable_qty, iLastPhysInv_dt, mOrderCost_amt, mCarryCost_amt, sComment, dtLastUpdate_dt, sLastUpdate_id, VendorShippedQty, BuildToLevel_qty 
		from tbItemQty where entityId = @toEntity and itemId = @ReceivedItemId
 
 
	DECLARE @ReceivedBy varchar(30)  = ''	
    WHILE @@FETCH_STATUS = 0  
    BEGIN  

		SET @ReceivedBy = Concat(@NameBase, (select Substring(Entity, Len(Entity), 1) from tbEntity where entityid = @ToEntity))

		-- 3a) Create Received Transfer header record for Nuggets
		INSERT INTO tbDeliveryCheck values (1, 1, @ReceivedTransferDate, @ReceivedBy)
		
		DECLARE @deliveryCheckId bigint = (select @@Identity)
		
		INSERT INTO tbTransactionTransfer  (TransferFromEntityID, TransferToEntityID, TransferStatus,  TransferDate,		  ReceiveDate,			 InitiatingEntityId, CreateDate,			TransferedBy, ReceivedBy,	TransferStoreDateTime,	ReceiveStoreDateTime,	TransferReasonId,  RequestDate,			  RequestedBy,  DeliveryCheckId)
									VALUES (@TransferEntityId,	  @toEntity,		  @ReceivedStatus, @ReceivedTransferDate, @ReceivedTransferDate, @TransferEntityId,	 @ReceivedTransferDate,	@InitiatedBy, @ReceivedBy,	@ReceivedTransferDate,	@ReceivedTransferDate,	@transferReasonId, @ReceivedTransferDate, @ReceivedBy,	@deliveryCheckId)

		SET @transferId = (select @@Identity)

		-- 3b) Create Received Transfer detail record for Nuggets
		INSERT INTO tbTransactionTransferDetail  (TransactionTransferID, ItemID, TransferQty, TransferCost, TransferUnit1, TransferUnit2, TransferUnit3, TransferQty1, TransferQty2, TransferQty3, TransferUnitCost, Code, Description, VendorID, TransferUnit4, TransferQty4, OriginalTransferQty1, OriginalTransferQty2, OriginalTransferQty3, OriginalTransferQty4, ReasonCodeId)
									VALUES (@transferId, @ReceivedItemId, 36, 102.7692,	'36 Lb Case', '6 Lb Bag', 'Pound', 1, 0, 0, 2.8547, '70', 'Chicken, Nuggets', 0, '', 0, 1, 0, 0, 0, @TransferReasonId)
		
		-- 4a) Create Open Transfer header record for Filets
		INSERT INTO tbTransactionTransfer  (TransferFromEntityID, TransferToEntityID, TransferStatus, TransferDate,		 ReceiveDate, InitiatingEntityId, CreateDate,		 TransferedBy, ReceivedBy, TransferStoreDateTime, ReceiveStoreDateTime, TransferReasonId,  RequestDate, RequestedBy)
									VALUES (@TransferEntityId,	  @toEntity,		  @OpenStatus,	  @OpenTransferDate, NULL,		  @TransferEntityId,  @OpenTransferDate, @InitiatedBy, NULL,	   @OpenTransferDate,	  NULL,					@transferReasonId, NULL,		@ReceivedBy)

		SET @transferId = (select @@Identity)

		-- 4b) Create Open Transfer detail record for Filets
		INSERT INTO tbTransactionTransferDetail  (TransactionTransferID, ItemID, TransferQty, TransferCost, TransferUnit1, TransferUnit2, TransferUnit3, TransferQty1, TransferQty2, TransferQty3, TransferUnitCost, Code, Description, VendorID, TransferUnit4, TransferQty4, OriginalTransferQty1, OriginalTransferQty2, OriginalTransferQty3, OriginalTransferQty4, ReasonCodeId)
									VALUES (@transferId, @OpenItemId1, 36, 100.5516, '36 Lb Case', '6 Lb Bag', 'Pound', 1, 0, 0, 2.7931, '1', 'Chicken, Filets', 0, '', 0, 1, 0, 0, 0, NULL)
		
		-- 4b) Create Open Transfer detail record for Waffles
		INSERT INTO tbTransactionTransferDetail  (TransactionTransferID, ItemID, TransferQty, TransferCost, TransferUnit1, TransferUnit2, TransferUnit3, TransferQty1, TransferQty2, TransferQty3, TransferUnitCost, Code, Description, VendorID, TransferUnit4, TransferQty4, OriginalTransferQty1, OriginalTransferQty2, OriginalTransferQty3, OriginalTransferQty4, ReasonCodeId)
									VALUES (@transferId, @OpenItemId2, 20, 12.934, '6/5 lb Bag Case', '5 Lb Bag', 'Pound', 0, 4, 0, 0.6467, '1241', 'Potato, Waffle Fries', 0, '', 0, 0, 4, 0, 0, NULL)

		FETCH NEXT FROM toEntity_cursor INTO @ToEntity  
    END  
  
    CLOSE toEntity_cursor  
    DEALLOCATE toEntity_cursor  

END

--If you want to change the date on the Received transfer:
--update tbTransactionTransfer set TransferReasonId = 3 
--update tbTransactionTransfer set TransferReasonId = 3, CreateDate = '2020-01-15 12:25:33.000' where transferstatus = 'Received'
--If you want to change the date on the Open transfer:
--update tbTransactionTransfer set CreateDate = '2020-01-16 10:25:33.000' where transferstatus = 'Transfered'

--select * from tbTransactionTransfer
--select * from tbTransactionTransferDetail

/*
-- RESET - Remove all transfers and delete Transfer store
declare @TransferEntityId bigint = (select entityid from tbEntity where entityTypeId = 4 and Entity like 'Transfer%')
delete from tbItemQty where entityid in (@TransferEntityId)
delete from tbEntityItemDailyStockBalance where entityid in (@TransferEntityId)
delete from tbEntityItemCost where entityid in (@TransferEntityId)
delete from tbVendorEntity where entityid in (@TransferEntityId)
delete from tbVendorEntityItem where entityid in (@TransferEntityId)
delete from tbEntityItem where entityid in (@TransferEntityId)
delete from tbEntityDailyStockBalance where entityid in (@TransferEntityId)
delete from tbEntity where entityid in (@TransferEntityId)
delete from tbTransactionTransfer
delete from tbTransactionTransferDetail

-- Verify that RESET worked
declare @ConsultantEntityId bigint = (select entityid from tbEntity where entityTypeId = 4 and Entity like 'Transfer%')
select * from tbVendorEntityItem where entityid = @ConsultantEntityId
select * from tbVendorEntity where entityid = @ConsultantEntityId
select * from tbEntityItem where entityid = @ConsultantEntityId
select * from tbItemQty where entityid = @ConsultantEntityId
select * from tbEntity where entitytypeid = 4 and entityid = @ConsultantEntityId
select * from tbEntityItemCost where entityid = @ConsultantEntityId
select * from tbEntityItemDailyStockBalance where entityid = @ConsultantEntityId
select * from tbTransactionTransfer
select * from tbTransactionTransferDetail
*/


--commit
--rollback
