USE [powerpos]
GO
/****** Object:  StoredProcedure [dbo].[sp_beg_inventory_date]    Script Date: 01/23/2026 9:05:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DROP PROCEDURE sp_beg_inventory_date
go

--exec sp_insert_begbal_todate '1/4/2026','PASIG001','PASIG001'
CREATE PROCEDURE [dbo].[sp_beg_inventory_date]
    @itemcode   VARCHAR(50) = NULL,
    @trans_datefrom DATETIME,
    @trans_dateto DATETIME,
    @store_id   VARCHAR(50) = NULL
	-- Add the parameters for the stored procedure here
AS
BEGIN

create table #beg_inv(trans_date datetime,itemcode varchar(50),itemname varchar(150),ending_balance decimal(18,2),beg_qty decimal(18,2),sales decimal(18,2),delivery decimal(18,2),pullout decimal(18,2),transfer_in decimal(18,2)
,transfer_out decimal(18,2),adjustments decimal(18,2))


 SET NOCOUNT ON;

    INSERT INTO #beg_inv 
    (
        trans_date,
        itemcode,
        itemname,
        ending_balance,
        beg_qty,
        sales,
        delivery,
        pullout,
        transfer_in,
        transfer_out,
        adjustments

    )

    SELECT trans_date,itemcode,itemname,
    beg_qty,
    beg_qty,
    isnull((select sum(qty) from sales where sales.sales_date = b.trans_date AND (@store_id IS NULL OR store_id = @store_id) and sales.itemcode = b.itemcode),0) as sales,
    isnull((select sum(qty2) from delivery where delivery.delivery_date = b.trans_date AND (@store_id IS NULL OR store_id = @store_id) and delivery.itemcode = b.itemcode and delivery.dr_type = 'RECEIVING'),0) as delivery,
    isnull((select sum(qty) from pullout where pullout.pullout_date = b.trans_date AND (@store_id IS NULL OR store_id = @store_id) and pullout.itemcode = b.itemcode),0) as pullout,
    isnull((select sum(qty) from stock_transfers where stock_transfers.trans_date = b.trans_date AND (@store_id IS NULL OR store_id = @store_id) and stock_transfers.itemcode = b.itemcode and transfer_flag = '1'),0) as transfer_in,
    isnull((select sum(qty) from stock_transfers where stock_transfers.trans_date = b.trans_date AND (@store_id IS NULL OR store_id = @store_id) and stock_transfers.itemcode = b.itemcode and transfer_flag = '0'),0) as transfer_out,
    isnull((select sum(qty) from inventory_adjustment where inventory_adjustment.trans_date = b.trans_date AND (@store_id IS NULL OR store_id = @store_id) and inventory_adjustment.itemcode = b.itemcode),0) as adjustments
    FROM beg_inventory b
    WHERE (@itemcode IS NULL OR itemcode = @itemcode)
      AND trans_date between @trans_datefrom and @trans_dateto
      AND (@store_id IS NULL OR store_id = @store_id)
    ORDER BY trans_date ASC;

    update #beg_inv set beg_qty = (beg_qty - (delivery + transfer_in + adjustments) + (sales + pullout + transfer_out))

    select * from #beg_inv order by trans_date ASC
END