Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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'))
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement