-- Check Inventory Tables
Select * from warehouse_inventory_header where org_id ='KYMMSENG' order by txn_date desc
Select * from warehouse_inventory_detail where warehouse_inventory_hdr_id = 'KSWHGS0001-SUNTORY-20240606'
Select * from mst_warehouse_item wi where wi.warehouse_id ='0086d5d6-b9f1-44ba-a35c-1051b438ebf3'

-- Patch inventory -- (Note: warehouse_inventory_detail is stock UOM)
	--Update warehouse_inventory_detail set
	--opening_gs = x.closing_gs,
	--reserved_gs = x.reserved_gs,
	--closing_gs = x.closing_gs,
	--opening_bs = x.closing_bs,
	--closing_bs = x.closing_bs,
	--updated_by = 'System', updated_date = getdate()
	--from (
	--Select * from warehouse_inventory_detail where warehouse_inventory_hdr_id ='KSWHGS0001-SUNTORY-20240605'
	--) x
	--where warehouse_inventory_detail.warehouse_inventory_hdr_id ='KSWHGS0001-SUNTORY-20240606' and warehouse_inventory_detail.item_id = x.item_id and x.uom_id = warehouse_inventory_detail.uom_id

-- Patch closing stock of yesterday's inventory --
	--Update warehouse_inventory_detail set closing_gs = y.closing_gs, closing_bs = y.closing_bs from (
	--Select dd.warehouse_inventory_dtl_id, dd.item_id, dd.warehouse_inventory_hdr_id,  x.closing_gs, x.closing_bs from warehouse_inventory_detail dd 
	--inner join (
	--Select warehouse_inventory_dtl_id, warehouse_inventory_hdr_id, item_id, uom_id, opening_gs, stock_in_gs,
	--stock_out_gs, reserved_gs, (opening_gs + stock_in_gs - stock_out_gs) [closing_gs] , opening_bs, stock_in_bs, stock_out_bs, (opening_bs + stock_in_bs - stock_out_bs) [closing_bs]
	--from warehouse_inventory_detail where warehouse_inventory_hdr_id ='KSWHGS0001-SUNTORY-20240605' 

	--) x on x.warehouse_inventory_dtl_id = dd.warehouse_inventory_dtl_id 
	--where dd.closing_gs <> x.closing_gs or dd.closing_bs <> x.closing_bs
	--) y where y.warehouse_inventory_dtl_id = warehouse_inventory_detail.warehouse_inventory_dtl_id and y.warehouse_inventory_hdr_id = warehouse_inventory_detail.warehouse_inventory_hdr_id and y.item_id = warehouse_inventory_detail.item_id and warehouse_inventory_detail.warehouse_inventory_hdr_id = 'KSWHGS0001-SUNTORY-20240605' 


-- Insert into warehouse_inventory_detail where not exists --
	--insert into warehouse_inventory_detail (warehouse_inventory_dtl_id, warehouse_inventory_hdr_id, item_id, uom_id, opening_gs, stock_in_gs, stock_out_gs, reserved_gs, closing_gs, opening_bs, stock_in_bs, stock_out_bs, closing_bs, status, created_date, created_by, updated_date ,updated_by, tenant_id, org_id)
	--Select newid(), 'KSWHGS0001-SUNTORY-20240606', item_id, uom_id, closing_gs, 0, 0, reserved_gs, closing_gs, closing_bs, 0, 0, closing_bs, status, GEtDAte(), 'System', GetDate() ,'System', tenant_id, org_id
	--from warehouse_inventory_detail dd where warehouse_inventory_hdr_id ='KSWHGS0001-SUNTORY-20240605' 
	--and not exists (Select 1 from warehouse_inventory_detail d where d.warehouse_inventory_hdr_id = 'KSWHGS0001-SUNTORY-20240606' and d.item_id = dd.item_id)


	-- Check mst_warehouse_item --
	Select * from mst_warehouse_item wi
	inner join warehouse_inventory_detail d on d.item_id = wi.item_id
	where wi.warehouse_id ='0086d5d6-b9f1-44ba-a35c-1051b438ebf3'  and d.warehouse_inventory_hdr_id ='KSWHGS0001-SUNTORY-20240606'

-- Check duplication in mst_warehouse_item
	Select * from mst_warehouse_item w where exists (
	Select 
	item_id from mst_warehouse_item wi where wi.warehouse_id ='0086d5d6-b9f1-44ba-a35c-1051b438ebf3'  
	and w.item_id = wi.item_id
	group by item_id having count(1) > 1
	)
	and w.warehouse_id ='0086d5d6-b9f1-44ba-a35c-1051b438ebf3'  
	order by item_id

-- Delete Duplication in mst_Warehouse_item
	--delete from mst_warehouse_item where tenant_id = 'KYMMSENG' and warehouse_item_id in (
	Select warehouse_item_id from mst_warehouse_item w where exists (
	Select 
	item_id from mst_warehouse_item wi where wi.warehouse_id ='0086d5d6-b9f1-44ba-a35c-1051b438ebf3'  
	and w.item_id = wi.item_id
	group by item_id having count(1) > 1
	)
	and w.warehouse_id ='0086d5d6-b9f1-44ba-a35c-1051b438ebf3' and tenant_id ='KYMMSENG'
	--)

-- Update quantity in mst_warehouse_item (quantity in lowest uom ) --
	--Update mst_warehouse_item set
	--quantity_available = x.quantity
	--from (
	--Select wi.warehouse_item_id, wi.item_id, quantity_available, wi.warehouse_id, d.closing_gs, p.cf_oth_uom, d.closing_gs * p.cf_oth_uom [quantity] from mst_warehouse_item wi
	--inner join warehouse_inventory_detail d on d.item_id = wi.item_id
	--inner join product_item p on p.item_id = d.item_id 
	--where wi.warehouse_id ='0086d5d6-b9f1-44ba-a35c-1051b438ebf3'  and d.warehouse_inventory_hdr_id ='KSWHGS0001-SUNTORY-20240606'
	--and Cast(d.closing_gs * p.cf_oth_uom as decimal(18,2)) <> wi.quantity_available

	--) x
	--where mst_warehouse_item.item_id = x.item_id and mst_warehouse_item.warehouse_item_id = x.warehouse_item_id