set @localxml=
(SELECT 
 OrderItems.OrderNo								"OrderNo"			--"h - OrderNo"				-- 0
,OrderItems.[LineNo]                            "LineNo"			--"h - LineNo"				-- 1
,OrderItems.[ItemId]                            "ItemId" 			--"h - ItemId"				-- 2
,OrderItems.[CscuName]                          "CscuName"          --"h - CskuName"			-- 3
,OrderItems.[VariantCode]                       "VariantCode"       --"h - VarCode"				-- 4
,OrderItems.[Quantity]                          "Quantity"          --"int - Количество"		-- 5
,OrderItems.[ItemName]                          "ItemName"          --"txt - Название"			-- 6
,case when isnull(OrderItems.[VariantName], '')='' then OrderItems.[VariantCode] else OrderItems.[VariantName] end 
												"VariantName"		--"txt - Размер"			-- 7
,OrderItems.[PriceInclVat]                      "PriceInclVat"      --"money - Цена"			-- 8
--,OrderItems.[CouponInclVat]                     "CouponInclVat"     --"money - Купон"			-- 9
,q3.AmountCP                                    "CouponInclVat"     --"money - Купон"			-- 9
,OrderItems.[VatPercent]                        "VatPercent"        --"int - Ставка НДС"		--10
,OrderItems.[VatAmount]                         "VatAmount"         --"money - Сумма НДС"		--11
,OrderItems.[PriceExclVat]                      "PriceExclVat"      --"h - Цена без НДС"		--12
,OrderItems.[CouponExclVat]                     "CouponExclVat"		--"h - Купон без НДС"		--13
,case when OrderItems.[url]='' then '' else 
  case when charindex(',', OrderItems.[url], 1)=0 then OrderItems.[url] else SUBSTRING(OrderItems.[url], 1, charindex(',', OrderItems.[url], 1)-1) end
end												"url"				--"h - url"					--14
,Orders.CampaignName							"CampaignName"		--"h - Акция"				--15
,OrderItems.[Quantity]                          "MaxQuantity"       --"h - MaxQuantity"			--16
,Orders.CampaignCode							"CampaignCode"		--"h - CampaignCode"		--17
,OrderItems.[ItemDescription]                   "ItemDescription"	--"h - ItemDescription"		--18
,OrderItems.Changed                             "Changed"           --"h - Changed"				--19
--,isnull(ROUND((OrderItems.PriceInclVat+OrderItems.CouponInclVat)* convert(float, (Orders.CashlessDiscount))/100, 0), 0) 												"DiscountCashLess"	--"h - DiscountCashLess"	--20
,isnull(q2.AmountBN, 0) 												"DiscountCashLess"	--"h - DiscountCashLess"	--20
,isnull(OrderItems.[LPAmount], 0) "LPAmount"
,isnull(OrderItems.[LPToAdd], 0) "LPToAdd"
,isnull(q4.[AvaibleQty],0) "InWare"
,isnull(q5.Reason,0) PCID
,isnull(q5.ReasonDescr,'') PCD
, OrderItems.PriceInclVat+OrderItems.CouponInclVat+isnull(OrderItems.LPAmount,0) InSite
from
(
select 
OrderNo,
Host,
CashlessDiscount,
PaymentMethod,
CampaignName,
CampaignCode
from @Order
)Orders
left join
(
SELECT [OrderNo]
      ,[Host]
      ,[LineNo]
      ,[ItemId]
      ,[CscuName]
      ,[VariantCode]
      ,[Quantity]
      ,[VatPercent]
      ,[VatAmount]
      ,[PriceExclVat]
      ,[PriceInclVat]
      ,[CouponExclVat]
      ,[CouponInclVat]
      ,[ItemName]
      ,[VariantName]
      ,[url]
      ,[ItemDescription]
      ,[Changed]
	  ,[LPAmount]
	  ,LPToAdd
FROM [dbo].[cc_OrderItems](nolock)
)OrderItems
on OrderItems.OrderNo=@OrderNo and OrderItems.Host=@Host
 outer apply
  (
    select isnull(sum([Amount]),0) AmountBN
	from 
	(
	    Select distinct CSCU, Variant, DiscountType, Amount
		from
		(
			select distinct *
			from MO.[dbo].[cc_Order_HeadDiscount](nolock) q
			where [OrderNo]=@OrderNo
			and [OrderType]=(select top 1 [ShortName] from MO.[dbo].[cc_Ref_OrderTypes](nolock) where [id]=@OT) 
			and Id=(select max(Id) from MO.[dbo].[cc_Order_HeadDiscount](nolock) where [OrderNo]=@OrderNo and [OrderType]=q.OrderType)
		) t1
		left join MO.[dbo].[cc_Order_HeadDiscountItems](nolock) t2
		on t1.Id=t2.idHead
	) z1
	where DiscountType='PAYMENT'
	and CSCU=OrderItems.[CscuName]
	and Variant=OrderItems.[VariantCode]
  ) q2
  outer apply
  (
    select isnull(sum([Amount]),0) AmountCP
	from 
	(
	    Select distinct CSCU, Variant, DiscountType, Amount
		from
		(
			select distinct *
			from MO.[dbo].[cc_Order_HeadDiscount](nolock) q
			where [OrderNo]=@OrderNo
			and [OrderType]=(select top 1 [ShortName] from MO.[dbo].[cc_Ref_OrderTypes](nolock) where [id]=@OT) 
			and Id=(select max(Id) from MO.[dbo].[cc_Order_HeadDiscount](nolock) where [OrderNo]=@OrderNo and [OrderType]=q.OrderType)
		) t1
		left join MO.[dbo].[cc_Order_HeadDiscountItems](nolock) t2
		on t1.Id=t2.idHead
	) z2
	where DiscountType not in ('PAYMENT','LOYAL')
	and CSCU=OrderItems.[CscuName]
	and Variant=OrderItems.[VariantCode]
  ) q3
  outer apply
  ( select top 1 *
    from [KupiVIP].[dbo].[v_AvaibleQty] 
    where [Item No_]=OrderItems.CscuName and [Variant Code]=OrderItems.VariantCode
  ) q4
  outer apply
  (
     select top 1 [Reason],
	 (select top 1 [Description] from [dbo].[cc_Ref_PartialCancel](nolock) where [id]=p.Reason) ReasonDescr
	 from [dbo].[cc_ItemsChange_Log](nolock) p
	 where [OrderNo]=OrderItems.OrderNo
	 and [Host]=OrderItems.Host
	 and [Cscu]=OrderItems.CscuName
	 and [Varcode]=OrderItems.VariantCode
	 order by [Date] desc
  ) q5
  where OrderItems.[OrderNo]=@OrderNo and OrderItems.[Host]=@Host
  


order by OrderItems.OrderNo, OrderItems.[LineNo]
for xml path('Item'), root('Items'))