USE [wms-skt-demo]
GO
/****** Object:  StoredProcedure [dbo].[sp_confirm_Stock_Taking_table]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_confirm_Stock_Taking_table] 	
	@takingMonth varchar(10),
	@partNo varchar(40),
	@location varchar(20),
	@qty numeric(24, 2),
	@userId varchar(100)
AS

--status info
 -- START
 -- RECORD
 -- CONFIRMED
 -- DELETED

BEGIN
	
	
update Stocktaking
	set confirm_dt = SYSDATETIME(), confirm_user_id = @userId, status = 'CONFIRMED'
	where taking_month = @takingMonth
	and item_id = @partNo and location_id = @location
	and st_qty = @qty


END
GO
/****** Object:  StoredProcedure [dbo].[sp_confirm_stocktaking]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_confirm_stocktaking] 
    -- Add the parameters for the stored procedure here
    @yyyymm VARCHAR(6),
    @user_id VARCHAR(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    UPDATE c 
    SET c.status = 'CONFIRMED',
        c.update_user_id = @user_id,
        c.confirm_user_id = @user_id,
        c.update_dt = GETDATE(),
        c.confirm_dt = GETDATE()
    FROM 
    (
        SELECT b.item_id, b.lot_no, b.location_id, b.serial_no AS kanban_serial, SUM(st_qty) AS sum_st_qty
        FROM
        (
            SELECT item_id, lot_no, location_id, serial_no, MAX(division) AS division
            FROM Stocktaking
            WHERE taking_month = @yyyymm AND status = 'RECORD'
            GROUP BY item_id, lot_no, location_id, serial_no
        ) a
        INNER JOIN Stocktaking b
        ON a.item_id = b.item_id
        AND a.lot_no = b.lot_no
        AND a.serial_no = b.serial_no
        AND a.location_id = b.location_id
        AND a.division = b.division
        AND b.taking_month = @yyyymm
        GROUP BY b.item_id, b.lot_no, b.location_id, b.serial_no
    ) a
    LEFT JOIN
    (
        SELECT item_id, location_id, lot_no, SUM(qty) AS sum_qty, kanban_serial
        FROM Stock
        GROUP BY item_id, location_id, lot_no, kanban_serial
    ) b
    ON a.item_id = b.item_id
    AND a.location_id = b.location_id
    AND a.lot_no = b.lot_no
    AND a.kanban_serial = b.kanban_serial
    AND a.sum_st_qty = b.sum_qty

    INNER JOIN Stocktaking c
    ON c.item_id = b.item_id
    AND c.lot_no = b.lot_no
    AND c.location_id = b.location_id
    AND c.serial_no = b.kanban_serial
    AND c.status = 'RECORD'
END
GO
/****** Object:  StoredProcedure [dbo].[sp_delete_Inspection_Detail]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_Inspection_Detail] 	
	@prod_schd_id bigint,
	@serialNo varchar(10),
	@qtySerial decimal(24, 2),
	@status varchar(20)

AS



BEGIN

	delete from SerialNo where prod_schd_id = @prod_schd_id and serial_no = @serialNo and qty = @qtySerial and [status] = @status
	
	

END
GO
/****** Object:  StoredProcedure [dbo].[sp_delete_Production_Inbound]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_Production_Inbound] 	
	@line varchar(50),
	@FGparts varchar(50),
	@parts_no varchar(50)
AS



BEGIN

	DECLARE @pid NUMERIC

	--set @pid = (select TOP (1) [id] from [ProductionSchedule] where [parts_no] = @FGparts order by id desc)


	delete from ProductionInbound where Line = @line and FGparts = @FGparts and parts_no = @parts_no
	
	
END
GO
/****** Object:  StoredProcedure [dbo].[sp_deleteAll_Inspection_Detail]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_deleteAll_Inspection_Detail] 	
	@prod_schd_id bigint

AS



BEGIN

	delete from SerialNo where prod_schd_id = @prod_schd_id 
	
	

END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_Inbound_Inspection_Part]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_Inbound_Inspection_Part] 
	@dateFrom date,
	@dateTo date
AS
BEGIN

	select parts_no  from ProductionSchedule where action_plan_date >= @dateFrom and action_plan_date <= @dateTo group by parts_no order by parts_no


END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_Inbound_Inspection_table]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_Inbound_Inspection_table] 
	@dateFrom date,
	@dateTo date,
	@part varchar(50),
	@exceptComplete tinyint
AS

if (@part <> '')
BEGIN
	
if (@exceptComplete = 1)

BEGIN

	DECLARE @count NUMERIC

	select ROW_NUMBER() OVER(ORDER BY P.action_plan_date, S.name, P.parts_no ASC) AS PRow, P.action_plan_date as PDate, S.name as SName, 
	P.parts_no as Partno, --P.qty as Qty,
	CONVERT(DECIMAL(10,2), (P.qty / IIF((select count(qty) from [PackingStandard] where [unit_qty] = P.unit_qty and [item_id] = P.parts_no) = 0,
	1,(select qty from [PackingStandard] where [unit_qty] = P.unit_qty and [item_id] = P.parts_no)))) as Qty,
	--ISNULL((select qty from [PackingStandard] where [unit_qty] = P.unit_qty and [item_id] = P.parts_no),1))) as Qty, 
	P.unit_qty as unit_qty, 
	P.status as Status, 
	P.action_act_qty as ActQty, P.id as IdPS 
	from ProductionSchedule as P left join Supplier as S on P.supplier_id = S.id_internal
	where P.action_plan_date >= @dateFrom and P.action_plan_date <= @dateTo and P.parts_no = @part and P.status <> 'Completed'
	order by P.action_plan_date, S.name, P.parts_no
		
END

else 

BEGIN

	select ROW_NUMBER() OVER(ORDER BY P.action_plan_date, S.name, P.parts_no ASC) AS PRow, P.action_plan_date as PDate, S.name as SName, 
	P.parts_no as Partno, --P.qty as Qty,
	--CONVERT(DECIMAL(10,2), (P.qty / (select qty from [PackingStandard] where [unit_qty] = P.unit_qty and [item_id] = P.parts_no))) as Qty, 
	CONVERT(DECIMAL(10,2), (P.qty / IIF((select count(qty) from [PackingStandard] where [unit_qty] = P.unit_qty and [item_id] = P.parts_no) = 0,
	1,(select qty from [PackingStandard] where [unit_qty] = P.unit_qty and [item_id] = P.parts_no)))) as Qty,
	P.unit_qty as unit_qty, 
	P.status as Status , 
	P.action_act_qty as ActQty, P.id as IdPS
	from ProductionSchedule as P left join Supplier as S on P.supplier_id = S.id_internal
	where P.action_plan_date >= @dateFrom and P.action_plan_date <= @dateTo and P.parts_no = @part 
	order by P.action_plan_date, S.name, P.parts_no

END

END

else

BEGIN

if (@exceptComplete = 1)

BEGIN

	select ROW_NUMBER() OVER(ORDER BY P.action_plan_date, S.name, P.parts_no ASC) AS PRow, P.action_plan_date as PDate, S.name as SName, 
	P.parts_no as Partno, --P.qty as Qty,
	--CONVERT(DECIMAL(10,2), (P.qty / (select qty from [PackingStandard] where [unit_qty] = P.unit_qty and [item_id] = P.parts_no))) as Qty, 
	CONVERT(DECIMAL(10,2), (P.qty / IIF((select count(qty) from [PackingStandard] where [unit_qty] = P.unit_qty and [item_id] = P.parts_no) = 0,
	1,(select qty from [PackingStandard] where [unit_qty] = P.unit_qty and [item_id] = P.parts_no)))) as Qty,
	P.unit_qty as unit_qty, 
	P.status as Status, 
	P.action_act_qty as ActQty, P.id as IdPS 
	from ProductionSchedule as P left join Supplier as S on P.supplier_id = S.id_internal
	where P.action_plan_date >= @dateFrom and P.action_plan_date <= @dateTo and P.status <> 'Completed'
	order by P.action_plan_date, S.name, P.parts_no

END

else 

BEGIN

	select ROW_NUMBER() OVER(ORDER BY P.action_plan_date, S.name, P.parts_no ASC) AS PRow, P.action_plan_date as PDate, S.name as SName, 
	P.parts_no as Partno, --P.qty as Qty,
	--CONVERT(DECIMAL(10,2), (P.qty / (select qty from [PackingStandard] where [unit_qty] = P.unit_qty and [item_id] = P.parts_no))) as Qty, 
	CONVERT(DECIMAL(10,2), (P.qty / IIF((select count(qty) from [PackingStandard] where [unit_qty] = P.unit_qty and [item_id] = P.parts_no) = 0,
	1,(select qty from [PackingStandard] where [unit_qty] = P.unit_qty and [item_id] = P.parts_no)))) as Qty,
	P.unit_qty as unit_qty, 
	P.status as Status , 
	P.action_act_qty as ActQty, P.id as IdPS
	from ProductionSchedule as P left join Supplier as S on P.supplier_id = S.id_internal
	where P.action_plan_date >= @dateFrom and P.action_plan_date <= @dateTo  
	order by P.action_plan_date, S.name, P.parts_no

END

END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_Inbound_part]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_Inbound_part] 	
	@partNo varchar(40),
	@serialNo varchar(20),
	@qtyScan  decimal(24, 2)
AS



BEGIN

	DECLARE @qtyStock decimal(24, 2)
	
	set @qtyStock = (select top (1) IIF(([qty] Is Null),0,[qty] * 
	ISNULL((select top (1) qty from [PackingStandard] where [unit_qty] = 
	(select top (1) unit_qty from [Inbound] where [kanban_serial] = @serialNo order by [id] desc) 
	and [item_id] = @partNo),1)) as qty 
	from [Stock] where [item_id] = @partNo and [lot_no] = 
	(select top (1) [lot_no] from [Inbound] where [item_id] = @partNo and [kanban_serial] = @serialNo order by id desc)
	and [kanban_serial] = @serialNo
	and [location_id] = (select top (1) [location_id] from [Inbound] where [item_id] = @partNo 
	and [kanban_serial] = @serialNo order by id desc)) - @qtyScan
	
	if (@qtyStock >= 0.00)
	begin
	select 
	[item_id]
      ,[item_name]
      ,[package_id]
      ,[package_qty]
      ,[kanban_serial]
      ,[order_no]
      ,[qty]
      ,[unit_qty]
      ,[location_id]
      ,[location_name]
      ,[ref_sch_id]
      ,[label_id]
      ,[status]
      ,[slip_divide]

	  from [Inbound] where [item_id] = @partNo and [kanban_serial] = @serialNo

	  end
	 else
	 begin
		select 
	[item_id]
      ,[item_name]
      ,[package_id]
      ,[package_qty]
      ,[kanban_serial]
      ,[order_no]
      ,[qty]
      ,[unit_qty]
      ,[location_id]
      ,[location_name]
      ,[ref_sch_id]
      ,[label_id]
      ,[status]
      ,[slip_divide]

	  from [Inbound] where [item_id] = '' and [kanban_serial] = @serialNo
	 end
	

END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_Inbound_part_ReOutbound]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_Inbound_part_ReOutbound] 	
	@partNo varchar(40),
	@serialNo varchar(20),
	@qtyScan  decimal(24, 2)
AS



BEGIN

	DECLARE @qtyStock decimal(24, 2)
	DECLARE @qtyCheck decimal(24, 2)
	
	set @qtyStock = (select top (1) IIF(([qty] Is Null),0,[qty]) as qty from [Stock] where [item_id] = @partNo and [lot_no] =
	(select top (1) [lot_no] from [Inbound] where [item_id] = @partNo and [kanban_serial] = @serialNo order by id desc)
	and [kanban_serial] = @serialNo
	and [location_id] = (select top (1) [location_id] from [Inbound] where [item_id] = @partNo and [kanban_serial] = @serialNo order by id desc))
	
	
	set @qtyCheck = @qtyStock - @qtyScan
	
	if (@qtyCheck >= 0.00)
	begin
	select 
	[item_id]
      ,[item_name]
      ,[package_id]
      ,[package_qty]
      ,[kanban_serial]
      ,[order_no]
      ,[qty]
      ,[unit_qty]
      ,[location_id]
      ,[location_name]
      ,[ref_sch_id]
      ,[label_id]
      ,[status]
      ,[slip_divide]

	  from [Inbound] where [item_id] = @partNo and [kanban_serial] = @serialNo

	  order by id desc

	  end
	 else
	 begin
		if (@qtyStock > 0.00)
		begin
		--select 
		--[item_id] = ''
  --    ,[item_name]
  --    ,[package_id]
  --    ,[package_qty]
  --    ,[serial_no]
  --    ,[order_no]
  --    ,[qty] = @qtyStock
  --    ,[unit_qty]
  --    ,[location_id]
  --    ,[location_name]
  --    ,[ref_sch_id]
  --    ,[label_id]
  --    ,[status]
  --    ,[slip_divide]

	 -- from [Inbound] --where [item_id] = @partNo and [lot_no] = @serialNo

	  select 
	[item_id]
      ,[item_name]
      ,[package_id]
      ,[package_qty]
      ,[kanban_serial]
      ,[order_no]
      ,[qty]
      ,[unit_qty]
      ,[location_id]
      ,[location_name]
      ,[ref_sch_id]
      ,[label_id]
      ,[status]
      ,[slip_divide]

	  from [Inbound] where [item_id] = @partNo and [kanban_serial] = @serialNo

	  order by id desc



	  end
	  else
	  begin
	  select 
		[item_id]
      ,[item_name]
      ,[package_id]
      ,[package_qty]
      ,[kanban_serial]
      ,[order_no]
      ,[qty]
      ,[unit_qty]
      ,[location_id]
      ,[location_name]
      ,[ref_sch_id]
      ,[label_id]
      ,[status]
      ,[slip_divide]

	  from [Inbound] where [item_id] = '' and [kanban_serial] = @serialNo

	  end
	 end
	

END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_Inspection_Detail_duplicate]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_Inspection_Detail_duplicate] 	
	@prod_schd_id bigint,
	@serialNo varchar(10) 
AS



BEGIN

	select ROW_NUMBER() OVER(ORDER BY serial_no ASC) AS DRow, serial_no as serialNo, qty as qtySerial, [status] as statusSerial from SerialNo where prod_schd_id = @prod_schd_id
	and serial_no = @serialNo and status = 'OK'
	order by serial_no


END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_Inspection_Detail_table]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_Inspection_Detail_table] 	
	@prod_schd_id bigint
AS



BEGIN

	select ROW_NUMBER() OVER(ORDER BY id ASC) AS DRow, serial_no as serialNo, qty as qtySerial, [status] as statusSerial, 
	kanban_code, kanban_class, remainder_class from SerialNo where prod_schd_id = @prod_schd_id
	order by id


END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_item_location]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[sp_get_item_location] 	
	@partNo varchar(40),
	@location varchar(20)

AS



BEGIN

select [id] from [Item] where [id] = @partNo and [location_fix_id] = @location



END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_kanban_code]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_kanban_code] 	
	@kanbanCode varchar(40)

AS



BEGIN

select [kanban_name] from [KanbanCode] where kanban_code = @kanbanCode



END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_kanban_status]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_kanban_status] 	
	@kanbanCode varchar(40),
	@kanbanClass varchar(10),
	@partNo varchar(100),
	@serialNo varchar(10) 
AS



BEGIN

select [status] from Kanban where kanban_code = @kanbanCode and kanban_class = @kanbanClass and item_id = @partNo and kanban_serial = @serialNo



END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_location]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_location] 
	@location varchar(20)
AS
BEGIN

	select id,[name]  from Location where id = @location


END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_outbound]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_outbound] 
	@partNo varchar(40),
	@serialNo varchar(20)
AS
BEGIN

	select top (1) [qty]  from Outbound where [item_id] = @partNo and [kanban_serial] = @serialNo  order by [id] desc


END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_Production_Inbound_BOM]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_Production_Inbound_BOM] 	
	@fgParts varchar(100),
	@partNo varchar(100) 
AS



BEGIN

	select product_no, parts_no from BOM where product_no = @fgParts
	and parts_no = @partNo 


END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_Stock_Change_table]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_Stock_Change_table] 	
	@partNo varchar(100),
	@serial varchar(50)
AS



BEGIN
	
	
    select item_id, lot_no, sum(qty) as qty from Stock where item_id =
	@partNo and lot_no = (select top (1) [lot_no] from [Inbound] where [item_id] = @partNo and [kanban_serial] = @serial order by id desc)
	and [kanban_serial] = @serial
	group by item_id, lot_no

END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_Stock_Check_table]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_Stock_Check_table] 	
	@partNo varchar(100) 
AS



BEGIN
	DECLARE @allqty numeric(24, 2)

	set @allqty = (select sum(qty) as sumqty from Stock where item_id = @partNo group by item_id)

	select ROW_NUMBER() OVER(ORDER BY location_id ASC) AS DRow, location_id, sum(qty) as sumqty, @allqty as allqty from Stock 
	where item_id = @partNo group by location_id order by location_id asc


END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_Stock_Taking_table]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_Stock_Taking_table]
    @takingMonth varchar(10),
    @partNo varchar(40),
    @serialNo varchar(50),
    @qty numeric(24, 2),
    @userId varchar(100)
AS
BEGIN
    DECLARE @location varchar(20)

    IF (@partNo = '')
    BEGIN
        SELECT TOP 5000 *
        FROM [dbo].[getStocktaking](@takingMonth)
        ORDER BY location_id, item_id, lot_no, serial_no, division ASC
    END
    ELSE
    BEGIN
        -- set @location = (select top(1) location_id from Stock where item_id = @partNo and lot_no = @serialNo and qty <> 0)

        SET @location = (
            SELECT TOP(1) location_id
            FROM Stocktaking
            WHERE item_id = @partNo
              AND lot_no = @serialNo
              AND qty < st_qty
            ORDER BY location_id
        )

        UPDATE Stocktaking
        SET [qty] = [qty] + @qty, update_dt = SYSDATETIME(), update_user_id = @userId
        WHERE taking_month = @takingMonth
          AND item_id = @partNo
          AND location_id = @location

        SELECT TOP 5000 *
        FROM [dbo].[getStocktaking](@takingMonth)
        WHERE item_id = @partNo
        ORDER BY location_id, item_id, lot_no, serial_no, division ASC
    END
END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_Stock_Taking_table_HT]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_Stock_Taking_table_HT]
    @takingMonth varchar(10),
    @partNo varchar(40),
    @serialNo varchar(50),
    @qty numeric(24, 2),
    @userId varchar(100)
AS
BEGIN
    DECLARE @location varchar(20)
	DECLARE @count numeric

    IF (@partNo = '')
    BEGIN

	set @count = (select count(location_id) FROM Stocktaking
		where taking_month = @takingMonth)

		IF (@count = 0)
		begin
			INSERT INTO [Stocktaking] 
	(
		[item_id]
      ,[location_id]
      ,[lot_no]
      ,[serial_no]
      ,[taking_month]
      ,[st_qty]
      ,[status]
      ,[qty]
      ,[control_unit]
      ,[create_dt]
      ,[create_user_id]     
      ,[remark]
      ,[label_id]
      ,[division]
	)

	select 
		[item_id]
      ,[location_id]
	  ,[lot_no]
	  ,[kanban_serial]
	  ,@takingMonth
	  ,0
	  ,'0'
	  ,[qty]
	  ,(select TOP (1) [control_unit] from [Item] where [id] = [item_id])
	  ,SYSDATETIME()
	  ,@userId
	  ,''
	  ,[kanban_serial]
	  ,1
	from stock
	where [qty] <> 0

		end


        SELECT ROW_NUMBER() OVER(ORDER BY location_id ASC) AS DRow, item_id, location_id, sum(st_qty) as st_qty, sum(qty) as qty
        FROM Stocktaking
		where taking_month = @takingMonth
        group by item_id, location_id

		
    END
    ELSE
    BEGIN
        -- set @location = (select top(1) location_id from Stock where item_id = @partNo and lot_no = @serialNo and qty <> 0)

		set @count = (select count(location_id) FROM Stocktaking
		where taking_month = @takingMonth)

		IF (@count = 0)
		begin
			INSERT INTO [Stocktaking] 
	(
		[item_id]
      ,[location_id]
      ,[lot_no]
      ,[serial_no]
      ,[taking_month]
      ,[st_qty]
      ,[status]
      ,[qty]
      ,[control_unit]
      ,[create_dt]
      ,[create_user_id]     
      ,[remark]
      ,[label_id]
      ,[division]
	)

	select 
		[item_id]
      ,[location_id]
	  ,[lot_no]
	  ,[kanban_serial]
	  ,@takingMonth
	  ,0
	  ,'0'
	  ,[qty]
	  ,(select TOP (1) [control_unit] from [Item] where [id] = [item_id])
	  ,SYSDATETIME()
	  ,@userId
	  ,''
	  ,[kanban_serial]
	  ,1
	from stock
	where [qty] <> 0

		end




        SET @location = (
            SELECT TOP(1) location_id
            FROM Stocktaking
            WHERE item_id = @partNo
              AND serial_no = @serialNo
              AND st_qty < qty
            ORDER BY location_id
        )

        UPDATE Stocktaking
        SET st_qty = st_qty + @qty, update_dt = SYSDATETIME(), update_user_id = @userId
        WHERE taking_month = @takingMonth
          AND item_id = @partNo
          AND location_id = @location
		  and [serial_no] = @serialNo
		         
		SELECT ROW_NUMBER() OVER(ORDER BY location_id ASC) AS DRow, item_id, location_id, sum(st_qty) as st_qty, sum(qty) as qty
        FROM Stocktaking
		where taking_month = @takingMonth and item_id = @partNo
		group by item_id, location_id
        
    END
END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_user]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_user] 
	@status tinyint
AS
BEGIN

	select id,[password]  from UserCredential where status = @status


END
GO
/****** Object:  StoredProcedure [dbo].[sp_get_User_Leader]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_User_Leader] 	
	@userID varchar(100)
	
AS



BEGIN

	select ROW_NUMBER() OVER(ORDER BY [id] ASC) AS DRow, [id], [auth_id] from Users where [id] = @userID
	and ([auth_id] = 'admin' or [auth_id] = 'leader')
	order by [id]


END
GO
/****** Object:  StoredProcedure [dbo].[sp_insert_Inbound]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_Inbound] 	
	@partNo varchar(50),
	@locationId varchar(20),
	@serial varchar(20),
	@qtySerial numeric(24, 2),
	@userId varchar(100)
AS



BEGIN

	DECLARE @count NUMERIC
	DECLARE @pid NUMERIC
	DECLARE @lotNo varchar(20)
		
	--set @pid = (select TOP (1) [prod_schd_id] from [SerialNo] where [parts_no] = @partNo and [serial_no] = @serial)

	set @count = (select TOP (1) count([id]) as countId from [ProductionSchedule] where [parts_no] = @partNo)
		
	set @pid = (select TOP (1) [id] from [ProductionSchedule] where [parts_no] = @partNo)
	
	
	set @lotNo = (SELECT FORMAT(GetDate(), 'yyyy-MM-dd')) 

	if (@count > 0)
	begin
	INSERT INTO Inbound 
	(
		[item_id]
      ,[item_name]
      ,[supplier_id]
      ,[supplier_name]
      ,[package_id]
      ,[package_qty]
      ,[lot_no]
      ,[order_no]
      ,[qty]
      ,[unit_qty]
      ,[location_id]
      ,[location_name]
      ,[ref_sch_id]
      ,[label_id]
      ,[status]
      ,[slip_divide]
      ,[create_dt]
      ,[create_user_id]
      ,[update_dt]
	  ,[inspection_flg]
	  ,[ng]
	  ,[kanban_serial]
	)
	select [parts_no], [parts_name], [supplier_id], (select TOP (1) [name] from [Supplier] where [id_internal] = PS.[supplier_id]) as [supplier_name] , 
		--(select TOP (1) [package_id] from [PackingStandard] where [item_id] = PS.[parts_no] and [unit_qty] = PS.[unit_qty]) ,
		--(select TOP (1) [qty] from [PackingStandard] where [item_id] = PS.[parts_no] and [unit_qty] = PS.[unit_qty]),	
		--PS.[unit_qty],
		(select TOP (1) [control_unit] from [Item] where [id] = PS.[parts_no]),
		ISNULL((select TOP (1) [qty] from [PackingStandard] where [item_id] = PS.[parts_no] and [unit_qty] = 
		(select TOP (1) [control_unit] from [Item] where [id] = PS.[parts_no])),1),
		@lotNo, [po_no], @qtySerial, 
		--[unit_qty], 
		(select TOP (1) [control_unit] from [Item] where [id] = PS.[parts_no]),
		@locationId, (select TOP (1) [name] from [Location] where [id] = @locationId) ,
		@pid, @serial, 1, 3, SYSDATETIME(), @userId, SYSDATETIME(), 0, 0, @serial from [ProductionSchedule] PS where [id] = @pid

		end

		else

		begin
		INSERT INTO Inbound 
	(
		[item_id]
      ,[item_name]
      ,[supplier_id]
      ,[supplier_name]
      ,[package_id]
      ,[package_qty]
      ,[lot_no]
      ,[order_no]
      ,[qty]
      ,[unit_qty]
      ,[location_id]
      ,[location_name]
      ,[ref_sch_id]
      ,[label_id]
      ,[status]
      ,[slip_divide]
      ,[create_dt]
      ,[create_user_id]
      ,[update_dt]
	  ,[inspection_flg]
	  ,[ng]
	  ,[kanban_serial]
	)
	VALUES
	(
	@partNo
	,(select TOP (1) [name] from [Item] where [id] = @partNo)
	,''
	,''
	,(select TOP (1) [control_unit] from [Item] where [id] = @partNo)
	,ISNULL((select TOP (1) [qty] from [PackingStandard] where [item_id] = @partNo and [unit_qty] = 
		(select TOP (1) [control_unit] from [Item] where [id] = @partNo)),1)
		,@lotNo
		,''
		,@qtySerial
		,(select TOP (1) [control_unit] from [Item] where [id] = @partNo)
		,@locationId
		,(select TOP (1) [name] from [Location] where [id] = @locationId)
		,NULL
		,@serial
		,1, 3, SYSDATETIME(), @userId, SYSDATETIME(), 0, 0, @serial
	)

		end
			

END
GO
/****** Object:  StoredProcedure [dbo].[sp_insert_Inbound_Production]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_Inbound_Production] 	
	@partNo varchar(50),
	@locationId varchar(20),
	@serial varchar(20),
	@qtySerial numeric(24, 2),
	@userId varchar(100),
	@productNo varchar(100)
AS



BEGIN

	DECLARE @count NUMERIC
	DECLARE @pid NUMERIC
	DECLARE @pdid NUMERIC
	DECLARE @lotNo varchar(20)
	
	set @count = (select TOP (1) count([id]) from [ProductionSchedule] where [parts_no] = @productNo)
	if	(@count > 0)
	begin
	set @pdid = (select TOP (1) ISNULL([id], 0 ) from [ProductionSchedule] where [parts_no] = @productNo)
	end
	else
	begin
	set @pdid = 0
	end

	set @pid = (select TOP (1) [id] from [Bom] where [product_no] = @productNo and [parts_no] = @partNo order by id desc)

	set @lotNo = (SELECT FORMAT(GetDate(), 'yyyy-MM-dd')) 

	
	INSERT INTO Inbound 
	(
		[item_id]
      ,[item_name]
      ,[supplier_id]
      ,[supplier_name]
      ,[package_id]
      ,[package_qty]
      ,[lot_no]
      ,[order_no]
      ,[qty]
      ,[unit_qty]
      ,[location_id]
      ,[location_name]
      ,[ref_sch_id]
      ,[label_id]
      ,[status]
      ,[slip_divide]
      ,[create_dt]
      ,[create_user_id]
      ,[update_dt]
	  ,[inspection_flg]
	  ,[ng]
	  ,[kanban_serial]
	)
	select [parts_no], (select TOP (1) ISNULL([name], '') from [Item] where [id] = B.[parts_no]) as [parts_name], 
	[supplier_no], (select TOP (1) ISNULL([name], '') from [Supplier] where [id] = B.[supplier_no]) as [supplier_name] , 
		--(select TOP (1) ISNULL([package_id], '') from [PackingStandard] where [item_id] = B.[parts_no]) ,
		(select [control_unit] from [Item] where [id] = B.[parts_no]),
		--(select TOP (1) ISNULL([qty],0) from [PackingStandard] where [item_id] = B.[parts_no]) and [unit_qty] = 
		--(select [control_unit] from [Item] where [id] = B.[parts_no])),		
		(select TOP (1) [qty] from [PackingStandard] where [item_id] = B.[parts_no] and [unit_qty] = 
		(select [control_unit] from [Item] where [id] = B.[parts_no])),
		@lotNo, '', @qtySerial, 
		--(select TOP (1) ISNULL([unit_qty], '') from [PackingStandard] where [item_id] = B.[parts_no]), 
		(select [control_unit] from [Item] where [id] = B.[parts_no]),
		@locationId, (select TOP (1) ISNULL([name], '') from [Location] where [id] = @locationId) ,
		@pdid, @serial, 1, 3, SYSDATETIME(), @userId, SYSDATETIME(), 0, 0, @serial from [Bom] B where [id] = @pid

			

END
GO
/****** Object:  StoredProcedure [dbo].[sp_insert_Inspection_Detail]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_Inspection_Detail] 	
	@prod_schd_id bigint,
	@parts_no varchar(50),
	@serialNo varchar(10),
	@qtySerial decimal(24, 2),
	@statusSerial varchar(20),
	@userId varchar(100),
	@kanbanCode varchar(10),
	@kanbanClass varchar(10),
	@remainderClass varchar(10)
AS



BEGIN

	
	INSERT INTO SerialNo 
	(
		prod_schd_id, parts_no, serial_no, qty, [status], create_dt, create_user_id, kanban_code, kanban_class, remainder_class
	)
	VALUES
	(
		@prod_schd_id,
		@parts_no,
		@serialNo,
		@qtySerial,
		@statusSerial,
		SYSDATETIME(),
		@userId,
		@kanbanCode,
		@kanbanClass,
		@remainderClass
		
	)

END
GO
/****** Object:  StoredProcedure [dbo].[sp_insert_Kanban_Transfer]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_Kanban_Transfer] 	
	@temp_kanban varchar(100),
	@temp_parts_no nvarchar(50),
	@temp_serial_no varchar(10),
	@temp_qty decimal(24, 2),
	@temp_status varchar(20),
	@normal_kanban varchar(100),
	@normal_parts_no nvarchar(50),
	@normal_serial_no varchar(10),
	@normal_qty decimal(24, 2),
	@normal_status varchar(20),
	@userId varchar(100)
AS



BEGIN

	DECLARE @count NUMERIC
	DECLARE @pid NUMERIC
	
	
	INSERT INTO KanbanTransfer 
	(
		temp_kanban
      ,temp_parts_no
      ,temp_serial_no
      ,temp_qty
      ,temp_status
      ,normal_kanban
      ,normal_parts_no
      ,normal_serial_no
      ,normal_qty
      ,normal_status
      ,[create_dt]
      ,[create_user_id]
      ,[update_dt]
	)

	VALUES (
		@temp_kanban,
		@temp_parts_no,
		@temp_serial_no,
		@temp_qty,
		@temp_status,
		@normal_kanban,
		@normal_parts_no,
		@normal_serial_no,
		@normal_qty,
		@normal_status,
		SYSDATETIME(), 
		@userId, 
		SYSDATETIME()

	)
		

END
GO
/****** Object:  StoredProcedure [dbo].[sp_insert_KB]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_KB] 	
	@PDSNo varchar(100),
	@HTID varchar(50),
	@userName varchar(50),
	@logDate varchar(20),
	@logTime varchar(20),
	@CustomerKB varchar(50),
	@CustomerQty varchar(50),
	@SKTKB varchar(50),
	@SKTQTY varchar(50),
	@ResultKB varchar(20),
	@ResultQT varchar(20),
	@Approve varchar(50),
	@SKTKBSerial nvarchar(20),
	@customerName varchar(50)

AS



BEGIN


	INSERT INTO Record_KB 
	(
		logDate, logTime, HTID, userName, PDS, CustomerName, CustomerKB, CustomerQty, SKTKB, SKTKBSerial, SKTQTY, ResultKB, ResultQT, Approve, systemDateTime
		
	)
	VALUES
	(
		@logDate,
		@logTime,
		@HTID,
		@userName,
		@PDSNo,
		@customerName,
		@CustomerKB,
		@CustomerQty,
		@SKTKB,
		@SKTKBSerial,
		@SKTQTY,
		@ResultKB,
		@ResultQT,
		@Approve,
		SYSDATETIME()
		
	)

	

END
GO
/****** Object:  StoredProcedure [dbo].[sp_insert_Outbound]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_Outbound] 	
	@partNo varchar(50),
	@serial varchar(20),
	@qtySerial numeric(24, 2),
	@userId varchar(100)
AS



BEGIN

	DECLARE @count NUMERIC
	DECLARE @pid NUMERIC
	
	
	INSERT INTO Outbound 
	(
		[item_id]
      ,[item_name]
      ,[customer_id]
      ,[customer_name]
      ,[package_id]
      ,[package_qty]
      ,[lot_no]
      ,[order_no]
      ,[qty]
      ,[unit_qty]
      ,[location_id]
      ,[location_name]
      ,[ref_sch_id]
      ,[label_id]
      ,[status]
      ,[slip_divide]
      ,[create_dt]
      ,[create_user_id]
      ,[update_dt]
	  ,[kanban_serial]
	 
	)

	select TOP (1)
	[item_id]
      ,[item_name]
      ,''
      ,''
      ,[package_id]
      ,[package_qty]
	  --, @qtySerial * (select TOP (1) [qty] from [PackingStandard] where [item_id] = [item_id] and [unit_qty] = [unit_qty])
      ,[lot_no]
      ,[order_no]
      ,@qtySerial
      ,[unit_qty]
      ,[location_id]
      ,[location_name]
      ,[ref_sch_id]
      ,[label_id]
      ,2
      ,4
      ,SYSDATETIME(), @userId, SYSDATETIME()
	  ,[kanban_serial]

	  from [Inbound] where [item_id] = @partNo and [kanban_serial] = @serial

	  order by id desc	   
	

END
GO
/****** Object:  StoredProcedure [dbo].[sp_insert_PDS]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_PDS] 	
	@PDSNo varchar(100),
	@HTID varchar(50),
	@userName varchar(50),
	@logDate varchar(20),
	@logTime varchar(20)
AS



BEGIN


	INSERT INTO Record_PDS 
	(
		logDate, logTime, HTID, userName, PDS, systemDateTime
		
	)
	VALUES
	(
		@logDate,
		@logTime,
		@HTID,
		@userName,
		@PDSNo,
		SYSDATETIME()
		
	)

	

END
GO
/****** Object:  StoredProcedure [dbo].[sp_insert_Production_Inbound]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_Production_Inbound] 	
	@line varchar(50),
	@FGparts varchar(50),
	@parts_no varchar(50),
	@serialNo varchar(10),
	@qtySerial int,
	@statusSerial varchar(20),
	@userId varchar(100),
	@kanbanCode varchar(10),
	@kanbanClass varchar(10),
	@remainderClass varchar(10),
	@FGpartsKanban varchar(100)
AS



BEGIN

	DECLARE @pid NUMERIC
	DECLARE @outboundStatus NUMERIC

	set @pid = (select TOP (1) [id] from [ProductionSchedule] where [parts_no] = @FGparts order by id desc)

	set @outboundStatus = 0
	
	INSERT INTO ProductionInbound 
	(
		prod_schd_id, Line, FGparts, parts_no, serial_no, qty, [status], create_dt, create_user_id, kanban_code, kanban_class, remainder_class,
		FGparts_Kanban, outbound_status
	)
	VALUES
	(
		@pid,
		@line,
		@FGparts,
		@parts_no,
		@serialNo,
		@qtySerial,
		@statusSerial,
		SYSDATETIME(),
		@userId,
		@kanbanCode,
		@kanbanClass,
		@remainderClass,
		@FGpartsKanban,
		@outboundStatus
		
	)

	

END
GO
/****** Object:  StoredProcedure [dbo].[sp_insert_Production_Outbound]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_Production_Outbound] 	
	@line varchar(50),
	@FGparts varchar(50),
	@parts_no varchar(50),
	@serialNo varchar(10),
	@qtySerial int,
	@statusSerial varchar(20),
	@userId varchar(100),
	@kanbanCode varchar(10),
	@kanbanClass varchar(10),
	@remainderClass varchar(10),
	@FGpartsKanban varchar(100)
AS



BEGIN

	DECLARE @pid NUMERIC

	set @pid = (select TOP (1) [id] from [ProductionSchedule] where [parts_no] = @FGparts order by id desc)
		
	INSERT INTO ProductionOutbound 
	(
		prod_schd_id, Line, FGparts, parts_no, serial_no, qty, [status], create_dt, create_user_id, kanban_code, kanban_class, remainder_class,
		FGparts_Kanban
	)
	
	select 
	[prod_schd_id]
      ,[Line]
      ,[FGparts]
      ,[parts_no]
      ,[serial_no]
      ,[qty]
      ,[status]
      ,SYSDATETIME()
      ,@userId      
      ,[kanban_code]
      ,[kanban_class]
      ,[remainder_class]
	  ,[FGparts_Kanban]
  FROM [ProductionInbound]
  where FGparts_Kanban = @FGpartsKanban and [Line] = @line and outbound_status = 0

  UPDATE ProductionInbound
  SET outbound_status = 1
  where FGparts_Kanban = @FGpartsKanban and [Line] = @line and outbound_status = 0


END
GO
/****** Object:  StoredProcedure [dbo].[sp_insert_ReInbound]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_ReInbound] 	
	@partNo varchar(50),
	@locationId varchar(20),
	@serial varchar(20),
	@qtySerial numeric(24, 2),
	@userId varchar(100)
AS



BEGIN

	DECLARE @count NUMERIC
	DECLARE @pid NUMERIC
	DECLARE @Outid NUMERIC
	DECLARE @OutQty NUMERIC
	DECLARE @lotNo varchar(20)
	
	set @pid = (select TOP (1) [prod_schd_id] from [SerialNo] where [parts_no] = @partNo and [serial_no] = @serial)
	set @Outid = (select TOP (1) [id] from [Outbound] where [item_id] = @partNo and [kanban_serial] = @serial order by id desc)
	set @OutQty = (select TOP (1) [qty] from [Outbound] where [item_id] = @partNo and [kanban_serial] = @serial order by id desc) - @qtySerial
	--set @lotNo = (SELECT FORMAT(GetDate(), 'yyyy-MM-dd'))
	set @lotNo = (select TOP (1) [lot_no] from [Outbound] where [item_id] = @partNo and [kanban_serial] = @serial order by id desc)
	
	INSERT INTO Inbound 
	(
		[item_id]
      ,[item_name]
      ,[supplier_id]
      ,[supplier_name]
      ,[package_id]
      ,[package_qty]
      ,[lot_no]
      ,[order_no]
      ,[qty]
      ,[unit_qty]
      ,[location_id]
      ,[location_name]
      ,[ref_sch_id]
      ,[label_id]
      ,[status]
      ,[slip_divide]
      ,[create_dt]
      ,[create_user_id]
      ,[update_dt]
	  ,[inspection_flg]
	  ,[ng]
	  ,[kanban_serial]
	)
	select [parts_no], [parts_name], [supplier_id], (select TOP (1) [name] from [Supplier] where [id_internal] = PS.[supplier_id]) as [supplier_name] , 
		(select TOP (1) [package_id] from [PackingStandard] where [item_id] = PS.[parts_no]) ,
		(select TOP (1) [qty] from [PackingStandard] where [item_id] = PS.[parts_no]),			   
		@lotNo, '', @qtySerial, [unit_qty], @locationId, (select TOP (1) [name] from [Location] where [id] = @locationId) ,
		@pid, @serial, 1, 3, SYSDATETIME(), @userId, SYSDATETIME(), 0, 0, @serial from [ProductionSchedule] PS where [id] = @pid
	
	update [Outbound]
	set [qty] = @OutQty, update_dt = SYSDATETIME(), update_user_id = @userId
	where [id] = @Outid
	

END
GO
/****** Object:  StoredProcedure [dbo].[sp_insert_Stock_Tracking]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_Stock_Tracking] 	
	@partNo varchar(40),
	@locationId varchar(20),
	@serial varchar(20),
	@qtySerial numeric(24, 2),
	@userId varchar(100)
AS



BEGIN

	DECLARE @count NUMERIC
	
	

	INSERT INTO StockTracking 
	(
		item_id, location_id, lot_no, inbound_qty, outbound_qty, increase, section, create_dt, create_user_id
	)
	VALUES
	(
		@partNo,
		@locationId,
		@serial,
		@qtySerial,
		0,
		@qtySerial,
		'',
		SYSDATETIME(),
		@userId
		
	)

	set @count = (select count(item_id) from StockTracking where item_id = @partNo and location_id = @locationId and lot_no = 
	(select top (1) [lot_no] from [Inbound] where [item_id] = @partNo and [kanban_serial] = @serial order by id desc))

	if (@count = 0)
	begin
	INSERT INTO Stock 
	(
		item_id, location_id, lot_no, carry_over, qty, hold_qty, reserve_qty, create_dt, create_user_id
	)
	VALUES
	(

		@partNo,
		@locationId,
		@serial,
		0,
		@qtySerial,
		0,
		0,
		SYSDATETIME(),
		@userId
		
	)
	end
	else
	begin
		UPDATE Stock set qty = qty + @qtySerial, update_dt = SYSDATETIME(), update_user_id = @userId where item_id = @partNo 
		and location_id = @locationId and lot_no =
		(select top (1) [lot_no] from [Inbound] where [item_id] = @partNo and [kanban_serial] = @serial order by id desc)
		and [kanban_serial] = @serial
	end

END
GO
/****** Object:  StoredProcedure [dbo].[sp_insert_Stock_Tracking_Outbound]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_Stock_Tracking_Outbound] 	
	@partNo varchar(40),
	@serial varchar(20),
	@qtySerial numeric(24, 2),
	@userId varchar(100)
AS



BEGIN

	DECLARE @count NUMERIC
	DECLARE @location varchar(20)
	
	set @location = (select location_id from Stock where item_id = @partNo and lot_no =
	(select top (1) [lot_no] from [Inbound] where [item_id] = @partNo and [kanban_serial] = @serial order by id desc)
	and [kanban_serial] = @serial)

	INSERT INTO StockTracking 
	(
		item_id, location_id, lot_no, inbound_qty, outbound_qty, increase, section, create_dt, create_user_id
	)
	VALUES
	(
		@partNo,
		@location,
		@serial,
		0,
		@qtySerial,
		-@qtySerial,
		'',
		SYSDATETIME(),
		@userId
		
	)

	
		UPDATE Stock set qty = qty - @qtySerial, update_dt = SYSDATETIME(), update_user_id = @userId where item_id = @partNo 
		and location_id = @location and lot_no = 
		(select top (1) [lot_no] from [Inbound] where [item_id] = @partNo and [kanban_serial] = @serial order by id desc)
		and [kanban_serial] = @serial
	

END
GO
/****** Object:  StoredProcedure [dbo].[sp_insert_Store_Outbound]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_Store_Outbound] 	
	@store_kanban varchar(100),
	@store_parts_no nvarchar(50),
	@store_serial_no varchar(10),
	@store_qty decimal(24, 2),
	@store_status varchar(20),
	@pickup_kanban varchar(100),
	@pickup_parts_no nvarchar(50),
	@pickup_serial_no varchar(10),
	@pickup_qty decimal(24, 2),
	@pickup_status varchar(20),
	@userId varchar(100)
AS



BEGIN

	DECLARE @count NUMERIC
	DECLARE @pid NUMERIC
	
	
	INSERT INTO [StoreOutbound] 
	(
		[store_kanban]
      ,[store_parts_no]
      ,[store_serial_no]
      ,[store_qty]
      ,[store_status]
      ,[pickup_kanban]
      ,[pickup_parts_no]
      ,[pickup_serial_no]
      ,[pickup_qty]
      ,[pickup_status]
      ,[create_dt]
      ,[create_user_id]
      ,[update_dt]
	)

	VALUES (
		@store_kanban,
		@store_parts_no,
		@store_serial_no,
		@store_qty,
		@store_status,
		@pickup_kanban,
		@pickup_parts_no,
		@pickup_serial_no,
		@pickup_qty,
		@pickup_status,
		SYSDATETIME(), 
		@userId, 
		SYSDATETIME()

	)
		

END
GO
/****** Object:  StoredProcedure [dbo].[sp_update_change_stock]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_update_change_stock] 	
	@partNo varchar(40),
	@locationId varchar(20),
	@serial varchar(20),
	@qtySerial numeric(24, 2),
	@userId varchar(100)
AS



BEGIN

	INSERT INTO Stock
	(
	[item_id]
      ,[location_id]
      ,[lot_no]
      ,[carry_over]
      ,[qty]
      ,[hold_qty]
      ,[reserve_qty]
      ,[create_dt]
      ,[create_user_id]
      ,[update_dt]
      ,[update_user_id]
      ,[kanban_serial]

	)

	select top (1) [item_id]
      ,@locationId
      ,[lot_no]
      ,[carry_over]
      ,[qty]
      ,[hold_qty]
      ,[reserve_qty]
      ,[create_dt]
      ,[create_user_id]
      ,SYSDATETIME()
      ,@userId
      ,[kanban_serial]
	  from stock where item_id = @partNo and lot_no =
	(select top (1) [lot_no] from [Inbound] where [item_id] = @partNo and [kanban_serial] = @serial order by id desc)
	and [kanban_serial] = @serial


	UPDATE Stock
	SET [qty] = 0, update_dt = SYSDATETIME(), update_user_id = @userId
	WHERE item_id = @partNo and lot_no =
	(select top (1) [lot_no] from [Inbound] where [item_id] = @partNo and [kanban_serial] = @serial order by id desc)
	and [kanban_serial] = @serial and [location_id] <> @locationId
			
	

END
GO
/****** Object:  StoredProcedure [dbo].[sp_update_Outbound]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_update_Outbound] 	
	@partNo varchar(50),
	@serial varchar(20),
	@qtySerial numeric(24, 2),
	@userId varchar(100)
AS



BEGIN

	
	INSERT INTO Outbound 
	(
		[item_id]
      ,[item_name]
      ,[customer_id]
      ,[customer_name]
      ,[package_id]
      ,[package_qty]
      ,[lot_no]
      ,[order_no]
      ,[qty]
      ,[unit_qty]
      ,[location_id]
      ,[location_name]
      ,[ref_sch_id]
      ,[label_id]
      ,[status]
      ,[slip_divide]
      ,[create_dt]
      ,[create_user_id]
      ,[update_dt]
	  ,[kanban_serial]
	 
	)

	select TOP(1)
	[item_id]
      ,[item_name]
      ,''
      ,''
      ,[package_id]
      ,[package_qty]
      ,[lot_no]
      ,[order_no]
      ,@qtySerial
      ,[unit_qty]
      ,[location_id]
      ,[location_name]
      ,[ref_sch_id]
      ,[label_id]
      ,2
      ,4
      ,SYSDATETIME(), @userId, SYSDATETIME()
	  ,[kanban_serial]

	  from [Inbound] where [item_id] = @partNo and [kanban_serial] = @serial
	
	order by id desc


END
GO
/****** Object:  StoredProcedure [dbo].[sp_update_status_Inbound_Inspection]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_update_status_Inbound_Inspection] 	
	@prod_schd_id bigint,
	@userId varchar(100),
	@status varchar(50)
AS



BEGIN

	UPDATE ProductionSchedule
	SET status = @status, update_dt = SYSDATETIME(), update_user_id = @userId
	WHERE id = @prod_schd_id;
	
			
	

END
GO
/****** Object:  StoredProcedure [dbo].[sp_update_status_kanban_HT]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_update_status_kanban_HT] 	
	@kanbanCode varchar(40),
	@kanbanClass varchar(10),
	@partNo varchar(100),
	@serialNo varchar(10),
	@userId varchar(100),
	@status tinyint
AS



BEGIN

	UPDATE Kanban
	SET [status] = @status, update_dt = SYSDATETIME(), update_user_id = @userId
	WHERE kanban_code = @kanbanCode and kanban_class = @kanbanClass and item_id = @partNo and kanban_serial = @serialNo
	
			
	

END
GO
/****** Object:  StoredProcedure [dbo].[sp_update_status_kanban_HT_production]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_update_status_kanban_HT_production] 		
	@userId varchar(100),
	@status tinyint,
	@FGKanban varchar(100)
AS



BEGIN

	

	UPDATE k
	SET [status] = @status, update_dt = SYSDATETIME(), update_user_id = @userId
	from Kanban k
	LEFT JOIN ProductionOutbound p ON p.kanban_code COLLATE Latin1_General_100_CI_AI_SC_UTF8 = k.kanban_code 
	and p.kanban_class COLLATE Latin1_General_100_CI_AI_SC_UTF8 = k.kanban_class 
	and p.parts_no COLLATE Latin1_General_100_CI_AI_SC_UTF8 = k.item_id 
	and p.serial_no COLLATE Latin1_General_100_CI_AI_SC_UTF8 = k.kanban_serial 
	where p.FGparts_Kanban = @FGKanban

	
	

END
GO
/****** Object:  StoredProcedure [dbo].[sp_update_stock]    Script Date: 7/12/2024 2:16:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_update_stock] 
	-- Add the parameters for the stored procedure here
	@parts_no VARCHAR(100)
	, @location_id VARCHAR(100)
	, @lot_no VARCHAR(50)
	, @qty DECIMAL(24,2)
	, @hold_qty DECIMAL(24,2)
	, @reserve_qty DECIMAL(24,2)
	, @user_id VARCHAR(100)
	, @kanban_serial VARCHAR(20)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	MERGE Stock as t
	USING (VALUES (@parts_no, @location_id, @lot_no, @qty, @hold_qty, @reserve_qty, @user_id, @kanban_serial))
	AS s (parts_no, location_id, lot_no, qty, hqty, sqty, uid, kanban_serial)
	ON t.item_id = s.parts_no
	AND t.location_id = s.location_id
	AND t.lot_no = s.lot_no
	AND t.kanban_serial = s.kanban_serial
	WHEN matched THEN
	UPDATE
	SET t.qty = t.qty + s.qty
	, t.hold_qty = t.hold_qty + s.hqty
	, t.reserve_qty = t.reserve_qty + s.sqty
	WHEN NOT matched THEN
	INSERT (item_id, location_id, lot_no, qty, hold_qty, reserve_qty, create_user_id, kanban_serial)
	VALUES (s.parts_no, s.location_id, s.lot_no, s.qty, s.hqty, s.sqty, s.uid, s.kanban_serial);
END
GO
