UFN_PDCOMPOSITESEGMENTMAPPINGENTRY_VALIDMAPPING_2

CREATE function dbo.UFN_PDCOMPOSITESEGMENTMAPPINGENTRY_VALIDMAPPING_2 (
  @ID uniqueidentifier
  ,@PDCOMPOSITESEGMENTMAPPINGID uniqueidentifier
  ,@DATECHANGED datetime
  )
returns bit
  with execute as caller
as
begin
  declare @Valid bit = 1
  declare @PDACCOUNTSYSTEMID uniqueidentifier

  select @PDACCOUNTSYSTEMID = b.PDACCOUNTSYSTEMID
  from PDCOMPOSITESEGMENTMAPPING as a
  inner join PDACCOUNTSTRUCTURE as b on a.PDACCOUNTSTRUCTUREID = b.ID
  where a.ID = @PDCOMPOSITESEGMENTMAPPINGID

  declare @NumTables tinyint
  declare @NumTablesProcessed tinyint
  declare @NumTablesMatched tinyint

  select @NumTables = count(distinct a.ID)
  from PDCOMPOSITESEGMENTTABLELIST as a
  inner join PDCOMPOSITESEGMENTMAPPING as b on a.PDCOMPOSITESEGMENTID = b.PDCOMPOSITESEGMENTID
  inner join PDCOMPOSITESEGMENTMAPPINGENTRY as c on c.PDCOMPOSITESEGMENTMAPPINGID = b.ID
  where b.ID = @PDCOMPOSITESEGMENTMAPPINGID

  select @NumTablesProcessed = count(ID)
  from PDCOMPOSITESEGMENTMAPPINGENTRY
  where PDCOMPOSITESEGMENTMAPPINGID = @PDCOMPOSITESEGMENTMAPPINGID

  if @NumTablesProcessed = @NumTables
  begin
    declare @COMPOSITESEGMENTKEY nvarchar(144)

    set @COMPOSITESEGMENTKEY = dbo.UFN_PDCOMPOSITESEGMENTMAPPING_KEY(@PDCOMPOSITESEGMENTMAPPINGID)

    if exists (
        select top 1 1
        from dbo.PDCOMPOSITESEGMENTMAPPING
        inner join dbo.PDACCOUNTSTRUCTURE on PDCOMPOSITESEGMENTMAPPING.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
        where PDCOMPOSITESEGMENTMAPPING.ID != @PDCOMPOSITESEGMENTMAPPINGID
          and PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
          and case 
            when len(COALESCE(COMPOSITESEGMENTKEY, '')) = 0
              then dbo.UFN_PDCOMPOSITESEGMENTMAPPING_KEY(PDCOMPOSITESEGMENTMAPPING.ID)
            else COMPOSITESEGMENTKEY
            end = @COMPOSITESEGMENTKEY
          and PDCOMPOSITESEGMENTID in (
            select PDCOMPOSITESEGMENTID
            from PDCOMPOSITESEGMENTMAPPING as MAPPING
            inner join PDACCOUNTSTRUCTURE as STRUCT on MAPPING.PDACCOUNTSTRUCTUREID = STRUCT.ID
            where MAPPING.ID = @PDCOMPOSITESEGMENTMAPPINGID
              and STRUCT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
            )
        )
      set @Valid = 0
  end

  return @Valid
end