USP_MKTCONSTITUENTFILEIMPORTPROCESS_GETOPTIONS

CREATE procedure dbo.[USP_MKTCONSTITUENTFILEIMPORTPROCESS_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.[MKTCONSTITUENTFILEIMPORTPROCESS]
  inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[RECORDTYPEID] = [MKTCONSTITUENTFILEIMPORTPROCESS].[RECORDTYPEID]
  where [MKTCONSTITUENTFILEIMPORTPROCESS].[ID] = @ID
  and [QUERYVIEWCATALOG].[ID] in ('09A6D736-2ACB-42FF-87DB-F4F92A2025E1', 'EB67DCAC-60B8-4D81-BA86-C2C08C96CC1D', '33739C2C-393F-4DAE-B61E-F9B0B3733807');

  if @BASETABLENAME is null
    begin
      select
        @BASETABLENAME = [RECORDTYPE].[BASETABLENAME]
      from dbo.[MKTCONSTITUENTFILEIMPORTPROCESS]
      inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [MKTCONSTITUENTFILEIMPORTPROCESS].[RECORDTYPEID]
      where [MKTCONSTITUENTFILEIMPORTPROCESS].[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');
    end

  select
    [MKTCONSTITUENTFILEIMPORTPROCESS].[ID],
    [MKTCONSTITUENTFILEIMPORTPROCESS].[SEGMENTATIONID],
    [MKTCONSTITUENTFILEIMPORTPROCESS].[LISTLAYOUTID],
    [MKTCONSTITUENTFILEIMPORTPROCESS].[QUANTITY],
    (select top 1 [PRIMARYKEYTYPENAME]
     from dbo.[QUERYVIEWCATALOG]
     where [QUERYVIEWCATALOG].[RECORDTYPEID] = [MKTCONSTITUENTFILEIMPORTPROCESS].[RECORDTYPEID]
     and [ROOTOBJECT] = 1
     and [PRIMARYKEYTYPENAME] is not null
     and [PRIMARYKEYTYPENAME] <> '') as [DATATYPE],
     @BASETABLENAME as [BASETABLENAME],
     @BASETABLEPRIMARYKEY as [BASETABLEPRIMARYKEY]
  from dbo.[MKTCONSTITUENTFILEIMPORTPROCESS] 
  inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [MKTCONSTITUENTFILEIMPORTPROCESS].[RECORDTYPEID]
  where [MKTCONSTITUENTFILEIMPORTPROCESS].[ID] = @ID;

  return 0;