select    DISTINCT   
-- Material
--,itemcatalog_material_description
businessunit_shortname
,season_id+' ' +convert(nvarchar(10),seasonyear_year) as Season
, WHOLESALEvalue [Wholesale value]
,Units
   from packlist as packlist 
inner JOIN CARTON  as carton  on carton.packlist_id=packlist.packlist_id
inner join cartonitem as cartonitem on cartonitem.carton_id=carton.carton_id
inner join (select businessunit_id 
,sum (cartonitem_quantity) as Units
from
cartonitem  as cartonitem
inner join carton as carton on carton.carton_id=cartonitem.carton_id
 group by businessunit_id,carton.seasonyear_id
)cartonitemTable on carton.businessunit_id=cartonitemTable.businessunit_id
inner join (SELECT SUM (item_wholesaleunit_price) AS WHOLESALEvalue,carton.businessunit_id
FROM dbo.ItemCatalog
inner join cartonitem as cartonitem on itemcatalog_style_code+'-'+itemcatalog_color_code=cartonitem.cartonitem_style_code+'-'+cartonitem_color_code
inner join carton as carton on carton.carton_id=cartonitem.carton_id
GROUP BY  carton.businessunit_id,carton.seasonyear_id)AS wholesaletable
on wholesaletable.businessunit_id=carton.businessunit_id
inner join cartonstatus on cartonstatus.cartonstatus_id=carton.cartonstatus_id
inner join businessunit on businessunit.businessunit_id=carton.businessunit_id
INNER join  seasonyear on seasonyear.seasonyear_id=carton.seasonyear_id
inner join shipto on shipto.shipto_id=packlist.shipto_id
inner join billto on billto.billto_id=SHIPTO.billto_id
inner join electronicinvoice ei on ei.electronicinvoice_id=carton.electronicinvoice_id
left join invoice as invoice on invoice.packlist_id=packlist.packlist_id
left join [order] as orders on orders.order_id=packlist.order_id
 where (carton.packlist_id=@PacklistId or @PacklistId is null)
and    (billto.customergroup_id in ( select value from dbo.fn_RepSplit(@CustomerGroup,',')) or @CustomerGroup is null) 
and    (billto.customertype_id in ( select value from dbo.fn_RepSplit(@CustomerType ,',')) or @CustomerType  is null) 
and (billto.nos=@Nos or @Nos is null)
 and (billto.BillTo_Id=@BillToId or @BillToId is null)
and (EI.electronicinvoice_fileid=@ElectronicInvoiceId or @ElectronicInvoiceId is null)
and (packlist.packlist_status in ( select value from dbo.fn_RepSplit(@PacklistStatus,','))or @PacklistStatus is null)
and (invoice.invoicestatus_id in ( select value from dbo.fn_RepSplit(@InvoiceStatus,','))or @InvoiceStatus is null)
and (orders.order_statusid in ( select value from dbo.fn_RepSplit(@OrderStatus,','))or @OrderStatus is null)
and (packlist.order_id=@OrderId  or @OrderId  is null)
and (carton.carton_id=@CartonId or @CartonId is null)
and (businessunit.businessunit_id in ( select value from dbo.fn_RepSplit(@BusinessUnitName ,','))or @BusinessUnitName  is null)
 and (cartonitem.cartonitem_style_code =@Style or @Style is null)
and (cartonitem.cartonitem_color_code =@Colour or @Colour is null)
and (cartonitem_size_id=@Size or @Size is null)
and (carton.seasonyear_id=@Season or @Season is null)
and (packlist.packlist_planeddelivery=@DeliveryPlanDate  or @DeliveryPlanDate is null)

end

SELECT c.[businessunit_id],
c.seasonyear_id,
SUM(ci.totalqty) as units,
SUM(ci.totalqty * ic.item_wholesaleunit_price) AS [Total wholesale value]
FROM packlist p
JOIN [Carton] c
ON c.packlist_id=p.packlist_id
----
JOIN (SELECT [cartonitem_color_code]
		,[cartonitem_style_code]
		,carton_id,
		SUM(cartonitem_quantity) AS totalqty
      FROM [CartonItem] 
      GROUP BY [cartonitem_color_code]
		,[cartonitem_style_code]
		,carton_id)ci
ON ci.carton_id=c.carton_id
---
JOIN [ItemCatalog] ic
ON ic.itemcatalog_style_code = ci.cartonitem_style_code
AND ic.itemcatalog_color_code=ci.cartonitem_color_code
left join invoice on invoice.packlist_id=p.packlist_id
 where  c.packlist_id='70381271'
group by  c.businessunit_id
,c.seasonyear_id