USE [P21]
GO

/****** Object:  UserDefinedFunction [dbo].[nutech_fn_kit_items_w_costs]    Script Date: 9/23/2024 6:32:14 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ===================================================
-- Author:		Fred Rappuhn, NuTech Services, LLC
-- Create date: 08/23/207
-- Description:	Return all items in kit including sub kits
-- ===================================================
CREATE FUNCTION [dbo].[nutech_fn_kit_items_w_costs] (
	@QTY as integer, 
	@ITEM_ID as varchar(40),
	@SOURCE_LOCATION_ID int,
	@START_DATE as datetime,
	@END_DATE as datetime)
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
,moving_average_cost		DECIMAL(19,9) NULL
)
AS
BEGIN

DECLARE @ITEM_UID int;
DECLARE @INV_MAST_UID int;
DECLARE @COUNT int = @QTY;
DECLARE @TEMP int;
DECLARE @LABOR_COST_PER_KIT	DECIMAL(19,9);

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, inv_loc.moving_average_cost
  from assembly_line
         inner join inv_mast on
		   inv_mast.inv_mast_uid = assembly_line.component_inv_mast_uid
         inner join inv_loc on
		   inv_loc.inv_mast_uid = inv_mast.inv_mast_uid
  where assembly_line.inv_mast_uid = @ITEM_UID
  and assembly_line.delete_flag <> 'Y'
  and inv_loc.location_id = @SOURCE_LOCATION_ID

  select TOP 1 @COUNT=quantity, @ITEM_UID=inv_mast_uid, @INV_MAST_UID=kit_inv_mast_uid
  from @kit_items
  where inv_mast_uid in (select inv_mast_uid from assembly_hdr where delete_flag <> 'Y')

  delete from @kit_items where kit_inv_mast_uid = @INV_MAST_UID and inv_mast_uid = @ITEM_UID

end

select @LABOR_COST_PER_KIT=labor_cost_per_kit
from nutech_fn_kit_costs(@ITEM_ID, @SOURCE_LOCATION_ID, @START_DATE, @END_DATE);

delete @kit_items
where item_id = 'DNULABOR' and kit_item_id <> @ITEM_ID

update @kit_items
set moving_average_cost = @LABOR_COST_PER_KIT, quantity = @QTY
where item_id = 'DNULABOR'

return

END
GO


