USE [P21]
GO

/****** Object:  UserDefinedFunction [dbo].[nutech_fn_kit_subkits]    Script Date: 9/23/2024 6:33:34 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ===================================================
-- Author:		Fred Rappuhn, NuTech Services, LLC
-- Create date: 05/16/2021
-- Description:	Return all items in kit including sub kits
-- ===================================================
CREATE FUNCTION [dbo].[nutech_fn_kit_subkits] (
	@COUNT as integer, 
	@ITEM_ID as varchar(40))
RETURNS @kit_items TABLE 
(	
quantity					INTEGER NULL
,kit_inv_mast_uid			INTEGER NULL
,kit_item_id				VARCHAR(40) NULL
,inv_mast_uid				INTEGER NULL
,item_id					VARCHAR(40) NULL
,processed					CHAR(1) NULL
)
AS
BEGIN

DECLARE @ITEM_UID int;
DECLARE @INV_MAST_UID int;
DECLARE @QTY int;
DECLARE @TEMP int;

select @ITEM_UID = inv_mast.inv_mast_uid from inv_mast where inv_mast.item_id = @ITEM_ID

WHILE @@ROWCOUNT <> 0
begin

INSERT INTO @kit_items
  select @COUNT*quantity as quantity, assembly_line.inv_mast_uid, dbo.p21_fn_item_id(assembly_line.inv_mast_uid), 
         component_inv_mast_uid, inv_mast.item_id, 'N'
  from assembly_line
         inner join inv_mast on
           inv_mast.inv_mast_uid = assembly_line.component_inv_mast_uid
  where assembly_line.inv_mast_uid = @ITEM_UID
  and assembly_line.delete_flag <> 'Y'
  and assembly_line.sub_assembly = 'Y'

  select TOP 1 @COUNT=quantity, @ITEM_UID=inv_mast_uid, @INV_MAST_UID=kit_inv_mast_uid
  from @kit_items
  where processed = 'N'

  update @kit_items set processed = 'Y' where kit_inv_mast_uid = @INV_MAST_UID and inv_mast_uid = @ITEM_UID and processed = 'N'
end

return

END
GO


