USP_DATAFORMTEMPLATE_EDIT_EXPORTPROCESS_2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_EXPORTPROCESS_2
(
  @ID uniqueidentifier,
  @ADHOCQUERYID uniqueidentifier,
  @SMARTQUERYINSTANCEID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @NAME nvarchar(255),
  @DESCRIPTION nvarchar(1000),
  @EXPORTDEFINITIONID uniqueidentifier,
  @SELECTIONID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @SITEID uniqueidentifier
)
with execute as caller
as begin
  set nocount on;

  declare @BUSINESSPROCESSCATALOGID uniqueidentifier = '64FAA344-9C75-4C98-AFE3-A40EC2DF9249';
  declare @CURRENTDATE datetime = getdate();

  begin try
    if @SITEID is not null
      if dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, @SITEID) = 0
        begin
          raiserror ('ERR_SITE_NOACCESS', 13, 1);
          return 1;
        end

    --Bug 438143:BB753056: error: conversion failed when converting from a character string to unique identifier when including a User Defined Smart Query selection in an Export Process

    --When we are adding an export process based on a selection, make sure that the selection uses a guid as its ID field.

    --We do not need to check for a blank primary key field because you cannot create a selection without specifying a primary key field.

    if @SELECTIONID is not null and exists
    (
        --we only care about smart query selections for this check

        select
            1
        from
            dbo.IDSETREGISTER
            inner join dbo.IDSETREGISTERSMARTQUERYINSTANCE on IDSETREGISTERSMARTQUERYINSTANCE.IDSETREGISTERID = IDSETREGISTER.ID
        where
            IDSETREGISTER.ID = @SELECTIONID
    )
    begin

        declare @DATATYPE nvarchar(50);
        declare @SELECTIONOBJECT nvarchar(128);

        with xmlnamespaces('bb_appfx_smartquery' as ns)
        select
            @DATATYPE = SPECXML.NODE.value('@DataType','nvarchar(50)'),
            @SELECTIONOBJECT = IDSETREGISTER.DBOBJECTNAME
        from
            dbo.IDSETREGISTER
            inner join dbo.IDSETREGISTERSMARTQUERYINSTANCE on IDSETREGISTERSMARTQUERYINSTANCE.IDSETREGISTERID = IDSETREGISTER.ID
            inner join dbo.SMARTQUERYINSTANCE on IDSETREGISTERSMARTQUERYINSTANCE.SMARTQUERYINSTANCEID = SMARTQUERYINSTANCE.ID
            inner join dbo.SMARTQUERYCATALOG on SMARTQUERYINSTANCE.SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID
            cross apply SMARTQUERYCATALOG.SMARTQUERYSPEC.nodes('ns:SmartQuerySpec/ns:Output/ns:OutputFields/ns:OutputField') SPECXML(NODE)
        where
            SPECXML.NODE.value('@Name','nvarchar(128)') = SMARTQUERYCATALOG.PRIMARYKEYFIELD
            and IDSETREGISTER.ID = @SELECTIONID;

        if @DATATYPE is null or @DATATYPE <> N'Guid'
            --The selection must use a system record ID field as its primary key field.

            raiserror('BBERR_PRIMARYKEYNOTGUID : You must use a selection that includes a system record ID in the primary key field.',13,1);
        else
        begin

            --If the smart query was changed after the selection was created but the selection was not updated to reflect this,

            --there could still be a problem that we cannot find by looking at the tables.


            set @DATATYPE = null;
            select
                @DATATYPE = t.name
            from
                sys.all_columns c
                inner join sys.types t on t.system_type_id = c.system_type_id
            where
                c.name = N'ID'
                and c.object_id = object_id(@SELECTIONOBJECT);

            if @DATATYPE <> N'uniqueidentifier'
                --The selection must use a system record ID field as its primary key. The user-defined smart query has been updated to

                --use a system record ID but the selection has not been updated to reflect this.

                raiserror('BBERR_PRIMARYKEYNOTGUID : You must use a selection that includes a system record ID in the primary key field.',13,1);
        end
    end

    update dbo.EXPORTPROCESS set
      ADHOCQUERYID = @ADHOCQUERYID,
      SMARTQUERYINSTANCEID = @SMARTQUERYINSTANCEID,
      NAME = @NAME,
      DESCRIPTION = @DESCRIPTION,
      EXPORTDEFINITIONID = @EXPORTDEFINITIONID,
      SELECTIONID = @SELECTIONID,
      CHANGEDBYID = @CHANGEAGENTID, 
      DATECHANGED = @CURRENTDATE
    where ID = @ID;

    if exists (select ID from dbo.BUSINESSPROCESSINSTANCE  where BUSINESSPROCESSINSTANCE.BUSINESSPROCESSPARAMETERSETID = @ID and BUSINESSPROCESSINSTANCE.BUSINESSPROCESSCATALOGID = @BUSINESSPROCESSCATALOGID)
      update dbo.BUSINESSPROCESSINSTANCE set
        SITEID = @SITEID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where BUSINESSPROCESSINSTANCE.BUSINESSPROCESSPARAMETERSETID = @ID
        and BUSINESSPROCESSINSTANCE.BUSINESSPROCESSCATALOGID = @BUSINESSPROCESSCATALOGID
        and (BUSINESSPROCESSINSTANCE.SITEID <> @SITEID 
          or BUSINESSPROCESSINSTANCE.SITEID is null and @SITEID is not null 
          or BUSINESSPROCESSINSTANCE.SITEID is not null and @SITEID is null);
    else
      exec dbo.USP_BUSINESSPROCESSINSTANCE_ADD
        @CHANGEAGENTID = @CHANGEAGENTID,
        @BUSINESSPROCESSCATALOGID = @BUSINESSPROCESSCATALOGID,
        @BUSINESSPROCESSPARAMETERSETID = @ID,
        @OWNERID = @CURRENTAPPUSERID, @SITEID = @SITEID;
  end try

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

  return 0;
end