USP_REVENUE_BENEFITS_VALIDATEGLMAPPINGS

CREATE procedure dbo.USP_REVENUE_BENEFITS_VALIDATEGLMAPPINGS
(
  @TYPECODE tinyint = 0,
  @PDACCOUNTSYSTEMID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @BENEFITS xml = null,
  @PERCENTAGEBENEFITS xml = null
)
as
begin
  set nocount on;

  declare @ERRORCODE tinyint;
  declare @ERRORCODE2 tinyint;
  declare @PDACCOUNTCODEMAPPINGID uniqueidentifier;
  declare @UNMAPPEDACCOUNTID uniqueidentifier = null;
  declare @UNMAPPEDBENEFITNAME nvarchar(100);
  declare @ACCTSYSNAME nvarchar(50);
  declare @MESSAGE nvarchar(2000);

  --Validate benefits only if BasicGL is installed and Revenue Type is Payment or Pledge (not Recurring Gift)

  if dbo.UFN_VALID_BASICGL_INSTALLED() != 0 and ((@TYPECODE = 0) or (@TYPECODE = 1))
  begin
    if @PDACCOUNTSYSTEMID is null
      set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID);

      select
        @UNMAPPEDACCOUNTID = DEFAULTGLACCOUNTID
      from
        dbo.PDACCOUNTSYSTEM
      where
        PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID;

    if @PDACCOUNTSYSTEMID is not null and @UNMAPPEDACCOUNTID is null
    begin
      select
        @ACCTSYSNAME = NAME
      from
        dbo.PDACCOUNTSYSTEM
      where
        ID = @PDACCOUNTSYSTEMID;

      declare @PDBENEFITMAPPINGIDTABLE table
      (
        BENEFITID uniqueidentifier,
        BATCHREVID uniqueidentifier,
        BENEFITMAPPINGID uniqueidentifier,
        LIABILITYMAPPINGID uniqueidentifier
      );

      insert into @PDBENEFITMAPPINGIDTABLE
      (
        BENEFITID,
        BATCHREVID
      )
      select
        T1.BENEFITID,
        T2.BATCHREVENUEID
      from
        dbo.UFN_REVENUEBATCH_GETBENEFITS_FROMITEMLISTXML(@BENEFITS) as T1
      inner join
        dbo.BATCHREVENUEBENEFIT as T2 on T1.ID = T2.ID

      union

      select
        T1.BENEFITID,
        T2.BATCHREVENUEID
      from
        dbo.UFN_REVENUEBATCH_GETBENEFITSPCT_FROMITEMLISTXML(@PERCENTAGEBENEFITS) as T1
      inner join
        dbo.BATCHREVENUEBENEFITPCT as T2 on T1.ID = T2.ID     

      if @TYPECODE = 1 
      begin
        if exists(select 1 from dbo.BENEFIT as T1 join @PDBENEFITMAPPINGIDTABLE as T2 on T1.ID = T2.BENEFITID where T1.SENDBENEFITCODE = 1)
        begin

          if not exists(select 1 from PDACCOUNTCODEMAPPING where OFFICEID = 4 and (REVENUETYPE & 8) = 8 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
            set @ERRORCODE2 = 1
          else
          begin
            if not exists (select 1 from dbo.PDACCOUNTCODEMAPPING as T1 inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE as T2 on T1.ID = T2.PDACCOUNTCODEMAPPINGID
              where OFFICEID = 4 and (REVENUETYPE & 8) = 8 and T2.SUBTYPEID = '99999999-9999-9999-9999-999999999999' and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
            begin
              if exists (select 1 from dbo.PDACCOUNTCODEMAPPING as T1
                  inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE as T2 on T1.ID = T2.PDACCOUNTCODEMAPPINGID
                  left outer join dbo.BATCHREVENUE as T3 on T3.PLEDGESUBTYPEID = T2.SUBTYPEID
                  left outer join @PDBENEFITMAPPINGIDTABLE as T4 on T3.ID = T4.BATCHREVID
                  where T1.OFFICEID = 4 and (T1.REVENUETYPE & 8) = 8 and T1.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and T3.ID is null )
                set @ERRORCODE2 = 1
            end
          end
        end
      end

      if --Type is Payment and Payments not mapped in GL

        (@TYPECODE = 0 and not exists (select * from dbo.PDACCOUNTCODEMAPPING where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID AND (REVENUETYPE & 1) = 1 AND OFFICEID = 5))
        or
        --Type is Pledge and Pledges not mapped in GL

        (@TYPECODE = 1 and not exists (select * from dbo.PDACCOUNTCODEMAPPING where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID AND (REVENUETYPE & 4) = 4 AND OFFICEID = 5))
          set @ERRORCODE = 0

      --If all subtypes are not mapped, then check individual mappings

      if not exists (select T1.ID from dbo.PDACCOUNTCODEMAPPINGSUBTYPE as T1
        inner join PDACCOUNTCODEMAPPING as T2 on T2.ID = T1.PDACCOUNTCODEMAPPINGID
        where T1.ADDITIONALSUBTYPE = 6 and SUBTYPEID = '99999999-9999-9999-9999-999999999999'
        and REVENUETYPE & (case @TYPECODE when 0 then  1 else 4 end) = (case @TYPECODE when 0 then  1 else 4 end))
      begin
        declare @UNMAPPEDBENEFITID uniqueidentifier = null;

        if exists (select 1 from @PDBENEFITMAPPINGIDTABLE as T1
            where T1.BENEFITID not in 
            (select T2.SUBTYPEID from PDACCOUNTCODEMAPPINGSUBTYPE as T2  
            join PDACCOUNTCODEMAPPING as T3 on T3.ID = T2.PDACCOUNTCODEMAPPINGID
            where T3.OFFICEID = 5 and T3.PDACCOUNTSYSTEMID  =  @PDACCOUNTSYSTEMID 
            and REVENUETYPE & (case @TYPECODE when 0 then  1 else 4 end) = (case @TYPECODE when 0 then  1 else 4 end)))                            
          select top 1 @UNMAPPEDBENEFITID = T1.BENEFITID
          from @PDBENEFITMAPPINGIDTABLE as T1
          where T1.BENEFITID not in 
          (select  T2.SUBTYPEID from PDACCOUNTCODEMAPPINGSUBTYPE T2  
          join PDACCOUNTCODEMAPPING  T3 on T3.ID = T2.PDACCOUNTCODEMAPPINGID
          where T3.OFFICEID = 5 and T3.PDACCOUNTSYSTEMID  =  @PDACCOUNTSYSTEMID 
          and REVENUETYPE & (case @TYPECODE when 0 then  1 else 4 end) = (case @TYPECODE when 0 then  1 else 4 end))

        if @UNMAPPEDBENEFITID is not null
        begin
          set @ERRORCODE = 1;
          select @UNMAPPEDBENEFITNAME = NAME from dbo.BENEFIT where ID = @UNMAPPEDBENEFITID;
        end
      end                     
    end
  end

      --Variables for error reporting

  declare @OFFICENAME nvarchar(100);
  declare @REVENUETYPENAME nvarchar(50);

  -- Report errors, if needed

  if @ERRORCODE is not null
  begin
    -- Get account system name

    select @ACCTSYSNAME = NAME from dbo.PDACCOUNTSYSTEM where ID = @PDACCOUNTSYSTEMID;

    -- Get Office and Revenue Type name

    select
      @OFFICENAME = t1.NAME,
      @REVENUETYPENAME = t2.TYPENAME 
    from
      dbo.PDACCOUNTCODEMAPOFFICE as t1
    left join
      dbo.PDACCOUNTCODEMAPREVENUETYPE as t2 on t1.ID = t2.PDACCOUNTCODEMAPOFFICEID and t2.NAMEID = case when @TYPECODE = 0 then 1 else 4 end and t2.OFFICEID = 5          
    where
      t1.OFFICEID = 5

    -- Make error message, depending on which error was thrown

    if @ERRORCODE = 0
      set @MESSAGE = 'In account system "' + @ACCTSYSNAME + '", a mapping does not exist for Office: '+ @OFFICENAME + isnull(', Revenue type: '+ @REVENUETYPENAME,'') + ', Subtype not specified.';
    else
      set @MESSAGE = 'In account system "' + @ACCTSYSNAME + '", a mapping does not exist for Office: '+ @OFFICENAME + ', ' + @UNMAPPEDBENEFITNAME;

    raiserror(@MESSAGE, 13, 1);
  end

  if @ERRORCODE is null and @ERRORCODE2 = 1
  begin            
    select
      @OFFICENAME = t1.NAME,
      @REVENUETYPENAME = t2.TYPENAME
    from
      dbo.PDACCOUNTCODEMAPOFFICE as t1
    inner join
      dbo.PDACCOUNTCODEMAPREVENUETYPE as t2 on t1.ID = t2.PDACCOUNTCODEMAPOFFICEID and t2.NAMEID = 8 and t1.OFFICEID = 4

    select @ACCTSYSNAME = NAME from dbo.PDACCOUNTSYSTEM where ID = @PDACCOUNTSYSTEMID;

    set @MESSAGE = 'In account system "' + @ACCTSYSNAME + '", a mapping does not exist for Office: '+ @OFFICENAME + isnull(', Revenue type: '+ @REVENUETYPENAME,'');

    raiserror(@MESSAGE, 13, 1);
  end

  return 0;
end