go

declare @whCode varchar(5), @binLoc varchar(2)
set @whCode = '04'
	if @whCode = ''
		set @whCode = '%'

if @whCode = '02'
	set @binLoc = 'S%'
else
if @whCode = '03'
	set @binLoc = 'M%'
else
if @whCode = '04'
	set @binLoc = 'B%'
else
if @whCode = '%'
	set @binLoc = '%'

;
with CTEResultSet
as
(
	select ROW_NUMBER()
	over
	(
		partition by td.itemCode
		order by td.itemCode
	) as Seq,
	  td.ItemCode as ItemCode
	, td.Dscription as ItemDescription
	, cast(td.Quantity as decimal(10,0)) as OrderQty
	--, cast(twhs.OnHand as decimal(10,0)) as OnHand
	, th.CardCode as BPCode
	, th.CardName as BPName
	, cast(th.DocDate as nvarchar(20)) as DocumentDate
	, cast(th.DocDueDate as nvarchar(20)) as DocDueDate
	, cast(th.DocNum as decimal (10,0)) as SONumber
	, tsp.SlpName as SPerson
	--, twhs.WhsCode as WhsCode
	, isnull(tbloc.BINLABEL, '') as BLabel
	, cast(tbloc.QUANTITY as decimal(10,0)) as BQty


	from AU.dbo.RDR1 td
	inner join AU.dbo.ORDR th on th.DocEntry = td.DocEntry
	left outer join AU.dbo.OSLP tsp on tsp.SlpCode = td.SlpCode
	left outer join A1Warehouse.dbo.BINLOCAT tbloc on tbloc.PRODUCT = td.ItemCode collate SQL_Latin1_General_CP850_CI_AS --AND tbloc.WAREHOUSE = td.WhsCode collate SQL_Latin1_General_CP850_CI_AS

	where 
	td.LineStatus = 'O'					-- Order is Open
	and td.WhsCode like @whCode			-- Order lines are from Whs Code
	and tbloc.QUANTITY > 0				-- Location has Qty > 0
	and tbloc.BINLABEL like @binLoc		-- BinLabel corresponds with Warehouse
)

select
  --Seq,
  case when Seq = 1 then ItemCode else '' end as 'Item Code'
, case when Seq = 1 then ItemDescription else '' end as 'Item Description'
, case when Seq = 1 then cast(OrderQty as nvarchar(20)) else '' end as 'Order Qty'
, case when Seq = 1 then BPCode else '' end as 'BP Code'
, case when Seq = 1 then BPName else '' end as 'BP Name'
, case when Seq = 1 then cast(SONumber as nvarchar(20)) else '' end as 'Sales Order No.'
, case when Seq = 1 then SPerson else '' end as 'Sales Person'
--, case when Seq = 1 then WhsCode else '' end as 'Whs Code'
, BLabel as 'Bin Label'
, isnull(cast(BQty as nvarchar(20)), '') as 'Bin Qty'

from CTEResultSet

order by ItemCode, Seq, BLabel