USE [P21]
GO

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

SET QUOTED_IDENTIFIER ON
GO

-- ===================================================
-- Author:		Fred Rappuhn, NuTech Services, LLC
-- Create date: 05/30/2021
-- Description:	Return all items in kit including sub kits
-- ===================================================
CREATE FUNCTION [dbo].[nutech_fn_kit_items_w_costs_report] (
	@START_ITEM_ID as varchar(40),
	@END_ITEM_ID as varchar(40),
	@START_LOCATION_ID as int,
	@END_LOCATION_ID as int,
	@START_DATE as datetime,
	@END_DATE as datetime,
	@CUSTOMER_ID as int)
RETURNS @kit_items_report TABLE 
(	
quantity					INTEGER NULL
,kit_item_id				VARCHAR(40) NULL
,item_id					VARCHAR(40) NULL
,moving_average_cost		DECIMAL(19,9) NULL
)
AS
BEGIN

DECLARE @ITEM_ID varchar(100);
DECLARE @LOCATION_ID integer;

DECLARE db_cursor CURSOR FOR
select distinct p21_view_prod_order_line.item_id, dbo.p21_view_prod_order_hdr.source_location_id
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.inv_mast_uid in (
    select dbo.p21_view_assembly_line.inv_mast_uid 
    from dbo.p21_view_assembly_line 
	    LEFT JOIN assembly_hdr_ud on
	        dbo.p21_view_assembly_line.inv_mast_uid = assembly_hdr_ud.inv_mast_uid
    where --dbo.p21_view_assembly_line.sub_assembly = 'Y' AND
          dbo.p21_view_assembly_line.delete_flag = 'N' AND
		  (	( @CUSTOMER_ID = 0 ) OR
		    ( @CUSTOMER_ID > 0 and assembly_hdr_ud.primary_customer_id = @CUSTOMER_ID )
		  )
  ) AND
  dbo.p21_view_prod_order_line.item_id >= @START_ITEM_ID AND
  dbo.p21_view_prod_order_line.item_id <= @END_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.completed = 'Y' AND 
  dbo.p21_view_prod_order_line.cancel = 'N' AND 
  dbo.p21_view_prod_order_line_comp_labor.hours_worked >= 0  AND
  dbo.p21_view_prod_order_hdr.cancel = 'N' AND
  dbo.p21_view_prod_order_hdr.source_location_id >= @START_LOCATION_ID AND
  dbo.p21_view_prod_order_hdr.source_location_id <= @END_LOCATION_ID 
order by p21_view_prod_order_line.item_id, dbo.p21_view_prod_order_hdr.source_location_id
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @ITEM_ID, @LOCATION_ID;

WHILE @@FETCH_STATUS = 0
BEGIN

  insert into @kit_items_report
    select sum(quantity), @ITEM_ID, item_id, moving_average_cost
	from nutech_fn_kit_items_w_costs(1,@ITEM_ID,@LOCATION_ID,@START_DATE,@END_DATE)
	group by item_id, moving_average_cost
	order by item_id, moving_average_cost

  FETCH NEXT FROM db_cursor INTO @ITEM_ID, @LOCATION_ID;

END

CLOSE db_cursor

DEALLOCATE db_cursor


--insert into @kit_items_report
--select dbo.p21_view_prod_order_line.item_id, dbo.p21_view_prod_order_hdr.source_location_id,
-- count(*) as production_orders,
-- sum(dbo.p21_view_prod_order_line.qty_completed) as qty_completed,
-- sum(dbo.p21_view_prod_order_line_comp_labor.hours_worked) as minutes_worked, 
-- sum(dbo.p21_view_prod_order_line_cost.actual_material_cost) as actual_material_cost,
-- sum(dbo.p21_view_prod_order_line_cost.actual_labor_cost) as actual_labor_cost,
-- sum(dbo.p21_view_prod_order_line_comp_labor.hours_worked)/sum(dbo.p21_view_prod_order_line.qty_completed) as minutes_per_kit,
-- sum(dbo.p21_view_prod_order_line_cost.actual_material_cost)/sum(dbo.p21_view_prod_order_line.qty_completed) as material_cost_per_kit,
-- sum(dbo.p21_view_prod_order_line_cost.actual_labor_cost)/sum(dbo.p21_view_prod_order_line.qty_completed) as labor_cost_per_kit
--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.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.inv_mast_uid in (
--    select dbo.p21_view_assembly_line.inv_mast_uid 
--    from dbo.p21_view_assembly_line
--	    LEFT JOIN assembly_hdr_ud on
--	        dbo.p21_view_assembly_line.inv_mast_uid = assembly_hdr_ud.inv_mast_uid
--    where dbo.p21_view_assembly_line.sub_assembly = 'N' AND
--          dbo.p21_view_assembly_line.delete_flag = 'N' AND
--		  (	( @CUSTOMER_ID = 0 ) OR
--		    ( @CUSTOMER_ID > 0 and assembly_hdr_ud.primary_customer_id = @CUSTOMER_ID )
--		  )
--  ) AND
--  dbo.p21_view_prod_order_hdr.cancel ='N' AND
--  dbo.p21_view_prod_order_hdr.source_location_id >= @START_LOCATION_ID AND
---  dbo.p21_view_prod_order_hdr.source_location_id <= @END_LOCATION_ID AND
--  dbo.p21_view_prod_order_line.item_id >= @START_ITEM_ID AND
--  dbo.p21_view_prod_order_line.item_id <= @END_ITEM_ID
--  group by dbo.p21_view_prod_order_line.item_id, dbo.p21_view_prod_order_hdr.source_location_id
--  order by dbo.p21_view_prod_order_line.item_id, dbo.p21_view_prod_order_hdr.source_location_id







return

END
GO


