USE [P21]
GO

/****** Object:  UserDefinedFunction [dbo].[nutech_fn_item_fill_report]    Script Date: 9/23/2024 6:30:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- ===================================================
-- Author:		Fred Rappuhn, NuTech Services, LLC
-- Create date: 04/26/2024
-- Description:	
-- ===================================================
CREATE FUNCTION [dbo].[nutech_fn_item_fill_report] (
    @company_id  VARCHAR(8) = '10',
	@DAYS_TO_CREATE_PT int = 6,
    @START_ORDER_DATE_CREATED datetime = '1/1/2024 00:00:00',
    @END_ORDER_DATE_CREATED datetime = '4/28/2024 23:59:59',
    @START_LOCATION_ID decimal(19,0) = 0,
    @END_LOCATION_ID decimal(19,0) = 9999,
    @START_ORDER_NO varchar(12) = '',
    @END_ORDER_NO varchar(12) = 'ZZZZZZZZZZZZ',
    @PARTIAL_FILL varchar(1) = 'Y',
	@MIN_INV_MAX decimal(19,9) = 1
)
RETURNS @nutech_fn_item_fill_report TABLE 
(	
    oe_hdr_order_no [varchar](8) NULL,
    oe_hdr_date_created [datetime] NULL,
    oe_hdr_location_id [decimal](19, 0) NULL,
    oe_hdr_requested_date [datetime] NULL,
    oe_line_date_created [datetime] NULL,
    oe_line_line_no [decimal](19, 0) NULL,
    oe_line_inv_mast_uid [int] NULL,
    inv_mast_item_id [varchar](40) NULL,
    oe_line_qty_ordered [decimal](19,9) NULL,
    oe_line_source_loc_id [decimal](19, 0) NULL,
    oe_line_ship_loc_id [decimal](19, 0) NULL,
    oe_pick_ticket_pick_ticket_no [decimal](19, 0) NULL,
    oe_pick_ticket_date_created [datetime] NULL,
    oe_pick_ticket_location_id [decimal](19,0) NULL,
    oe_pick_ticket_detail_line_number [decimal](19, 0) NULL,
    oe_pick_ticket_detail_unit_quantity [decimal](19,9) NULL,
    oe_pick_ticket_detail_qty_to_pick [decimal](19,9) NULL,
    first_pick_ticket_date_created [datetime] NULL,
    oe_pick_ticket_ship_date [datetime] NULL,
	expected_date_to_create_pt [datetime] NULL,
	source_inv_loc_inv_min [decimal](19, 9) NULL,
	source_inv_loc_inv_max [decimal](19, 9) NULL,
	hours_to_create_pt_from_expected_pt_date [int] NULL,
	hours_to_create_pt_using_order_date [int] NULL,
	hours_after_requested_date [int] NULL,
	hours_after_first_pick_ticket [int] NULL,
	count_as_filled [varchar](1) NULL,
	INDEX idx_nutech_fn_item_fill_report (oe_line_inv_mast_uid ASC, inv_mast_item_id ASC)
)
AS
BEGIN 
-- hours_to_create_pt_from_expected_pt_date <= 20
-- hours_after_requested_date <= 0
-- hours_after_first_pick_ticket < 12
	-- Populate table   
	INSERT INTO @nutech_fn_item_fill_report  
select oe_hdr.order_no,
       oe_hdr.date_created oe_hdr_date_created,
       oe_hdr.location_id,
	   oe_hdr.requested_date oe_hdr_requested_date,
	   oe_line.date_created oe_line_date_created,
	   oe_line.line_no oe_line_line_no,
	   oe_line.inv_mast_uid oe_line_inv_mast_uid,
	   inv_mast.item_id,
	   oe_line.qty_ordered,
	   oe_line.source_loc_id,
	   oe_line.ship_loc_id, 
	   oe_pick_ticket.pick_ticket_no,
	   oe_pick_ticket.date_created oe_pick_ticket_date_created,
	   oe_pick_ticket.location_id oe_pick_ticket_location_id,
	   oe_pick_ticket_detail.line_number pt_line_number,
	   oe_pick_ticket_detail.unit_quantity,
	   oe_pick_ticket_detail.qty_to_pick,
	   first_pick_ticket.first_pick_ticket_date_created,
	   oe_pick_ticket.ship_date, 
	   CASE WHEN (dateadd(day, (-1)*@DAYS_TO_CREATE_PT, oe_hdr.requested_date) < oe_hdr.date_created)
	             THEN oe_hdr.date_created
				 ELSE dateadd(day, (-1)*@DAYS_TO_CREATE_PT, oe_hdr.requested_date)
	   END as expected_date_to_create_pt,
	   isnull(source_inv_loc.inv_min,0) source_inv_loc_inv_min,
	   isnull(source_inv_loc.inv_max,0) source_inv_loc_inv_max,
	   CASE WHEN (dateadd(day, (-1)*@DAYS_TO_CREATE_PT, oe_hdr.requested_date) < oe_hdr.date_created)
	             THEN datediff(HOUR, oe_hdr.date_created, oe_pick_ticket_detail.date_created)
				 ELSE datediff(HOUR, dateadd(day, (-1)*@DAYS_TO_CREATE_PT, oe_hdr.requested_date), oe_pick_ticket_detail.date_created)
	   END as hours_to_create_pt_from_expected_pt_date,
	   datediff(HOUR, oe_line.date_created, oe_pick_ticket_detail.date_created) hours_to_create_pt_using_order_date,
	   datediff(HOUR, oe_hdr.requested_date, oe_pick_ticket_detail.date_created) hours_after_requested_date,
	   datediff(HOUR, first_pick_ticket.first_pick_ticket_date_created, oe_pick_ticket_detail.date_created) hours_after_first_pick_ticket,
	   'N' count_as_filled
from   oe_hdr with (nolock)
       left join oe_line with (nolock) on 
         oe_hdr.order_no = oe_line.order_no
       left join oe_pick_ticket_detail with (nolock) on
		 oe_line.line_no = oe_pick_ticket_detail.oe_line_no and
		 oe_line.inv_mast_uid = oe_pick_ticket_detail.inv_mast_uid
       left join oe_pick_ticket with (nolock) on
         oe_pick_ticket.pick_ticket_no = oe_pick_ticket_detail.pick_ticket_no and
	     oe_hdr.order_no = oe_pick_ticket.order_no
	   left join inv_mast with (nolock) on
	     oe_line.inv_mast_uid = inv_mast.inv_mast_uid
       left join (select oe_pick_ticket.order_no, min(oe_pick_ticket.date_created) first_pick_ticket_date_created
	         from oe_pick_ticket with (nolock)
	         where oe_pick_ticket.delete_flag = 'N'
             group by oe_pick_ticket.order_no) first_pick_ticket on oe_hdr.order_no = first_pick_ticket.order_no
	   left join (select inv_loc.inv_mast_uid, inv_loc.location_id,
	                     isnull(inv_loc.inv_min,0) inv_min,
						 isnull(inv_loc.inv_max,0) inv_max
	              from inv_loc with (nolock)) source_inv_loc on
	     oe_line.source_loc_id = source_inv_loc.location_id and
		 oe_line.inv_mast_uid = source_inv_loc.inv_mast_uid
where oe_pick_ticket.delete_flag = 'N' and
      oe_hdr.date_created >= @START_ORDER_DATE_CREATED and
      oe_hdr.date_created <= @END_ORDER_DATE_CREATED and
	  oe_hdr.order_no >= @START_ORDER_NO and
	  oe_hdr.order_no <= @END_ORDER_NO and
	  oe_hdr.location_id >= @START_LOCATION_ID and
	  oe_hdr.location_id <= @END_LOCATION_ID and
	  oe_line.scheduled = 'N' and -- not sure if needed
	  oe_hdr.completed = 'Y' and -- not needed
	  oe_hdr.projected_order = 'N' and -- not needed
      oe_hdr.delete_flag = 'N' and
      oe_hdr.approved = 'Y' and
      oe_line.delete_flag = 'N' and
	  oe_line.cancel_flag = 'N' and
	  oe_pick_ticket.delete_flag = 'N' and
	  source_inv_loc.inv_max >= @MIN_INV_MAX
order by oe_hdr.order_no, oe_line.line_no, oe_pick_ticket.pick_ticket_no, oe_pick_ticket_detail.line_number;

  update @nutech_fn_item_fill_report
  set expected_date_to_create_pt = first_pick_ticket_date_created,
      hours_to_create_pt_from_expected_pt_date = datediff(HOUR, first_pick_ticket_date_created, oe_pick_ticket_date_created)
  where first_pick_ticket_date_created is not null and
        first_pick_ticket_date_created < expected_date_to_create_pt 

  delete @nutech_fn_item_fill_report
  where (expected_date_to_create_pt >= getdate() and
        first_pick_ticket_date_created is NULL);

  update @nutech_fn_item_fill_report
  set count_as_filled = 'Y'
  where hours_to_create_pt_from_expected_pt_date <= 10 OR
--        hours_after_requested_date <= 0 OR
        hours_after_first_pick_ticket < 6;

  update @nutech_fn_item_fill_report
  set count_as_filled = 'N'
  where @PARTIAL_FILL = 'N' and
        oe_line_qty_ordered > oe_pick_ticket_detail_unit_quantity;

  with NTFR_CTE AS (
     select *,
	        ROW_NUMBER() OVER (PARTITION BY oe_hdr_order_no, oe_line_inv_mast_uid order by oe_pick_ticket_date_created) AS row_num
     from @nutech_fn_item_fill_report
  )
  update NTFR_CTE
  set count_as_filled = 'S'
  where row_num > 1;

return

END

GO


