USE [P21]
GO

/****** Object:  UserDefinedFunction [dbo].[nutech_fn_kit_items_report]    Script Date: 9/23/2024 6:31:56 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- ===================================================
-- Author:		Fred Rappuhn, NuTech Services, LLC
-- Create date: 11/02/2020
-- Description:	Return all items in kit including sub kits using a table as input
-- ===================================================
CREATE FUNCTION [dbo].[nutech_fn_kit_items_report] (
	@PI_CUSTOMER_ID as integer, 
	@PI_LOCATION_ID as integer)
RETURNS @kit_items_report TABLE 
(	
quantity					INTEGER NULL
,kit_inv_mast_uid			INTEGER NULL
,kit_item_id				VARCHAR(40) NULL  -- just added 10/13/2021
,inv_mast_uid				INTEGER NULL
,item_id					VARCHAR(40) NULL
)
AS
BEGIN

DECLARE @QTY integer;
DECLARE @ITEM_ID varchar(100);
DECLARE @LOCATION_ID integer;
DECLARE @LAST_MODIFIED datetime;

DECLARE db_cursor CURSOR FOR
select qty, location_id, item_id, last_modified
from nutech_kit_item_report_input
where customer_id=@PI_CUSTOMER_ID and
  location_id=@PI_LOCATION_ID
order by last_modified;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @QTY, @LOCATION_ID, @ITEM_ID, @LAST_MODIFIED;

WHILE @@FETCH_STATUS = 0
BEGIN

  insert into @kit_items_report
    select *
    from nutech_fn_kit_items(@QTY,@ITEM_ID) 

  FETCH NEXT FROM db_cursor INTO @QTY, @LOCATION_ID, @ITEM_ID, @LAST_MODIFIED;

END

CLOSE db_cursor

DEALLOCATE db_cursor

return

END
GO


