USE [powerpos]
GO
/****** Object:  StoredProcedure [dbo].[sp_update_stock_inventory]    Script Date: 01/23/2026 2:20:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_update_stock_inventory] 
as
BEGIN

--reset balances
update items
set qty_in =0,qty_out=0,sales_return=0,delivery=0,pullout=0,end_qty=0,wip=0,adjustment=0
--END_QTY_TEMP (INILAGAY PARA MAGING REFERENCE SA ASSEMBLY_QTY)
,END_QTY_TEMP=0,transfer_in=0,transfer_out=0

update items set qty_out = (select isnull(sum(qty2),0) from [zankpos].[dbo].[sales] where items.itemcode = sales.itemcode and sales.posted = 0
-- and ref_no is null or ref_no = ''
and [zankpos].[dbo].[sales].sales_type = 'CS' and s_type = '0'
-- 2022-05-05 (ADDED PARA DI KASAMA YUNG MGA INVALID TRANSACTIONS)
and official_receipt is not null
--
and qty > 0) 
from items, [zankpos].[dbo].[sales]
where items.itemcode = [zankpos].[dbo].[sales].itemcode
and items.item_type = 'I'

update items
set qty_out = isnull(qty_out,0) + (select isnull(sum(sales_detail.sub_total_qty),0)
from sales_detail 
where items.itemcode = sales_detail.sub_itemcode 
and sales_detail.qty > 0
and sales_detail.posted = 0
and items.item_type = 'I')

update items
set qty_out = isnull(qty_out,0) + (select isnull(sum(sales_packages.sub_total_qty),0)
from sales_packages
where items.itemcode = sales_packages.sub_itemcode 
and sales_packages.qty > 0
and sales_packages.posted = 0
and items.item_type = 'I')

-- original
update items
set qty_out = isnull(qty_out,0) + (select isnull(sum(a.sub_total_qty),0)
from sales_detail a,  items_ingredients b
where b.sub_itemcode = items.itemcode
and a.sub_itemcode = b.sub_itemcode
and a.qty > 0
and a.posted = 0)
+
(select isnull(sum(a.sub_total_qty),0)
from sales_packages a,  items_packages b
where b.sub_itemcode = items.itemcode
and a.sub_itemcode = b.sub_itemcode
and a.qty > 0
and a.posted = 0)

- 

(select isnull(sum(a.qty),0)
from sales_packages a,  items_packages b
where b.sub_itemcode = items.itemcode
and a.sub_itemcode = b.sub_itemcode
and a.qty > 0
and a.posted = 0)


---
--(select isnull(sum(qty2),0) from sales where items.itemcode = sales.itemcode and sales.posted = 0
--and sales.sales_type = 'CS' and qty > 0 and ref_no > 0)

where  items.item_type='I'

update items set qty_out = isnull(qty_out,0) + (select isnull(sum(qty),0) from [zankpos].[dbo].[sales] where items.itemcode = sales.itemcode and sales.posted = 0
-- and ref_no is null or ref_no = ''
and [zankpos].[dbo].[sales].sales_type = 'CS'  and s_type = '1'
-- 2022-05-05 (ADDED PARA DI KASAMA YUNG MGA INVALID TRANSACTIONS)
and official_receipt is not null
and qty > 0) 
from items, [zankpos].[dbo].[sales]
where items.itemcode = [zankpos].[dbo].[sales].itemcode
and items.item_type = 'I'

update items set delivery = (select isnull(sum(qty),0) from delivery where items.itemcode = delivery.itemcode and delivery.posted = 0)
from items, delivery
where items.itemcode = delivery.itemcode
and items.item_type = 'I'


update items set pullout = (select isnull(sum(qty),0) from pullout where items.itemcode = pullout.itemcode and pullout.posted = 0)
from items, pullout
where items.itemcode = pullout.itemcode
and items.item_type = 'I'

update items set sales_return = (select isnull(sum(qty2),0) * -1 from sales
where items.itemcode = sales.itemcode
and sales.posted = 0
-- 2022-05-05 (ADDED PARA DI KASAMA YUNG MGA INVALID TRANSACTIONS)
and official_receipt is not null
--
and sales.sales_type = 'SR')
from items, sales
where items.itemcode = sales.itemcode
and items.item_type = 'I'

--2021-11-29 REVISION: FIX CHARGE AND INVOICING RETURN ITEM
update items set sales_return = items.sales_return -
(select isnull(sum(qty2),0) from sales 
where items.itemcode = sales.itemcode
and sales.posted = 0
and sales.trans_type IN ('INVOICING', 'CHARGE')
-- 2022-05-05 (ADDED PARA DI KASAMA YUNG MGA INVALID TRANSACTIONS)
and official_receipt is not null
--
and sales.sales_type = 'CS'
and sales. qty2 < 0)
from items, sales
where items.itemcode = sales.itemcode
and items.item_type = 'I'


update items set wip = (select isnull(sum(from_qty),0) from inventory_usage where items.itemcode = inventory_usage.from_itemcode and inventory_usage.posted = 0)
from items, inventory_usage
where items.itemcode = inventory_usage.from_itemcode
and items.item_type = 'I'

update items set qty_in = (select isnull(sum(to_qty),0) from inventory_usage where items.itemcode = inventory_usage.to_itemcode and inventory_usage.posted = 0)
from items, inventory_usage
where items.itemcode = inventory_usage.to_itemcode
and items.item_type = 'I'

update items set adjustment = (select isnull(sum(qty),0) from inventory_adjustment where items.itemcode = inventory_adjustment.itemcode and inventory_adjustment.posted = 0)
from items, inventory_adjustment
where items.itemcode = inventory_adjustment.itemcode
and items.item_type = 'I'

update items set adj_module = (select isnull(sum(qty),0) from adjustment_table where items.itemcode = adjustment_table.itemcode and adjustment_table.posted = 0)
from items, adjustment_table
where items.itemcode = adjustment_table.itemcode
and items.item_type = 'I'




update items set transfer_in = (select isnull(sum(qty),0) from stock_Transfers 
							where items.itemcode = stock_Transfers.itemcode and stock_Transfers.posted = 0
							and transfer_flag = 1)
from items, stock_Transfers
where items.itemcode = stock_Transfers.itemcode
and items.item_type = 'I'


update items set transfer_out = (select isnull(sum(qty),0) from stock_Transfers 
							where items.itemcode = stock_Transfers.itemcode and stock_Transfers.posted = 0
							and transfer_flag = 0)
from items, stock_Transfers
where items.itemcode = stock_Transfers.itemcode
and items.item_type = 'I'


update items set end_qty = (isnull(beg_qty,0) + isnull(delivery,0) + isnull(sales_return,0) + isnull(qty_in,0) + isnull(adjustment,0) + isnull(adj_module,0) + isnull(transfer_in,0)) - (isnull(qty_out,0) + isnull(pullout,0) + isnull(wip,0) + isnull(transfer_out,0))
where items.item_type = 'I'

--END_QTY_TEMP (INILAGAY PARA MAGING REFERENCE SA ASSEMBLY_QTY)
--end_qty_temp = (isnull(beg_qty,0) + isnull(delivery,0) + isnull(sales_return,0) + isnull(qty_in,0) + isnull(adjustment,0) + isnull(adj_module,0) + isnull(transfer_in,0)) - (isnull(qty_out,0) + isnull(pullout,0) + isnull(wip,0) + isnull(transfer_out,0))
--where items.item_type = 'I'

--DECLARE	@return_value int,
--		@assembly_id int

--SELECT	@assembly_id = 1

--EXEC	@return_value = [dbo].[sp_update_assembly]
--		@assembly_id = @assembly_id OUTPUT

--SELECT	@assembly_id as N'@assembly_id'

--SELECT	'Return Value' = @return_value

END