--declare @packageNumber int
--declare @orderNumber nvarchar (20)
--set @packageNumber = 101801
--set @orderNumber = 'GS00005402'

select 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.GetPackageAssemblyOrder(A.[Item No_],A.[Customer Group Code], A.[Package Set ID]) [Assembly Order No]
from   [Green Circle Growers$GCG Package Header] A (nolock),
       [Green Circle Growers$GCG Package Type] PT (nolock),
       [Green Circle Growers$Item] B (nolock),
	   [Green Circle Growers$Customer] C (nolock),
	   [Green Circle Growers$Ship-to Address] D (nolock)
where  A.[Source Document Type] in (1, 10)
and    A.[Sell-to Customer No_] <> '0001370'
and    A.[Ship-from Location Code] = 'LOC'
and    A.[No_] = @packageNumber
and    A.[Source Document No_] = @orderNumber
and    A.[Shipped] <> 1
and    PT.[Code] = A.[Package Code]
and    PT.[Package Type] = A.[Package Type]
and    B.[No_] = A.[Item No_]
and    C.[No_] = A.[Sell-to Customer No_]
and    D.[Customer No_] = A.[Sell-to Customer No_]
and    D.[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)
union
select 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.GetPackageAssemblyOrder(A.[Item No_],A.[Customer Group Code], A.[Package Set ID])
from   [Green Circle Growers$GCG Package Header] A (nolock),
       [Green Circle Growers$GCG Package Type] PT (nolock),
       [Green Circle Growers$Item] B (nolock),
	   [Green Circle Growers$Customer] C (nolock)
where  A.[Source Document Type] in (1, 10)
and    A.[Sell-to Customer No_] <> '0001370'
and    A.[Ship-from Location Code] = 'LOC'
and    A.[No_] = @packageNumber
and    A.[Source Document No_] = @orderNumber
and    A.[Shipped] <> 1
and    PT.[Code] = A.[Package Code]
and    PT.[Package Type] = A.[Package Type]
and    B.[No_] = A.[Item No_]
and    C.[No_] = A.[Sell-to Customer No_]
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)
order by [Package Name], [Package Set ID], [Sell-to Name], [Ship-to Name], [Package Number]