USP_MKTCONSOLIDATEDQUERYVIEW_INSERTUPDATE

CREATE procedure dbo.[USP_MKTCONSOLIDATEDQUERYVIEW_INSERTUPDATE]
(
  @PARENTQUERYCATALOGID uniqueidentifier,
  @CONSOLIDATEDQUERYVIEWCATALOGID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
as
  set nocount on;

  begin try
    if @CHANGEAGENTID is null
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    if not exists (select [ID] from dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] where [ID] = @PARENTQUERYCATALOGID)
      begin
        insert into dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] (
          [ID],
          [CONSOLIDATEDQUERYVIEWCATALOGID],
          [ADDEDBYID],
          [CHANGEDBYID]
        ) values (
          @PARENTQUERYCATALOGID,
          @CONSOLIDATEDQUERYVIEWCATALOGID,
          @CHANGEAGENTID,
          @CHANGEAGENTID
        );

        -- need to update all appeal export output fields that use the parent query ID to use the new consolidated ID

        -- acknowledgement export output fields should remain linked to the record source directly

        update dbo.[MKTEXPORTDEFINITIONOUTPUTFIELD] set
          [QUERYVIEWCATALOGID] = @CONSOLIDATEDQUERYVIEWCATALOGID,
          [CHANGEDBYID] = @CHANGEAGENTID
        from dbo.[MKTEXPORTDEFINITIONOUTPUTFIELD]
        inner join dbo.[MKTEXPORTDEFINITION] on [MKTEXPORTDEFINITION].[ID] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[EXPORTDEFINITIONID]
        where [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID] = @PARENTQUERYCATALOGID
        and [MKTEXPORTDEFINITION].[MAILINGTYPECODE] = 0;
      end
    else
      update dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] set
        [CONSOLIDATEDQUERYVIEWCATALOGID] = @CONSOLIDATEDQUERYVIEWCATALOGID,
        [CHANGEDBYID] = @CHANGEAGENTID
      where [ID] = @PARENTQUERYCATALOGID;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;