USP_OLAP_ETL_UPDATE_OLAPDATASOURCEATTRIBUTECATEGORY

CREATE procedure dbo.[USP_OLAP_ETL_UPDATE_OLAPDATASOURCEATTRIBUTECATEGORY](@OLAPDATASOURCEID uniqueidentifier)
as
begin

  declare @CHANGEAGENTID uniqueidentifier
  exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output; 

  --insert into control table all attributes categories that are needed for list builders

  insert into dbo.[OLAPDATASOURCEATTRIBUTECATEGORY]
  (
    [ID], 
    [OLAPDATASOURCEID], 
    [ATTRIBUTECATEGORYID], 
    [ADDEDBYID], 
    [DATEADDED], 
    [CHANGEDBYID], 
    [DATECHANGED], 
    [ISLISTBUILDERATTRIBUTE]
  )
  select
    newid() as [ID],
    @OLAPDATASOURCEID as [OLAPDATASOURCEID],
    ac.[ID] as [ATTRIBUTECATEGORYID],
    @CHANGEAGENTID as [ADDEDBYID],
    getdate() as [DATEADDED],
    @CHANGEAGENTID as [CHANGEDBYID],
    getdate() as [DATECHANGED],
    1 as [ISLISTBUILDERATTRIBUTE]
  from dbo.[ATTRIBUTECATEGORY] ac
  left join dbo.[OLAPDATASOURCEATTRIBUTECATEGORY] oac on ac.[ID] = oac.[ATTRIBUTECATEGORYID]
  where ac.[ISLISTBUILDERATTRIBUTE] = 1 and oac.[ID] is null;

  --Update then control table for all attributes categories that are no longer needed for list builders

    update oac
    set
        [ISLISTBUILDERATTRIBUTE] = 0
    from dbo.[OLAPDATASOURCEATTRIBUTECATEGORY] oac
    inner join dbo.[ATTRIBUTECATEGORY] ac on ac.[ID] = oac.[ATTRIBUTECATEGORYID]
    where oac.[ISLISTBUILDERATTRIBUTE] = 1 and ac.[ISLISTBUILDERATTRIBUTE] = 0;

    --Create all the needed audit tables to track deletes

    exec dbo.[USP_OLAPATTRIBUTECATEGORYSELECTION_CREATEETLDELETEDID];

end