USP_DATALIST_SALESORDERMERCHANDISE

CREATE procedure dbo.USP_DATALIST_SALESORDERMERCHANDISE
(
  @CONTEXTID uniqueidentifier
)
as
  set nocount on;

  -- Non-refunded merchandise

  select
    [SALESORDERITEM].[ID],
    [SALESORDERITEM].[DESCRIPTION] as [DESCRIPTION],
    [MERCHANDISEPRODUCTINSTANCE].[BARCODE],
    [SALESORDERITEM].[QUANTITY] - sum(coalesce([CREDITITEM].[QUANTITY],0)) as [QUANTITY],
    [SALESORDERITEM].[PRICE],
    coalesce(sum([SALESORDERITEMITEMDISCOUNT].[AMOUNT]),0.0)  as [DISCOUNTS],
    [SALESORDERITEM].[TOTAL] - sum(coalesce([CREDITITEM].[TOTAL],0)) - coalesce(sum([SALESORDERITEMITEMDISCOUNT].[AMOUNT]),0.0) as [TOTAL],
    [SALESORDERITEM].[TYPECODE],
    0 as ISREFUND
  from dbo.[SALESORDERITEM]
  inner join dbo.[SALESORDERITEMMERCHANDISE] on [SALESORDERITEM].[ID] = [SALESORDERITEMMERCHANDISE].[ID]
  inner join dbo.[MERCHANDISEPRODUCTINSTANCE] on [SALESORDERITEMMERCHANDISE].[MERCHANDISEPRODUCTINSTANCEID] = [MERCHANDISEPRODUCTINSTANCE].[ID]
  left outer join dbo.[CREDITITEM]  on [SALESORDERITEM].[ID] = [CREDITITEM].[SALESORDERITEMID]
  left outer join dbo.[CREDIT] on [CREDITITEM].[CREDITID] = [CREDIT].[ID]
  left join dbo.[SALESORDERITEMITEMDISCOUNT] on [SALESORDERITEMITEMDISCOUNT].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
  where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID
  group by 
    [SALESORDERITEM].[ID],
    [SALESORDERITEM].[DESCRIPTION],
    [MERCHANDISEPRODUCTINSTANCE].[BARCODE],
    [SALESORDERITEM].[QUANTITY],
    [SALESORDERITEM].[PRICE],
    [SALESORDERITEM].[TOTAL],
    [SALESORDERITEM].[TYPECODE]
  having ([SALESORDERITEM].[QUANTITY] - sum(coalesce([CREDITITEM].[QUANTITY],0))) > 0

  union all

  -- Refunded merchandise

  select
    [SALESORDERITEM].[ID],
    --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

    'Refund ' + convert(nvarchar(20), cast([CREDIT].[TRANSACTIONDATE] as date), 1) + ', ' + [SALESORDERITEM].[DESCRIPTION] as [DESCRIPTION],
    [MERCHANDISEPRODUCTINSTANCE].[BARCODE],
    sum([CREDITITEM].[QUANTITY]),
    [SALESORDERITEM].[PRICE],
    [CREDITITEM].[DISCOUNTS],
    sum([CREDITITEM].[TOTAL]) as [TOTAL],
    [SALESORDERITEM].[TYPECODE],
    1 as [ISREFUND]
  from dbo.[SALESORDERITEM]
  inner join dbo.[SALESORDERITEMMERCHANDISE] on [SALESORDERITEM].[ID] = [SALESORDERITEMMERCHANDISE].[ID]
  inner join dbo.[MERCHANDISEPRODUCTINSTANCE] on [SALESORDERITEMMERCHANDISE].[MERCHANDISEPRODUCTINSTANCEID] = [MERCHANDISEPRODUCTINSTANCE].[ID]
  left outer join dbo.[CREDITITEM] on [SALESORDERITEM].[ID] = [CREDITITEM].[SALESORDERITEMID]
  left outer join dbo.[CREDIT] on [CREDITITEM].[CREDITID] = [CREDIT].[ID]
  where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID
  and ([CREDITITEM].[QUANTITY]) > 0
  group by
    [SALESORDERITEM].[ID],
    [SALESORDERITEM].[DESCRIPTION],
    [MERCHANDISEPRODUCTINSTANCE].[BARCODE],
    [SALESORDERITEM].[PRICE],
    [SALESORDERITEM].[TOTAL],
    [SALESORDERITEM].[TYPECODE],
    --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

    cast([CREDIT].[TRANSACTIONDATE] as date),
    [CREDITITEM].[DISCOUNTS];

  return 0;