USE [P21]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Fred Rappuhn, NuTech Services, LLC
-- Create date: 08/23/2017
-- Description:	Return primary and all secondary bins
-- =============================================
CREATE PROCEDURE [dbo].[nutech_transfer_label] 
	@transfer_no as integer
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	select dbo.transfer_hdr.transfer_no, CONVERT(VARCHAR, dbo.transfer_hdr.date_created, 101) date_created,
		dbo.transfer_hdr.from_location_id, transfer_hdr.to_location_id, 
		dbo.transfer_line.inv_mast_uid, IM1.item_id, IM1.item_desc, dbo.inv_loc.primary_bin,
		dbo.transfer_line.line_no, dbo.inv_loc.primary_bin +
		ISNULL(Stuff((
			select ', ' + dbo.inv_bin.bin
			from dbo.transfer_hdr with (NOLOCK), dbo.transfer_line with (NOLOCK), 
				dbo.inv_mast IM2 with (NOLOCK), dbo.inv_bin with (NOLOCK), dbo.inv_loc with (NOLOCK)
			where dbo.transfer_line.transfer_no = dbo.transfer_hdr.transfer_no
			and IM2.inv_mast_uid = dbo.transfer_line.inv_mast_uid
			and dbo.inv_bin.inv_mast_uid = dbo.transfer_line.inv_mast_uid
			and dbo.inv_bin.location_id = dbo.transfer_hdr.to_location_id
			and dbo.inv_loc.location_id = dbo.transfer_hdr.to_location_id
			and dbo.inv_loc.inv_mast_uid = dbo.transfer_line.inv_mast_uid
			and dbo.inv_loc.primary_bin <> dbo.inv_bin.bin
			and dbo.transfer_hdr.transfer_no = @transfer_no
			and IM2.inv_mast_uid = IM1.inv_mast_uid
			order by dbo.inv_bin.bin
			FOR	XML PATH('')
		),1,0,''),'') as bins
	from dbo.transfer_hdr with (NOLOCK)
	       inner join dbo.transfer_line with (NOLOCK) on
		     dbo.transfer_line.transfer_no = dbo.transfer_hdr.transfer_no
		   inner join dbo.inv_mast IM1 with (NOLOCK) on
		     IM1.inv_mast_uid = dbo.transfer_line.inv_mast_uid
		   inner join dbo.inv_bin with (NOLOCK) on
		     dbo.inv_bin.inv_mast_uid = dbo.transfer_line.inv_mast_uid and
			 dbo.inv_bin.location_id = dbo.transfer_hdr.to_location_id
		   inner join dbo.inv_loc with (NOLOCK) on
		     dbo.inv_loc.location_id = dbo.transfer_hdr.to_location_id and
		     dbo.inv_loc.inv_mast_uid = dbo.transfer_line.inv_mast_uid and
		     dbo.inv_loc.primary_bin = dbo.inv_bin.bin
	where dbo.transfer_hdr.transfer_no = @transfer_no
	order by dbo.transfer_hdr.transfer_no, dbo.transfer_line.line_no


END
GO


