USE [P21]
GO

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

SET QUOTED_IDENTIFIER ON
GO

-- ===================================================
-- Author:		Fred Rappuhn, NuTech Services, LLC
-- Create date: 02/03/2022
-- Description:	
-- ===================================================
CREATE FUNCTION [dbo].[nutech_fn_branch_wwms_performance_report] (
	@START_DATE as datetime,
	@END_DATE as datetime,
	@START_LOCATION_ID as int,
	@END_LOCATION_ID as int)
RETURNS @branch_performance_report TABLE 
(	
location_id							INTEGER NULL
,total_po_lines_recd_other			INTEGER NULL
,total_po_lines_recd_wwms			INTEGER NULL
,total_po_lines_recd				INTEGER NULL
,total_po_recd						INTEGER NULL
,total_xfer_lines_recd_wwms			INTEGER NULL
,total_xfer_lines_recd				INTEGER NULL
,total_xfer_recd					INTEGER NULL
,total_xfer_lines_picked_wwms		INTEGER NULL
,total_xfer_lines_picked			INTEGER NULL
,total_xfer_picked					INTEGER NULL
,total_order_lines_picked_other		INTEGER NULL
,total_order_lines_picked_wwms		INTEGER NULL
,total_order_lines_picked			INTEGER NULL
,total_order_picked					INTEGER NULL
)
AS
BEGIN

DECLARE @ITEM_UID int;
DECLARE @INV_MAST_UID int;
DECLARE @QTY int;
DECLARE @TEMP int;

INSERT INTO @branch_performance_report
  select p21_view_location.location_id, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
  from p21_view_branch left join p21_view_location on
       p21_view_branch.branch_id = p21_view_location.default_branch_id
  where p21_view_branch.delete_flag = 'N'
    and p21_view_location.delete_flag = 'N'
order by p21_view_location.location_id


UPDATE @branch_performance_report
  set total_po_lines_recd_wwms = t3.total_po_lines_recd_wwms
  from @branch_performance_report T1 INNER JOIN
(select T2.location_id, count(*) total_po_lines_recd_wwms
from
(select p21_view_wireless_trans_audit_hdr.wireless_transaction_number,
        p21_view_wireless_trans_audit_hdr.location_id,
        p21_view_wireless_trans_audit_line.transaction_line_number
from p21_view_wireless_trans_audit_hdr inner join p21_view_inventory_receipts_hdr on
	     p21_view_wireless_trans_audit_hdr.wireless_transaction_number = p21_view_inventory_receipts_hdr.receipt_number
	 and p21_view_inventory_receipts_hdr.approved = 'Y'
	 inner join p21_view_inventory_receipts_line on
	     p21_view_inventory_receipts_hdr.receipt_number = p21_view_inventory_receipts_line.receipt_number
	 inner join p21_view_wireless_trans_audit_line on
         p21_view_wireless_trans_audit_hdr.wireless_trans_audit_hdr_uid = p21_view_wireless_trans_audit_line.wireless_trans_audit_hdr_uid
	 and p21_view_inventory_receipts_line.line_number = p21_view_wireless_trans_audit_line.transaction_line_number
where p21_view_wireless_trans_audit_hdr.wireless_transaction_type_cd in (1521)
  AND p21_view_wireless_trans_audit_hdr.date_created >= @START_DATE
  AND p21_view_wireless_trans_audit_hdr.date_created <= @END_DATE
  AND p21_view_wireless_trans_audit_hdr.location_id >= @START_LOCATION_ID 
  AND p21_view_wireless_trans_audit_hdr.location_id <= @END_LOCATION_ID
  AND p21_view_wireless_trans_audit_hdr.wireless_transaction_number > 0
group by p21_view_wireless_trans_audit_hdr.wireless_transaction_number,
         p21_view_wireless_trans_audit_hdr.location_id,
		 p21_view_wireless_trans_audit_line.transaction_line_number ) AS T2
group by T2.location_id) AS T3
ON T1.location_id = T3.location_id


UPDATE @branch_performance_report
  set total_po_lines_recd = t2.total_po_lines_recd
  from @branch_performance_report T1 INNER JOIN
(select p21_view_po_hdr.location_id, count(*) total_po_lines_recd
from p21_view_inventory_receipts_line
   INNER JOIN p21_view_inventory_receipts_hdr ON
         p21_view_inventory_receipts_hdr.receipt_number = p21_view_inventory_receipts_line.receipt_number
   INNER JOIN p21_view_po_hdr ON
         p21_view_po_hdr.po_no = p21_view_inventory_receipts_hdr.po_number
   INNER JOIN p21_view_po_line ON
         p21_view_po_line.po_no = p21_view_po_hdr.po_no 
     AND p21_view_po_line.line_no = p21_view_inventory_receipts_line.po_line_number
where p21_view_inventory_receipts_hdr.approved = 'Y'
  AND p21_view_inventory_receipts_hdr.receipt_type = 'P'
  AND p21_view_inventory_receipts_hdr.delete_flag = 'N'
  AND p21_view_inventory_receipts_line.qty_received <> 0
  AND p21_view_inventory_receipts_hdr.date_created >= @START_DATE
  AND p21_view_inventory_receipts_hdr.date_created <= @END_DATE
  AND p21_view_po_hdr.location_id >= @START_LOCATION_ID 
  AND p21_view_po_hdr.location_id <= @END_LOCATION_ID
group by p21_view_po_hdr.location_id) AS T2
ON T1.location_id = T2.location_id


UPDATE @branch_performance_report
  set total_po_lines_recd_other = t2.total_po_lines_recd_other
  from @branch_performance_report T1 INNER JOIN
(select p21_view_po_hdr.location_id, count(*) total_po_lines_recd_other
from p21_view_inventory_receipts_line
   INNER JOIN p21_view_inventory_receipts_hdr ON
         p21_view_inventory_receipts_hdr.receipt_number = p21_view_inventory_receipts_line.receipt_number
   INNER JOIN p21_view_po_hdr ON
         p21_view_po_hdr.po_no = p21_view_inventory_receipts_hdr.po_number
   INNER JOIN p21_view_po_line ON
         p21_view_po_line.po_no = p21_view_po_hdr.po_no 
     AND p21_view_po_line.line_no = p21_view_inventory_receipts_line.po_line_number
where p21_view_inventory_receipts_hdr.approved = 'Y'
  AND p21_view_inventory_receipts_hdr.receipt_type = 'P'
  AND p21_view_inventory_receipts_hdr.delete_flag = 'N'
  AND p21_view_inventory_receipts_line.qty_received <> 0
  AND p21_view_inventory_receipts_hdr.date_created >= @START_DATE
  AND p21_view_inventory_receipts_hdr.date_created <= @END_DATE
  AND p21_view_po_hdr.location_id >= @START_LOCATION_ID 
  AND p21_view_po_hdr.location_id <= @END_LOCATION_ID
  AND p21_view_po_hdr.po_type in ('R','X')
group by p21_view_po_hdr.location_id) AS T2
ON T1.location_id = T2.location_id


UPDATE @branch_performance_report
  set total_po_recd = t2.total_po_recd
  from @branch_performance_report T1 INNER JOIN
(select p21_view_po_hdr.location_id, count(*) total_po_recd
from p21_view_po_hdr, p21_view_inventory_receipts_hdr
where p21_view_inventory_receipts_hdr.date_created >= @START_DATE and 
      p21_view_inventory_receipts_hdr.date_created <= @END_DATE
  and p21_view_po_hdr.po_no = p21_view_inventory_receipts_hdr.po_number
--  and p21_view_po_hdr.cancel_flag = 'N'  contains NULL
  and p21_view_inventory_receipts_hdr.approved = 'Y'
  and p21_view_inventory_receipts_hdr.receipt_type = 'P'
  and p21_view_inventory_receipts_hdr.delete_flag = 'N'
  and p21_view_po_hdr.location_id >= @START_LOCATION_ID
  and p21_view_po_hdr.location_id <= @END_LOCATION_ID
group by p21_view_po_hdr.location_id) AS T2
ON T1.location_id = T2.location_id


UPDATE @branch_performance_report
  set total_xfer_lines_recd_wwms = t3.total_xfer_lines_recd_wwms
  from @branch_performance_report T1 INNER JOIN
(select T2.location_id, count(*) total_xfer_lines_recd_wwms
from
(select p21_view_wireless_trans_audit_hdr.wireless_transaction_number,
        p21_view_wireless_trans_audit_hdr.location_id,
        p21_view_wireless_trans_audit_line.transaction_line_number
from p21_view_wireless_trans_audit_hdr inner join p21_view_inventory_receipts_hdr on
	     p21_view_wireless_trans_audit_hdr.wireless_transaction_number = p21_view_inventory_receipts_hdr.receipt_number
	 and p21_view_inventory_receipts_hdr.approved = 'Y'
	 inner join p21_view_inventory_receipts_line on
	     p21_view_inventory_receipts_hdr.receipt_number = p21_view_inventory_receipts_line.receipt_number
	 inner join p21_view_wireless_trans_audit_line on
         p21_view_wireless_trans_audit_hdr.wireless_trans_audit_hdr_uid = p21_view_wireless_trans_audit_line.wireless_trans_audit_hdr_uid
	 and p21_view_inventory_receipts_line.line_number = p21_view_wireless_trans_audit_line.transaction_line_number
where p21_view_wireless_trans_audit_hdr.wireless_transaction_type_cd in (1523)
  AND p21_view_wireless_trans_audit_hdr.date_created >= @START_DATE
  AND p21_view_wireless_trans_audit_hdr.date_created <= @END_DATE
  AND p21_view_wireless_trans_audit_hdr.location_id >= @START_LOCATION_ID 
  AND p21_view_wireless_trans_audit_hdr.location_id <= @END_LOCATION_ID
  AND p21_view_wireless_trans_audit_hdr.wireless_transaction_number > 0
group by p21_view_wireless_trans_audit_hdr.wireless_transaction_number,
         p21_view_wireless_trans_audit_hdr.location_id,
		 p21_view_wireless_trans_audit_line.transaction_line_number ) AS T2
group by T2.location_id) AS T3
ON T1.location_id = T3.location_id


UPDATE @branch_performance_report
  set total_xfer_lines_recd = t2.total_xfer_lines_recd
  from @branch_performance_report T1 INNER JOIN
(select p21_view_transfer_hdr.to_location_id location_id, count(*) total_xfer_lines_recd
from  p21_view_inventory_receipts_line
   INNER JOIN p21_view_inventory_receipts_hdr ON 
         p21_view_inventory_receipts_hdr.receipt_number = p21_view_inventory_receipts_line.receipt_number
   INNER JOIN p21_view_transfer_shipment_hdr ON 
         p21_view_transfer_shipment_hdr.transfer_shipment_no = p21_view_inventory_receipts_hdr.po_number
   INNER JOIN p21_view_transfer_hdr ON 
         p21_view_transfer_hdr.transfer_no = p21_view_transfer_shipment_hdr.transfer_no
   INNER JOIN p21_view_transfer_shipment_line ON 
         p21_view_transfer_shipment_line.transfer_shipment_hdr_uid = p21_view_transfer_shipment_hdr.transfer_shipment_hdr_uid
     AND p21_view_transfer_shipment_line.transfer_line_no = p21_view_inventory_receipts_line.po_line_number
   INNER JOIN p21_view_transfer_line ON 
         p21_view_transfer_line.transfer_no = p21_view_transfer_hdr.transfer_no
     AND p21_view_transfer_line.line_no = p21_view_transfer_shipment_line.transfer_line_no
where  p21_view_inventory_receipts_hdr.approved = 'Y'
   AND p21_view_inventory_receipts_hdr.receipt_type = 'T'
   AND p21_view_inventory_receipts_hdr.delete_flag = 'N'
   AND p21_view_inventory_receipts_line.qty_received <> 0
   AND p21_view_inventory_receipts_hdr.date_created >= @START_DATE
   AND p21_view_inventory_receipts_hdr.date_created <= @END_DATE
   AND p21_view_transfer_hdr.to_location_id >= @START_LOCATION_ID 
   AND p21_view_transfer_hdr.to_location_id <= @END_LOCATION_ID
group by p21_view_transfer_hdr.to_location_id) AS T2
ON T1.location_id = T2.location_id

UPDATE @branch_performance_report
  set total_xfer_recd = t2.total_xfer_recd
  from @branch_performance_report T1 INNER JOIN
(select p21_view_transfer_hdr.to_location_id location_id, count(*) total_xfer_recd
from  p21_view_inventory_receipts_hdr
   INNER JOIN p21_view_transfer_shipment_hdr ON 
         p21_view_transfer_shipment_hdr.transfer_shipment_no = p21_view_inventory_receipts_hdr.po_number
   INNER JOIN p21_view_transfer_hdr ON 
         p21_view_transfer_hdr.transfer_no = p21_view_transfer_shipment_hdr.transfer_no
where  p21_view_inventory_receipts_hdr.approved = 'Y'
   AND p21_view_inventory_receipts_hdr.receipt_type = 'T'
   AND p21_view_inventory_receipts_hdr.delete_flag = 'N'
   AND p21_view_inventory_receipts_hdr.date_created >= @START_DATE
   AND p21_view_inventory_receipts_hdr.date_created <= @END_DATE
   AND p21_view_transfer_hdr.to_location_id >= @START_LOCATION_ID 
   AND p21_view_transfer_hdr.to_location_id <= @END_LOCATION_ID
group by p21_view_transfer_hdr.to_location_id) AS T2
ON T1.location_id = T2.location_id


UPDATE @branch_performance_report
  set total_xfer_lines_picked_wwms = t2.total_xfer_lines_picked_wwms
  from @branch_performance_report T1 INNER JOIN
(select p21_view_transfer_hdr.from_location_id location_id, count(*) total_xfer_lines_picked_wwms
from  p21_view_transfer_hdr
   INNER JOIN p21_view_transfer_shipment_hdr ON 
      p21_view_transfer_hdr.transfer_no = p21_view_transfer_shipment_hdr.transfer_no
  and p21_view_transfer_hdr.from_location_id >= @START_LOCATION_ID
  and p21_view_transfer_hdr.from_location_id <= @END_LOCATION_ID
  and p21_view_transfer_hdr.shipping_date >= @START_DATE
  and p21_view_transfer_hdr.shipping_date <= @END_DATE
   INNER JOIN p21_view_transfer_shipment_line ON 
         p21_view_transfer_shipment_line.transfer_shipment_hdr_uid = p21_view_transfer_shipment_hdr.transfer_shipment_hdr_uid
   INNER JOIN p21_view_transfer_line ON 
         p21_view_transfer_line.transfer_no = p21_view_transfer_hdr.transfer_no
		 and p21_view_transfer_line.line_no = p21_view_transfer_shipment_line.transfer_line_no
 where p21_view_transfer_hdr.transfer_no in (select p21_view_wireless_trans_audit_hdr.wireless_transaction_number
from p21_view_wireless_trans_audit_hdr inner join p21_view_wireless_trans_audit_line on
     p21_view_wireless_trans_audit_hdr.wireless_trans_audit_hdr_uid = p21_view_wireless_trans_audit_line.wireless_trans_audit_hdr_uid
where p21_view_wireless_trans_audit_hdr.wireless_transaction_type_cd in (1882)
  AND p21_view_wireless_trans_audit_hdr.wireless_transaction_number > 0
group by p21_view_wireless_trans_audit_hdr.wireless_transaction_number,
         p21_view_wireless_trans_audit_hdr.location_id,
		 p21_view_wireless_trans_audit_line.transaction_line_number)
group by p21_view_transfer_hdr.from_location_id) AS T2
ON T1.location_id = T2.location_id

UPDATE @branch_performance_report
  set total_xfer_lines_picked = t2.total_xfer_lines_picked
  from @branch_performance_report T1 INNER JOIN
(select p21_view_transfer_hdr.from_location_id location_id, count(*) total_xfer_lines_picked
from  p21_view_transfer_hdr
   INNER JOIN p21_view_transfer_shipment_hdr ON 
      p21_view_transfer_hdr.transfer_no = p21_view_transfer_shipment_hdr.transfer_no
  and p21_view_transfer_hdr.from_location_id >= @START_LOCATION_ID
  and p21_view_transfer_hdr.from_location_id <= @END_LOCATION_ID
  and p21_view_transfer_hdr.shipping_date >= @START_DATE
  and p21_view_transfer_hdr.shipping_date <= @END_DATE
   INNER JOIN p21_view_transfer_shipment_line ON 
         p21_view_transfer_shipment_line.transfer_shipment_hdr_uid = p21_view_transfer_shipment_hdr.transfer_shipment_hdr_uid
   INNER JOIN p21_view_transfer_line ON 
         p21_view_transfer_line.transfer_no = p21_view_transfer_hdr.transfer_no
     AND p21_view_transfer_line.line_no = p21_view_transfer_shipment_line.transfer_line_no
group by p21_view_transfer_hdr.from_location_id) AS T2
ON T1.location_id = T2.location_id


UPDATE @branch_performance_report
  set total_xfer_picked = t2.total_xfer_picked
  from @branch_performance_report T1 INNER JOIN
(select p21_view_transfer_hdr.from_location_id location_id, count(*) total_xfer_picked
from  p21_view_transfer_hdr
   INNER JOIN p21_view_transfer_shipment_hdr ON 
      p21_view_transfer_hdr.transfer_no = p21_view_transfer_shipment_hdr.transfer_no
  and p21_view_transfer_hdr.from_location_id >= @START_LOCATION_ID
  and p21_view_transfer_hdr.from_location_id <= @END_LOCATION_ID
  and p21_view_transfer_hdr.shipping_date >= @START_DATE
  and p21_view_transfer_hdr.shipping_date <= @END_DATE
group by p21_view_transfer_hdr.from_location_id) AS T2
ON T1.location_id = T2.location_id


UPDATE @branch_performance_report
  set total_order_lines_picked_other = t2.total_order_lines_picked_other
  from @branch_performance_report T1 INNER JOIN
(select p21_view_oe_pick_ticket.location_id, count(*) total_order_lines_picked_other
from p21_view_oe_pick_ticket INNER JOIN p21_view_oe_hdr ON 
    p21_view_oe_hdr.order_no = p21_view_oe_pick_ticket.order_no
	inner join p21_view_oe_pick_ticket_detail on
	p21_view_oe_pick_ticket.pick_ticket_no = p21_view_oe_pick_ticket_detail.pick_ticket_no
where
   p21_view_oe_pick_ticket.oe_pick_ticket_type_cd IS NULL
   and (p21_view_oe_pick_ticket.front_counter = 'Y')
   and p21_view_oe_pick_ticket.delete_flag = 'N'
   and p21_view_oe_pick_ticket.auxiliary = 'N'
   and p21_view_oe_hdr.rma_flag = 'N'
   and p21_view_oe_pick_ticket.direct_shipment = 'N'
   and p21_view_oe_pick_ticket.company_id = '10' 
   and p21_view_oe_pick_ticket.confirmable_row_status_flag in (1979,1980)  -- P21 Ignores 1979 
   and p21_view_oe_pick_ticket.location_id >= @START_LOCATION_ID 
   and p21_view_oe_pick_ticket.location_id <= @END_LOCATION_ID
   and p21_view_oe_pick_ticket.ship_date >= @START_DATE
   and p21_view_oe_pick_ticket.ship_date <= @END_DATE
   and p21_view_oe_pick_ticket.pick_ticket_no not in 
      (select wireless_trans_audit_hdr.wireless_transaction_number
         from wireless_trans_audit_hdr inner join wireless_trans_audit_line on
              wireless_trans_audit_hdr.wireless_trans_audit_hdr_uid = wireless_trans_audit_line.wireless_trans_audit_hdr_uid
        where wireless_trans_audit_hdr.wireless_transaction_type_cd in (1881,1520)
          and wireless_trans_audit_hdr.wireless_transaction_number = p21_view_oe_pick_ticket.pick_ticket_no
          and wireless_trans_audit_line.transaction_line_number = p21_view_oe_pick_ticket_detail.line_number
		union select group_pick_ticket_detail.pick_ticket_no
	            from group_pick_ticket_hdr inner join group_pick_ticket_detail on
                     group_pick_ticket_hdr.group_pick_ticket_hdr_uid = group_pick_ticket_detail.group_pick_ticket_hdr_uid
	             and group_pick_ticket_detail.pick_ticket_no = p21_view_oe_pick_ticket.pick_ticket_no)
group by p21_view_oe_pick_ticket.location_id) AS T2
ON T1.location_id = T2.location_id


UPDATE @branch_performance_report
  set total_order_lines_picked_wwms = t2.total_order_lines_picked_wwms
  from @branch_performance_report T1 INNER JOIN
(select oe_pick_ticket.location_id, count(*) total_order_lines_picked_wwms
from oe_pick_ticket 
      INNER JOIN oe_hdr ON 
        oe_hdr.order_no = oe_pick_ticket.order_no
	  INNER JOIN oe_pick_ticket_detail on
	    oe_pick_ticket.pick_ticket_no = oe_pick_ticket_detail.pick_ticket_no
where oe_pick_ticket.oe_pick_ticket_type_cd IS NULL
  and ( (oe_pick_ticket.delete_flag = 'N') or
        (oe_pick_ticket.delete_flag = 'Y' and oe_pick_ticket.pick_and_hold = 'Y') )
  and oe_pick_ticket.auxiliary = 'N'
  and oe_hdr.rma_flag = 'N'
  and oe_pick_ticket.direct_shipment = 'N'
  and oe_pick_ticket.company_id = '10' 
  and oe_pick_ticket.confirmable_row_status_flag in (1979,1980)  -- P21 Ignores 1979 
  and oe_pick_ticket.location_id >= @START_LOCATION_ID 
  and oe_pick_ticket.location_id <= @END_LOCATION_ID
  and oe_pick_ticket.ship_date >= @START_DATE
  and oe_pick_ticket.ship_date <= @END_DATE
  and oe_pick_ticket.pick_ticket_no in 
      (select wireless_trans_audit_hdr.wireless_transaction_number
         from wireless_trans_audit_hdr inner join wireless_trans_audit_line on
              wireless_trans_audit_hdr.wireless_trans_audit_hdr_uid = wireless_trans_audit_line.wireless_trans_audit_hdr_uid
        where wireless_trans_audit_hdr.wireless_transaction_type_cd in (1881,1520)
          and wireless_trans_audit_hdr.wireless_transaction_number = oe_pick_ticket.pick_ticket_no
          and wireless_trans_audit_line.transaction_line_number = oe_pick_ticket_detail.line_number
		union select group_pick_ticket_detail.pick_ticket_no
	            from group_pick_ticket_hdr inner join group_pick_ticket_detail on
                     group_pick_ticket_hdr.group_pick_ticket_hdr_uid = group_pick_ticket_detail.group_pick_ticket_hdr_uid
	             and group_pick_ticket_detail.pick_ticket_no = oe_pick_ticket.pick_ticket_no)
group by oe_pick_ticket.location_id ) AS T2
ON T1.location_id = T2.location_id


UPDATE @branch_performance_report
  set total_order_lines_picked = t2.total_order_lines_picked
  from @branch_performance_report T1 INNER JOIN
(select p21_view_oe_pick_ticket.location_id, count(*) total_order_lines_picked
from p21_view_oe_pick_ticket INNER JOIN p21_view_oe_hdr ON 
    p21_view_oe_hdr.order_no = p21_view_oe_pick_ticket.order_no
	inner join p21_view_oe_pick_ticket_detail on
	p21_view_oe_pick_ticket.pick_ticket_no = p21_view_oe_pick_ticket_detail.pick_ticket_no
where
   p21_view_oe_pick_ticket.oe_pick_ticket_type_cd IS NULL
   AND p21_view_oe_pick_ticket.delete_flag = 'N'
   AND p21_view_oe_pick_ticket.auxiliary = 'N'
   AND p21_view_oe_hdr.rma_flag = 'N'
   AND p21_view_oe_pick_ticket.direct_shipment = 'N'
   AND p21_view_oe_pick_ticket.company_id = '10' 
   AND p21_view_oe_pick_ticket.confirmable_row_status_flag in (1979,1980)  -- P21 Ignores 1979 
   and p21_view_oe_pick_ticket.location_id >= @START_LOCATION_ID 
   and p21_view_oe_pick_ticket.location_id <= @END_LOCATION_ID
   and p21_view_oe_pick_ticket.ship_date >= @START_DATE
   and p21_view_oe_pick_ticket.ship_date <= @END_DATE
group by p21_view_oe_pick_ticket.location_id) AS T2
ON T1.location_id = T2.location_id


UPDATE @branch_performance_report
  set total_order_picked = t2.total_order_picked
  from @branch_performance_report T1 INNER JOIN
(select p21_view_oe_pick_ticket.location_id, count(*) total_order_picked
from p21_view_oe_pick_ticket INNER JOIN p21_view_oe_hdr ON 
    p21_view_oe_hdr.order_no = p21_view_oe_pick_ticket.order_no
where
   p21_view_oe_pick_ticket.oe_pick_ticket_type_cd IS NULL
   AND p21_view_oe_pick_ticket.delete_flag = 'N'
   AND p21_view_oe_pick_ticket.auxiliary = 'N'
   AND p21_view_oe_hdr.rma_flag = 'N'
   AND p21_view_oe_pick_ticket.direct_shipment = 'N'
   AND p21_view_oe_pick_ticket.company_id = '10' 
   AND p21_view_oe_pick_ticket.confirmable_row_status_flag in (1979,1980)  -- P21 Ignores 1979 
   and p21_view_oe_pick_ticket.location_id >= @START_LOCATION_ID 
   and p21_view_oe_pick_ticket.location_id <= @END_LOCATION_ID
   and p21_view_oe_pick_ticket.ship_date >= @START_DATE
   and p21_view_oe_pick_ticket.ship_date <= @END_DATE
group by p21_view_oe_pick_ticket.location_id) AS T2
ON T1.location_id = T2.location_id


delete from @branch_performance_report
where total_po_lines_recd_wwms = 0
  and total_xfer_lines_recd_wwms = 0
  and total_xfer_lines_picked_wwms = 0
  and total_order_lines_picked_wwms = 0


return

END
GO


