USP_MKTSEGMENTLIST_GETHISTORICALDATALIST

CREATE procedure dbo.[USP_MKTSEGMENTLIST_GETHISTORICALDATALIST]
(
  @SEGMENTID uniqueidentifier,
  @STATUSCODE tinyint = null,
  @CURRENCYCODE tinyint = null
)
as
  set nocount on;

  set @STATUSCODE = isnull(@STATUSCODE, 0);
  set @CURRENCYCODE = isnull(@CURRENCYCODE, 0);

  select
    [MKTSEGMENTLIST].[ID],
    row_number() over (order by [MKTSEGMENTLIST].[DATEADDED]) as [SEQUENCE],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTSEGMENT].[NAME] else [MKTSEGMENTLISTHISTORICAL].[SEGMENTNAME] end) as [SEGMENTNAME],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTSEGMENT].[DESCRIPTION] else [MKTSEGMENTLISTHISTORICAL].[SEGMENTDESCRIPTION] end) as [SEGMENTDESCRIPTION],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTSEGMENT].[CODE] else [MKTSEGMENTLISTHISTORICAL].[SEGMENTCODE] end) as [SEGMENTCODE],
    (select [DESCRIPTION] from dbo.[MKTSEGMENTCATEGORYCODE] where [ID] = (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTSEGMENT].[SEGMENTCATEGORYCODEID] else [MKTSEGMENTLISTHISTORICAL].[SEGMENTCATEGORYCODEID] end)) as [SEGMENTCATEGORY],
    [MKTSEGMENTLIST].[STATUSCODE],
    [MKTSEGMENTLIST].[STATUS],
    [MKTSEGMENTLIST].[TOTALRECORDCOUNT],
    [MKTSEGMENTLIST].[DUPLICATERECORDCOUNT],
    [MKTSEGMENTLIST].[ORDERDATE],
    [MKTSEGMENTLIST].[EXPIRATIONDATE],
    [MKTSEGMENTLIST].[NUMBEROFCONTACTS],
    [MKTSEGMENTLIST].[RENTALQUANTITY],
    case @CURRENCYCODE
      when 1 then (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[ORGANIZATIONBASERENTALCOST] else [MKTSEGMENTLISTHISTORICAL].[ORGANIZATIONLISTBASERENTALCOST] end)
      else (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[BASERENTALCOST] else [MKTSEGMENTLISTHISTORICAL].[LISTBASERENTALCOST] end) end as [LISTBASERENTALCOST],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[BASERENTALCOSTBASISCODE] else [MKTSEGMENTLISTHISTORICAL].[LISTBASERENTALCOSTBASISCODE] end) as [LISTBASERENTALCOSTBASISCODE],
    case @CURRENCYCODE
      when 1 then [MKTSEGMENTLIST].[ORGANIZATIONRENTALCOSTADJUSTMENT]
      else [MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT] end [RENTALCOSTADJUSTMENT],
    [MKTSEGMENTLIST].[RENTALCOSTBASISCODE],
    [MKTSEGMENTLIST].[EXCHANGEQUANTITY],
    case @CURRENCYCODE
      when 1 then (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[ORGANIZATIONBASEEXCHANGECOST] else [MKTSEGMENTLISTHISTORICAL].[ORGANIZATIONLISTBASEEXCHANGECOST] end)
      else (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[BASEEXCHANGECOST] else [MKTSEGMENTLISTHISTORICAL].[LISTBASEEXCHANGECOST] end) end as [LISTBASEEXCHANGECOST],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[BASEEXCHANGECOSTBASISCODE] else [MKTSEGMENTLISTHISTORICAL].[LISTBASEEXCHANGECOSTBASISCODE] end) as [LISTBASEEXCHANGECOSTBASISCODE],
    case @CURRENCYCODE
      when 1 then [MKTSEGMENTLIST].[ORGANIZATIONEXCHANGECOSTADJUSTMENT]
      else [MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT] end [EXCHANGECOSTADJUSTMENT],
    [MKTSEGMENTLIST].[EXCHANGECOSTBASISCODE],
    [MKTSEGMENTLIST].[RECEIVEDVIA],

    --Total base cost

    case @CURRENCYCODE
      when 1 then
        ((case when (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[BASERENTALCOSTBASISCODE] else [MKTSEGMENTLISTHISTORICAL].[LISTBASERENTALCOSTBASISCODE] end) = 1 then  --Per thousand

          cast([MKTLIST].[ORGANIZATIONBASERENTALCOST] * ([MKTSEGMENTLIST].[RENTALQUANTITY] / cast(1000 as decimal(20,5))) as money)
        else
          [MKTLIST].[ORGANIZATIONBASERENTALCOST]
        end)
       +
       (case when (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[BASEEXCHANGECOSTBASISCODE] else [MKTSEGMENTLISTHISTORICAL].[LISTBASEEXCHANGECOSTBASISCODE] end) = 1 then  --Per thousand

          cast([MKTLIST].[ORGANIZATIONBASEEXCHANGECOST] * ([MKTSEGMENTLIST].[EXCHANGEQUANTITY] / cast(1000 as decimal(20,5))) as money)
        else
          [MKTLIST].[ORGANIZATIONBASEEXCHANGECOST]
        end))
      else
        ((case when (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[BASERENTALCOSTBASISCODE] else [MKTSEGMENTLISTHISTORICAL].[LISTBASERENTALCOSTBASISCODE] end) = 1 then  --Per thousand

          cast([MKTLIST].[BASERENTALCOST] * ([MKTSEGMENTLIST].[RENTALQUANTITY] / cast(1000 as decimal(20,5))) as money)
        else
          [MKTLIST].[BASERENTALCOST]
        end)
       +
       (case when (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[BASEEXCHANGECOSTBASISCODE] else [MKTSEGMENTLISTHISTORICAL].[LISTBASEEXCHANGECOSTBASISCODE] end) = 1 then  --Per thousand

          cast([MKTLIST].[BASEEXCHANGECOST] * ([MKTSEGMENTLIST].[EXCHANGEQUANTITY] / cast(1000 as decimal(20,5))) as money)
        else
          [MKTLIST].[BASEEXCHANGECOST]
        end)) end as [TOTALBASECOST],

    --Total cost adjustments

    case @CURRENCYCODE
      when 1 then
        ((case when [MKTSEGMENTLIST].[RENTALCOSTBASISCODE] = 1 then  --Per thousand

            cast([MKTSEGMENTLIST].[ORGANIZATIONRENTALCOSTADJUSTMENT] * ([MKTSEGMENTLIST].[RENTALQUANTITY] / cast(1000 as decimal(20,5))) as money)
          else
            [MKTSEGMENTLIST].[ORGANIZATIONRENTALCOSTADJUSTMENT]
          end)
         +
         (case when [MKTSEGMENTLIST].[EXCHANGECOSTBASISCODE] = 1 then  --Per thousand

            cast([MKTSEGMENTLIST].[ORGANIZATIONEXCHANGECOSTADJUSTMENT] * ([MKTSEGMENTLIST].[EXCHANGEQUANTITY] / cast(1000 as decimal(20,5))) as money)
          else
            [MKTSEGMENTLIST].[ORGANIZATIONEXCHANGECOSTADJUSTMENT]
          end))
      else
        ((case when [MKTSEGMENTLIST].[RENTALCOSTBASISCODE] = 1 then  --Per thousand

            cast([MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT] * ([MKTSEGMENTLIST].[RENTALQUANTITY] / cast(1000 as decimal(20,5))) as money)
          else
            [MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT]
          end)
         +
         (case when [MKTSEGMENTLIST].[EXCHANGECOSTBASISCODE] = 1 then  --Per thousand

            cast([MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT] * ([MKTSEGMENTLIST].[EXCHANGEQUANTITY] / cast(1000 as decimal(20,5))) as money)
          else
            [MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT]
          end)) end as [TOTALCOSTADJUSTMENT],

    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[NAME] else [MKTSEGMENTLISTHISTORICAL].[LISTNAME] end) as [LISTNAME],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[DESCRIPTION] else [MKTSEGMENTLISTHISTORICAL].[LISTDESCRIPTION] end) as [LISTDESCRIPTION],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[CODE] else [MKTSEGMENTLISTHISTORICAL].[LISTCODE] end) as [LISTCODE],
    (select [NAME] from dbo.[CONSTITUENT] where [ID] = (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[VENDORID] else [MKTSEGMENTLISTHISTORICAL].[LISTVENDORID] end)) as [LISTVENDOR],
    (select [DESCRIPTION] from dbo.[MKTLISTCATEGORYCODE] where [ID] = (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[LISTCATEGORYCODEID] else [MKTSEGMENTLISTHISTORICAL].[LISTCATEGORYCODEID] end)) as [LISTCATEGORY],
    [MKTLISTLAYOUT].[NAME] as [LAYOUTNAME],
    [MKTSEGMENTLIST].[FILENAME],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTSEGMENT].[DATECHANGED] else [MKTSEGMENTLISTHISTORICAL].[DATEADDED] end) as [DATEADDED],
    (case when [MKTSEGMENTLIST].[STATUSCODE] = 5 then [MKTSEGMENTLISTHISTORICAL].[DATEPURGED] else null end) as [DATEPURGED],
    case @CURRENCYCODE
      when 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]()
      else [MKTLIST].[BASECURRENCYID] end [BASECURRENCYID],
    (case when exists(select 1 from dbo.[MKTSEGMENTATIONSEGMENTLIST] where [SEGMENTLISTID] = [MKTSEGMENTLIST].[ID]) then 1 else 0 end) as [INUSEINEFFORT]
  from dbo.[MKTSEGMENTLIST]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTLIST].[SEGMENTID]
  left join dbo.[MKTSEGMENTLISTHISTORICAL] on [MKTSEGMENTLISTHISTORICAL].[ID] = [MKTSEGMENTLIST].[ID]
  inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
  inner join dbo.[MKTLISTLAYOUT] on [MKTLISTLAYOUT].[ID] = [MKTSEGMENTLIST].[LISTLAYOUTID]
  where [MKTSEGMENTLIST].[SEGMENTID] = @SEGMENTID
  and [MKTSEGMENTLIST].[TYPECODE] = 0  --Imported only

  and (@STATUSCODE = 0 or [MKTSEGMENTLIST].[STATUSCODE] = @STATUSCODE)
  order by [MKTSEGMENTLIST].[DATEADDED] desc;

  return 0;