-- GetPotUpcPrintData

declare @packageSetId as int
set @packageSetId = '{0}'

select
	a.[Load No]
	, a.[Package Set ID]
	, a.ItemNo
	, a.[Item Description]
	, a.[Unit Volume]
	, a.[UPC]
	, a.[Retail Price]
	, a.[Shipment Date]
	, sum(a.Quantity) [Quantity]
from (
	select
		ll.[Package Set ID]
		, lh.[GCG Load No_]		[Load No]
		, pl.No_				[ItemNo]
		, pl.Description		[Item Description]
		, itm.[Unit Volume]
		, pl.[UPC Code]			[UPC]
		, case when lld.[Document Type]=1 then
				(select top 1 sl.[Retail Unit Price] from  [Green Circle Growers$Sales Line] sl(nolock)
					where sl.[Document No_]=pl.[Source Document No_] and sl.[Line No_]=pl.[Source Document Line No_])
			else case when  lld.[Document Type]=11 then
				(select top 1 sl.[Retail Unit Price] from  [Green Circle Growers$Transfer Line] sl(nolock)
					where sl.[Document No_]=pl.[Source Document No_] and sl.[Line No_]=pl.[Source Document Line No_])
			else
				0
			end end	[Retail Price]
		, ph.[Shipment Date]
		, ll.Quantity
	from [Green Circle Growers$GCG Load Line] ll(nolock)
		join [Green Circle Growers$GCG Load Header] lh(nolock) on lh.Code=ll.[Load Code]
		join [Green Circle Growers$GCG Load Line Detail] lld(nolock) on lld.[Load Code]=ll.[Load Code] and lld.[Load Line No_]=ll.[Line No_]
		join [Green Circle Growers$GCG Package Line] pl(nolock)
			on pl.[Package No_] = ll.[Package No_]
			and pl.[Source Document No_]=lld.[Document No_]
			and pl.[Source Document Line No_]=lld.[Document Line No_]
		join [Green Circle Growers$GCG Package Header] ph(nolock) on ph.No_=pl.[Package No_]
		join [Green Circle Growers$Item] itm(nolock) on itm.No_=pl.No_
	where ll.[Package Set ID] = @packageSetId
) a
group by a.[Load No]
	, a.[Package Set ID]
	, a.ItemNo
	, a.[Item Description]
	, a.[Unit Volume]
	, a.[UPC]
	, a.[Retail Price]
	, a.[Shipment Date]