USP_IMPORTSELECTIONPROCESS_GETOPTIONS

CREATE procedure dbo.[USP_IMPORTSELECTIONPROCESS_GETOPTIONS]
(
  @ID uniqueidentifier
)
as
  set nocount on;

  declare @BASETABLENAME nvarchar(128);
  declare @BASETABLEPRIMARYKEY nvarchar(128);

  --Check if the record type matches one of the RE7 query views (Constituent, Gift, or Appeal).  Because the RE7

  --data mart tables are not real TableSpec tables and record types (ie - we use synonyms to access the tables in

  --the RE7 schema), we need to hard-code the information here.

  select
    @BASETABLENAME = (case [QUERYVIEWCATALOG].[ID]
                        when '09A6D736-2ACB-42FF-87DB-F4F92A2025E1' then 'RE7_CONSTITUENT'
                        when 'EB67DCAC-60B8-4D81-BA86-C2C08C96CC1D' then 'RE7_REVENUESPLIT'
                        when '33739C2C-393F-4DAE-B61E-F9B0B3733807' then 'RE7_APPEAL'
                      end),
    @BASETABLEPRIMARYKEY = 'LOCALID'
  from dbo.[IMPORTSELECTIONPROCESS]
  inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[RECORDTYPEID] = [IMPORTSELECTIONPROCESS].[RECORDTYPEID]
  where [IMPORTSELECTIONPROCESS].[ID] = @ID
  and [QUERYVIEWCATALOG].[ID] in ('09A6D736-2ACB-42FF-87DB-F4F92A2025E1', 'EB67DCAC-60B8-4D81-BA86-C2C08C96CC1D', '33739C2C-393F-4DAE-B61E-F9B0B3733807');

  -- another special case: the 'Audit table' record type really means TABLECATALOG

  if @BASETABLENAME is null
    select
      @BASETABLENAME = 'TABLECATALOG',
      @BASETABLEPRIMARYKEY = 'ID'
    from dbo.[IMPORTSELECTIONPROCESS]
    inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [IMPORTSELECTIONPROCESS].[RECORDTYPEID]
    where [IMPORTSELECTIONPROCESS].[ID] = @ID
    and [RECORDTYPE].[NAME] = 'Audit table';

  if @BASETABLENAME is null
    begin
      select
        @BASETABLENAME = [RECORDTYPE].[BASETABLENAME]
      from dbo.[IMPORTSELECTIONPROCESS]
      inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [IMPORTSELECTIONPROCESS].[RECORDTYPEID]
      where [IMPORTSELECTIONPROCESS].[ID] = @ID;

      set @BASETABLEPRIMARYKEY = (select [CCU].[COLUMN_NAME]
                                  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as [TC]
                                  inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as [CCU] on [CCU].[TABLE_SCHEMA] = [TC].[TABLE_SCHEMA] and [CCU].[TABLE_NAME] = [TC].[TABLE_NAME] and [CCU].[CONSTRAINT_NAME] = [TC].[CONSTRAINT_NAME]
                                  where [TC].[TABLE_SCHEMA] = 'dbo'
                                  and [TC].[TABLE_NAME] = @BASETABLENAME
                                  and [TC].[CONSTRAINT_TYPE] = 'PRIMARY KEY');

      --The revenue table has been changed to be an updatable view with the FTM. So the primary key 

      --information will not be found in the table schema but it we need to return the ID column to maintain compatibility.

      if @BASETABLENAME = 'REVENUE' and @BASETABLEPRIMARYKEY is null
        set @BASETABLEPRIMARYKEY = 'ID';
    end

  select
    [IMPORTSELECTIONPROCESS].[USEINQUERYDESIGNER],
    [IMPORTSELECTIONPROCESS].[IDFIELD],
    [IMPORTSELECTIONPROCESS].[IDTYPECODE],
    [IMPORTSELECTIONPROCESS].[ALTERNATELOOKUPIDTYPECODEID],
    [IMPORTSELECTIONPROCESS].[STATUSCODE],
    [IMPORTSELECTIONPROCESS].[IDSETREGISTERID],
    dbo.[UFN_IMPORTSELECTION_MAKETABLENAME]([IMPORTSELECTIONPROCESS].[ID]) as [IDSETTABLENAME],
    [RECORDTYPE].[NAME] as [RECORDTYPE],
    @BASETABLENAME as [BASETABLENAME],
    @BASETABLEPRIMARYKEY as [BASETABLEPRIMARYKEY],
    (select top 1 [PRIMARYKEYTYPENAME]
     from dbo.[QUERYVIEWCATALOG]
     where [QUERYVIEWCATALOG].[RECORDTYPEID] = [IMPORTSELECTIONPROCESS].[RECORDTYPEID]
     and [ROOTOBJECT] = 1
     and [PRIMARYKEYTYPENAME] is not null
     and [PRIMARYKEYTYPENAME] <> '') as [BASETABLEPRIMARYKEYTYPENAME],
    [IMPORTSELECTIONPROCESS].[NAME]
  from dbo.[IMPORTSELECTIONPROCESS]
  inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [IMPORTSELECTIONPROCESS].[RECORDTYPEID]
  where [IMPORTSELECTIONPROCESS].[ID] = @ID;

  return 0;