USE [P21]
GO

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

SET QUOTED_IDENTIFIER ON
GO

-- ===================================================
-- Author:		Fred Rappuhn, NuTech Services, LLC
-- Create date: 05/15/2021
-- Description:	Return material costs and labor cost
-- based on range of production orders.
-- ===================================================
CREATE FUNCTION [dbo].[nutech_fn_kit_costs2] (
	@ITEM_ID as varchar(40),
	@SOURCE_LOCATION_ID as int,
	@START_DATE as datetime,
	@END_DATE as datetime)
RETURNS @kit_items TABLE 
(	
item_id						VARCHAR(40) NULL
,source_location_id			INTEGER NULL
,production_orders	    	INTEGER NULL
,qty_completed              INTEGER NULL
,minutes_worked 			INTEGER NULL
,actual_material_costs		DECIMAL(19,9) NULL
,actual_labor_costs			DECIMAL(19,9) NULL
,minutes_per_kit			DECIMAL(19,9) NULL
,material_cost_per_kit		DECIMAL(19,9) NULL
,labor_cost_per_kit     	DECIMAL(19,9) NULL
)
AS
BEGIN

DECLARE @ORDERS int;
DECLARE @QTY int;
DECLARE @MINUTES int;
DECLARE @MATERIAL DECIMAL(19,9);
DECLARE @LABOR DECIMAL(19,9);
DECLARE @MINUTES_PER_KIT DECIMAL(19,9);
DECLARE @MATERIAL_COST_PER_KIT DECIMAL(19,9);
DECLARE @LABOR_COST_PER_KIT DECIMAL(19,9);

DECLARE @ORDERS_SUB int;
DECLARE @QTY_SUB int;
DECLARE @MINUTES_SUB int;
DECLARE @MATERIAL_SUB DECIMAL(19,9);
DECLARE @LABOR_SUB DECIMAL(19,9);
DECLARE @MINUTES_PER_KIT_SUB DECIMAL(19,9);
DECLARE @MATERIAL_COST_PER_KIT_SUB DECIMAL(19,9);
DECLARE @LABOR_COST_PER_KIT_SUB DECIMAL(19,9);

DECLARE @SUBKITS int;

select 
  @ORDERS=count(*),
  @QTY=sum(dbo.p21_view_prod_order_line.qty_completed),
  @MINUTES=sum(dbo.p21_view_prod_order_line_comp_labor.hours_worked), 
  @MATERIAL=sum(dbo.p21_view_prod_order_line_cost.actual_material_cost),
  @LABOR=sum(dbo.p21_view_prod_order_line_cost.actual_labor_cost),
  @MINUTES_PER_KIT=sum(dbo.p21_view_prod_order_line_comp_labor.hours_worked)/sum(dbo.p21_view_prod_order_line.qty_completed),
  @MATERIAL_COST_PER_KIT=sum(dbo.p21_view_prod_order_line_cost.actual_material_cost)/sum(dbo.p21_view_prod_order_line.qty_completed),
  @LABOR_COST_PER_KIT=sum(dbo.p21_view_prod_order_line_cost.actual_labor_cost)/sum(dbo.p21_view_prod_order_line.qty_completed)
FROM dbo.p21_view_prod_order_hdr 
  INNER JOIN dbo.p21_view_prod_order_line
   ON dbo.p21_view_prod_order_hdr.prod_order_number = dbo.p21_view_prod_order_line.prod_order_number
  INNER JOIN dbo.p21_view_prod_order_line_cost
   ON (dbo.p21_view_prod_order_line.line_number = dbo.p21_view_prod_order_line_cost.line_number AND 
       dbo.p21_view_prod_order_line.prod_order_number = dbo.p21_view_prod_order_line_cost.prod_order_number)
  INNER JOIN dbo.p21_view_prod_order_line_component 
   ON (dbo.p21_view_prod_order_line.line_number = dbo.p21_view_prod_order_line_component.line_number AND 
       dbo.p21_view_prod_order_line.prod_order_number = dbo.p21_view_prod_order_line_component.prod_order_number)
  INNER JOIN dbo.p21_view_prod_order_line_comp_labor
   ON dbo.p21_view_prod_order_line_component.prod_order_line_component_uid = dbo.p21_view_prod_order_line_comp_labor.prod_order_line_component_uid
--  INNER JOIN dbo.p21_view_prod_pick_ticket_hdr 
--   ON dbo.p21_view_prod_order_line_cost.prod_order_number = dbo.p21_view_prod_pick_ticket_hdr.prod_order_number
--  INNER JOIN dbo.p21_view_prod_order_line_process 
--   ON dbo.p21_view_prod_order_line.prod_order_line_uid = dbo.p21_view_prod_order_line_process.prod_order_line_uid
WHERE dbo.p21_view_prod_order_line.item_id = @ITEM_ID AND
  dbo.p21_view_prod_order_line.end_date >= @START_DATE AND
  dbo.p21_view_prod_order_line.end_date <= @END_DATE AND
  dbo.p21_view_prod_order_line_comp_labor.hours_worked >= 0  AND 
  dbo.p21_view_prod_order_line.completed = 'Y' AND 
  dbo.p21_view_prod_order_line.cancel = 'N' AND
  dbo.p21_view_prod_order_hdr.cancel = 'N' AND
  dbo.p21_view_prod_order_hdr.source_location_id = @SOURCE_LOCATION_ID

--select @SUBKITS=count(*) from nutech_fn_kit_subkits(1,@ITEM_ID);
select @SUBKITS=count(*)
from assembly_line
       inner join inv_mast on
         assembly_line.inv_mast_uid = inv_mast.inv_mast_uid
where inv_mast.item_id = @ITEM_ID
  and assembly_line.delete_flag <> 'Y'
  and assembly_line.sub_assembly = 'Y'

IF (@SUBKITS <= 0)
  BEGIN
    SET @MATERIAL_SUB = 0;
    SET @LABOR_SUB = 0;
	SET @MINUTES_SUB = 0;
    SET @MINUTES_PER_KIT_SUB=0;
    SET @MATERIAL_COST_PER_KIT_SUB=0;
    SET @LABOR_COST_PER_KIT_SUB=0;
  END
ELSE
  BEGIN
select
  @ORDERS_SUB=count(*),
  @QTY_SUB=sum(dbo.p21_view_prod_order_line.qty_completed),
  @MINUTES_SUB=sum(dbo.p21_view_prod_order_line_comp_labor.hours_worked), 
  @MATERIAL_SUB=sum(dbo.p21_view_prod_order_line_cost.actual_material_cost),
  @LABOR_SUB=sum(dbo.p21_view_prod_order_line_cost.actual_labor_cost),
  @MINUTES_PER_KIT_SUB=sum(dbo.p21_view_prod_order_line_comp_labor.hours_worked)/@QTY,
  @MATERIAL_COST_PER_KIT_SUB=sum(dbo.p21_view_prod_order_line_cost.actual_material_cost)/@QTY,
  @LABOR_COST_PER_KIT_SUB=sum(dbo.p21_view_prod_order_line_cost.actual_labor_cost)/@QTY
FROM dbo.p21_view_prod_order_hdr 
  INNER JOIN dbo.p21_view_prod_order_line 
   ON dbo.p21_view_prod_order_hdr.prod_order_number = dbo.p21_view_prod_order_line.prod_order_number
  INNER JOIN dbo.p21_view_prod_order_line_cost 
   ON (dbo.p21_view_prod_order_line.line_number = dbo.p21_view_prod_order_line_cost.line_number AND 
       dbo.p21_view_prod_order_line.prod_order_number = dbo.p21_view_prod_order_line_cost.prod_order_number)
  INNER JOIN dbo.p21_view_prod_order_line_component 
   ON (dbo.p21_view_prod_order_line.line_number = dbo.p21_view_prod_order_line_component.line_number AND 
       dbo.p21_view_prod_order_line.prod_order_number = dbo.p21_view_prod_order_line_component.prod_order_number)
  INNER JOIN dbo.p21_view_prod_order_line_comp_labor
   ON dbo.p21_view_prod_order_line_component.prod_order_line_component_uid = dbo.p21_view_prod_order_line_comp_labor.prod_order_line_component_uid
  INNER JOIN dbo.p21_view_prod_pick_ticket_hdr 
   ON dbo.p21_view_prod_order_line_cost.prod_order_number = dbo.p21_view_prod_pick_ticket_hdr.prod_order_number
  INNER JOIN dbo.p21_view_prod_order_line_process 
   ON dbo.p21_view_prod_order_line.prod_order_line_uid = dbo.p21_view_prod_order_line_process.prod_order_line_uid
WHERE dbo.p21_view_prod_order_line.item_id in (select item_id from nutech_fn_kit_subkits(1,@ITEM_ID)) AND
  dbo.p21_view_prod_order_line.end_date >= @START_DATE AND
  dbo.p21_view_prod_order_line.end_date <= @END_DATE AND
  dbo.p21_view_prod_order_line_comp_labor.hours_worked >= 0  AND 
  dbo.p21_view_prod_order_line.completed = 'Y' AND 
  dbo.p21_view_prod_order_line.cancel = 'N' AND
  dbo.p21_view_prod_order_hdr.cancel = 'N' AND
  dbo.p21_view_prod_order_hdr.source_location_id = @SOURCE_LOCATION_ID

END

insert @kit_items
select @ITEM_ID, @SOURCE_LOCATION_ID, @ORDERS, @QTY, @MINUTES+@MINUTES_SUB, @MATERIAL-@LABOR_SUB, @LABOR+@LABOR_SUB,
       @MINUTES_PER_KIT+@MINUTES_PER_KIT_SUB, @MATERIAL_COST_PER_KIT-@LABOR_COST_PER_KIT_SUB, @LABOR_COST_PER_KIT+@LABOR_COST_PER_KIT_SUB
return

END
GO


