92 lines
1.7 KiB
Transact-SQL
92 lines
1.7 KiB
Transact-SQL
USE POWERPOS
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_insert_begbal_todate] Script Date: 01/21/2026 9:56:36 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
DROP PROCEDURE sp_insert_begbal_todate
|
|
go
|
|
|
|
|
|
--exec sp_insert_begbal_todate '1/4/2026','PASIG001','PASIG001'
|
|
CREATE PROCEDURE [dbo].[sp_insert_begbal_todate]
|
|
-- Add the parameters for the stored procedure here
|
|
@dDate datetime,
|
|
@cStoreid varchar(50),
|
|
@cMachine varchar(50)
|
|
AS
|
|
BEGIN
|
|
|
|
--PASIG001
|
|
--PASIG001
|
|
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
|
|
-- Insert statements for procedure here
|
|
|
|
exec sp_update_stock_inventory
|
|
exec sp_update_stock_inventory_carcass
|
|
|
|
--insert parent items to beg_inventory
|
|
delete from beg_inventory
|
|
where trans_date = @ddate
|
|
|
|
|
|
--trans_type = 1 - for items in items table
|
|
--trans_type = 2 - for items(carcass) in delivery table
|
|
|
|
insert into beg_inventory(
|
|
trans_date,
|
|
itemcode,
|
|
itemname,
|
|
beg_qty, --ending_balance
|
|
machine_id,
|
|
store_id,
|
|
trans_type,
|
|
ref_no)
|
|
select
|
|
@ddate,
|
|
a.itemcode,
|
|
a.itemname,
|
|
isnull(a.end_qty,0),
|
|
@cMachine,
|
|
@cStoreid,
|
|
1,
|
|
''
|
|
from items a
|
|
where isnull(is_parent,0) =0
|
|
and isnull(end_qty,0) >0 and end_qty != isnull((select beg_qty from beg_inventory z where z.store_id = @cStoreid and z.itemcode = a.itemcode and z.posted = 0 and z.trans_date = @dDate),0)
|
|
|
|
--union all
|
|
--select
|
|
--@ddate,
|
|
--a.itemcode,
|
|
--a.itemname,
|
|
--a.end_qty,
|
|
--@cMachine,
|
|
--@cStoreid,
|
|
--2,
|
|
--ref_no
|
|
--from delivery a
|
|
--where a.is_parent =1
|
|
--and dr_type = 'RECEIVING'
|
|
|
|
|
|
|
|
|
|
|
|
END
|
|
GO
|
|
|