-- declare @CarouselPosition int
-- set @CarouselPosition = 1

select  *
from(
select Q.[Queue Order],	 
       A.[Package Set ID] [Package Set ID],
       A.[No_] [Package Number],
       A.[Package Sequence] [Package Sequence],
	   A.[Package Type] [Package Type],
       A.[Package Code] [Package Code],
	   PT.[Description] [Package Name],
	   A.[Sell-to Customer No_] [Sell-to Code],
	   C.[Name] [Sell-to Name],
       A.[Ship-to Code],
	   D.[Name] [Ship-to Name],
	   case A.[Source Document Type] when 1 then 'Sales Order' else 'Transfer Order' end [OrderType],
       A.[Source Document No_] [Order Number],
       A.[Customer Group Code] [Customer Group Code],
       A.[Shipment Date] [Shipment Date],
       B.[Genus Code] [Genus Code],
       A.[Item No_] [Item No],
	   A.[Variant Code] [Variant Code],
       A.[Description] [Item Description],
       A.[UPC Code],
       A.[Shipped],
	   A.[Source Unit of Measure Code] [Unit of Measure],
	   dbo.GetFeatureDescription(B.[Genus Code], 'COLORSALES', A.[Shortcut Feature 1 Code], A.[Item No_]) [Colors],
	   dbo.GetFeatureDescription(B.[Genus Code], 'BLOOM', A.[Shortcut Feature 2 Code], A.[Item No_]) [Bloom Count],
	   dbo.GetFeatureDescription(B.[Genus Code], 'VARIETIES', A.[Shortcut Feature 3 Code], A.[Item No_]) [Varieties Per Carrier],
	   dbo.GetFeatureDescription(B.[Genus Code], 'SPEC', A.[Shortcut Feature 4 Code], A.[Item No_]) [Plant Specifications],
	   dbo.GetFeatureDescription(B.[Genus Code], 'STEMCOUNT', A.[Shortcut Feature 5 Code], A.[Item No_]) [Stem Count],
	   dbo.GetFeatureDescription(B.[Genus Code], 'PROTECTION', A.[Shortcut Feature 6 Code], A.[Item No_]) [Plant Protection],
	   dbo.GetFeatureDescription(B.[Genus Code], 'PROTECT2', A.[Shortcut Feature 7 Code], A.[Item No_]) [Plant Protection 2],
	   dbo.GetFeatureDescription(B.[Genus Code], 'CASEINSERT', A.[Shortcut Feature 8 Code], A.[Item No_]) [Case Inserts],
	   dbo.GetFeatureDescription(B.[Genus Code], 'UPCLOCATION', A.[Shortcut Feature 9 Code], A.[Item No_]) [UPC Location],
	   dbo.GetFeatureDescription(B.[Genus Code], 'LABELSALES', A.[Shortcut Feature 10 Code], A.[Item No_]) [UPC Information],
       A.[Cross-Reference No_],
       A.[Package Quantity],
	   A.[Last Label Sequence Number],
	   dbo.GetPackageAssemblyOrderNumber(A.[Item No_],A.[Source Document No_], A.[No_]) [Assembly Order No]
from   [Green Circle Growers$GCG XPAK Job Queue] Q (nolock)
inner join [Green Circle Growers$GCG Package Header] A (nolock)	on
		 Q.[Order Number] = A.[Source Document No_]
		 and Q.[Status] in (0, 1)
		 and Q.[Package Number] = A.[No_]
		 and A.[Source Document Type] in (1, 10)
		 and A.[Sell-to Customer No_] <> '0001370'
		 and A.[Ship-from Location Code] = 'LOC'
		 and A.[Shipped] <> 1
Inner Join [Green Circle Growers$GCG Package Type] PT (nolock) on 
		 PT.[Code] = A.[Package Code]
		 and    PT.[Package Type] = A.[Package Type]
Inner Join [Green Circle Growers$Item] B (nolock) on 
		B.[No_] = A.[Item No_]
Inner Join [Green Circle Growers$Customer] C (nolock) on
		C.[No_] = A.[Sell-to Customer No_]
Inner Join [Green Circle Growers$Ship-to Address] D (nolock) on
		D.[Customer No_] = A.[Sell-to Customer No_]
		and    D.[Code] = A.[Ship-to Code]

where  Q.[Position No_] = @CarouselPosition

and    exists(select 'x'
              from   [Green Circle Growers$Default Dimension] SA (nolock),
	                 [Green Circle Growers$Dimension Value] SB (nolock)
              where  SA.[Dimension Code] = 'PRODLINE'
              and    SA.[Table ID] = 27
              and    SA.[No_] = B.[No_]
              and    SA.[Dimension Code] = SB.[Dimension Code]
              and    SA.[Dimension Value Code] = SB.[Code]
              and    SB.[Blocked] = 0
              and    SB.[Dimension Value Type] = 0
              and    SB.[Code] between 2110 and 2190)
and   exists(select 'x'
             from   [Green Circle Growers$Sales Header] SH (nolock)
			 where  SH.[No_] = A.[Source Document No_]
			 and    SH.[Status] = 1
			 union
			 select 'x'
			 from   [Green Circle Growers$Transfer Header] TH (nolock)
			 where  TH.[No_] = A.[Source Document No_]
			 and    TH.[Status] = 1)
union
select Q.[Queue Order],
       A.[Package Set ID],
       A.[No_],
       A.[Package Sequence],
	   A.[Package Type],
       A.[Package Code],
	   PT.[Description],
	   A.[Sell-to Customer No_],
	   C.[Name],
       A.[Ship-to Code],
	   '' [Ship-to Name],
	   case A.[Source Document Type] when 1 then 'Sales Order' else 'Transfer Order' end,
       A.[Source Document No_],
       A.[Customer Group Code],
       A.[Shipment Date],
       B.[Genus Code],
       A.[Item No_],
	   A.[Variant Code],
       A.[Description],
       A.[UPC Code],
       A.[Shipped],
	   A.[Source Unit of Measure Code],
	   dbo.GetFeatureDescription(B.[Genus Code], 'COLORSALES', A.[Shortcut Feature 1 Code], A.[Item No_]),
	   dbo.GetFeatureDescription(B.[Genus Code], 'BLOOM', A.[Shortcut Feature 2 Code], A.[Item No_]),
	   dbo.GetFeatureDescription(B.[Genus Code], 'VARIETIES', A.[Shortcut Feature 3 Code], A.[Item No_]),
	   dbo.GetFeatureDescription(B.[Genus Code], 'SPEC', A.[Shortcut Feature 4 Code], A.[Item No_]),
	   dbo.GetFeatureDescription(B.[Genus Code], 'STEMCOUNT', A.[Shortcut Feature 5 Code], A.[Item No_]),
	   dbo.GetFeatureDescription(B.[Genus Code], 'PROTECTION', A.[Shortcut Feature 6 Code], A.[Item No_]),
	   dbo.GetFeatureDescription(B.[Genus Code], 'PROTECT2', A.[Shortcut Feature 7 Code], A.[Item No_]),
	   dbo.GetFeatureDescription(B.[Genus Code], 'CASEINSERT', A.[Shortcut Feature 8 Code], A.[Item No_]),
	   dbo.GetFeatureDescription(B.[Genus Code], 'UPCLOCATION', A.[Shortcut Feature 9 Code], A.[Item No_]),
	   dbo.GetFeatureDescription(B.[Genus Code], 'LABELSALES', A.[Shortcut Feature 10 Code], A.[Item No_]),
       A.[Cross-Reference No_],
       A.[Package Quantity],
	   A.[Last Label Sequence Number],
	   dbo.GetPackageAssemblyOrderNumber(A.[Item No_],A.[Source Document No_], A.[No_]) [Assembly Order No]
from   [Green Circle Growers$GCG XPAK Job Queue] Q (nolock)
inner join [Green Circle Growers$GCG Package Header] A (nolock) on
		Q.[Order Number] = A.[Source Document No_]
		and Q.[Package Number] = A.[No_]
		and Q.[Status] in (0, 1)
		and    A.[Source Document Type] in (1, 10)
		and    A.[Sell-to Customer No_] <> '0001370'
		and    A.[Ship-from Location Code] = 'LOC'
		and    A.[Shipped] <> 1
Inner Join [Green Circle Growers$GCG Package Type] PT on 
	     PT.[Code] = A.[Package Code] 
		 and    PT.[Package Type] = A.[Package Type]
Inner Join [Green Circle Growers$Item] B 	on
         B.[No_] = A.[Item No_]
Inner Join [Green Circle Growers$Customer] C on 
		 C.[No_] = A.[Sell-to Customer No_]
where  Q.[Position No_] = @CarouselPosition
and    not exists(select 'x'
                  from   [Green Circle Growers$Ship-to Address] (nolock)
				  where  [Customer No_] = A.[Sell-to Customer No_]
                  and    [Code] = A.[Ship-to Code])
and    exists(select 'x'
              from   [Green Circle Growers$Default Dimension] SA (nolock),
	                 [Green Circle Growers$Dimension Value] SB (nolock)
              where  SA.[Dimension Code] = 'PRODLINE'
              and    SA.[Table ID] = 27
              and    SA.[No_] = B.[No_]
              and    SA.[Dimension Code] = SB.[Dimension Code]
              and    SA.[Dimension Value Code] = SB.[Code]
              and    SB.[Blocked] = 0
              and    SB.[Dimension Value Type] = 0
              and    SB.[Code] between 2110 and 2190)
and   exists(select 'x'
             from   [Green Circle Growers$Sales Header] SH (nolock)
			 where  SH.[No_] = A.[Source Document No_]
			 and    SH.[Status] = 1
			 union
			 select 'x'
			 from   [Green Circle Growers$Transfer Header] TH (nolock)
			 where  TH.[No_] = A.[Source Document No_]
			 and    TH.[Status] = 1)
) as temp
order by [Queue Order]
--order by [Package Name], [Package Set ID], [Sell-to Name], [Ship-to Name], [Package Number]