BEGIN TRANSACTION

CREATE TABLE [dba].[dbo].[tbItemBalance_CFAMX12456](
	[ItemID] [bigint] NOT NULL,
	[EntityID] [bigint] NOT NULL,
	[FiscalYear] [varchar](10) NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[EndDate] [datetime] NOT NULL,
	[PeriodNo] [int] NULL,
	[COGS_amt] [money] NULL,
	[Sales_amt] [money] NULL,
	[Sales_qty] [float] NULL,
	[SalesReturn_amt] [money] NULL,
	[SalesReturn_qty] [float] NULL,
	[Purchase_amt] [money] NULL,
	[Purchase_qty] [float] NULL,
	[PurchaseReturn_amt] [money] NULL,
	[PurchaseReturn_qty] [float] NULL,
	[Spoilage_amt] [money] NULL,
	[Spoilage_qty] [float] NULL,
	[Adjust_amt] [money] NULL,
	[Adjust_qty] [float] NULL,
	[TransferOut_amt] [money] NULL,
	[TransferOut_qty] [float] NULL,
	[TransferIn_amt] [money] NULL,
	[TransferIn_qty] [float] NULL,
	[Inventory_amt] [money] NULL,
	[Inventory_qty] [float] NULL,
	[LastAdjustDate] [datetime] NULL,
	[InternalUse_amt] [money] NULL,
	[InternalUse_qty] [float] NULL
) ON [PRIMARY]


INSERT INTO [dba].[dbo].[tbItemBalance_CFAMX12456] (
	[ItemID], [EntityID], [FiscalYear], [StartDate], [EndDate], [PeriodNo], [COGS_amt], [Sales_amt], [Sales_qty], [SalesReturn_amt], [SalesReturn_qty], 
	[Purchase_amt], [Purchase_qty], [PurchaseReturn_amt], [PurchaseReturn_qty], [Spoilage_amt], [Spoilage_qty], [Adjust_amt], [Adjust_qty], [TransferOut_amt], 
	[TransferOut_qty], [TransferIn_amt], [TransferIn_qty], [Inventory_amt], [Inventory_qty], [LastAdjustDate], [InternalUse_amt], [InternalUse_qty]
)
SELECT [ItemID], [EntityID], [FiscalYear], [StartDate], [EndDate], [PeriodNo], [COGS_amt], [Sales_amt], [Sales_qty], [SalesReturn_amt], [SalesReturn_qty], 
	[Purchase_amt], [Purchase_qty], [PurchaseReturn_amt], [PurchaseReturn_qty], [Spoilage_amt], [Spoilage_qty], [Adjust_amt], [Adjust_qty], [TransferOut_amt], 
	[TransferOut_qty], [TransferIn_amt], [TransferIn_qty], [Inventory_amt], [Inventory_qty], [LastAdjustDate], [InternalUse_amt], [InternalUse_qty]
FROM [dbo].[tbItemBalance] WITH(NOLOCK)
WHERE EntityId = 1050 and StartDate = '2020-12-21 00:00:00.000'


DELETE FROM [dbo].[tbItemBalance] WHERE EntityId = 1050 and StartDate = '2020-12-21 00:00:00.000'

UPDATE IQ SET [fOnHand_qty] = IB.[Inventory_qty]
		FROM [dbo].[tbItemQty] IQ WITH(NOLOCK)
		INNER JOIN [dbo].[tbItemBalance] IB WITH(NOLOCK)
			ON IB.[EntityID] = 1050 AND IB.[ItemID] = IQ.[ItemID]
			AND IB.[FiscalYear] = 2020
			AND IB.[StartDate] = '11/2/2020'
		WHERE
			IQ.[EntityID] = 1050
			AND IQ.[fOnHand_qty] <> IB.[Inventory_qty]

--SELECT * FROM [dba].[dbo].[tbItemBalance_CFAMX12456]
--COMMIT TRANSACTION
--ROLLBACK TRANSACTION
GO