UFN_GLACCOUNT_GETACCOUNTSFROMPARAMETERS

    CREATE function [dbo].[UFN_GLACCOUNT_GETACCOUNTSFROMPARAMETERS] (@InputTable UDT_MAPPEDVALUESFORGETACCOUNTS readonly)
    returns @OutputTable table (ID uniqueidentifier, DEBITGLACCOUNTSID uniqueidentifier, DEBITGLACCOUNTNUMBER varchar(100), CREDITGLACCOUNTSID uniqueidentifier, CREDITGLACCOUNTNUMBER varchar(100), ACCOUNTNUMBERERROR tinyint)
    --ACCOUNTNUMBERERROR = 0, no error; 1, AccountCode; 2, Segments

    as
    begin
    declare @NumSegments int = (select count(*) from dbo.PDACCOUNTSTRUCTURE)
    declare @AccountCodeSegmentID uniqueidentifier = (select ID from dbo.PDACCOUNTSTRUCTURE where SEGMENTTYPE = 1)

    declare @SegmentsWorkTable table (ID uniqueidentifier, PDAccountStructureID uniqueidentifier, PDAccountSegmentValueID uniqueidentifier NULL, TransactionType tinyint, SegType tinyint, PRIMARY KEY (ID, PDAccountStructureID, TransactionType))
    declare @InputLines table (ID uniqueidentifier, TableID uniqueidentifier, MappedID uniqueidentifier, PRIMARY KEY (ID, TableID))
    declare @FinalTable table (ID uniqueidentifier, GLACCOUNTID uniqueidentifier, ACCOUNTNUMBER varchar(100), TransactionType tinyint, PRIMARY KEY (ID, TransactionType, GLACCOUNTID))
  declare @AccountCodeMappingTable table (ID uniqueidentifier, PDAccountStructureID uniqueidentifier, PDAccountSegmentValueID uniqueidentifier NULL, GLAccountID uniqueidentifier NULL, TransactionType tinyint, PRIMARY KEY (ID, TransactionType))

  insert into @AccountCodeMappingTable (ID, PDAccountStructureID, PDAccountSegmentValueid, GLAccountID, TransactionType)
  select distinct TV1.ID, V3.PDACCOUNTSTRUCTUREID,
  case V3.TransactionType when 1 then V1.DEBITPDACCOUNTSEGMENTVALUEID when 2 then V1.CREDITPDACCOUNTSEGMENTVALUEID end,
  case V3.TransactionType when 1 then V1.DEBITGLACCOUNTID when 2 then V1.CREDITGLACCOUNTID end,
  V3.TransactionType
  from
  (select distinct t1.DEBITGLACCOUNTID, t1.CREDITGLACCOUNTID, t1.DEBITPDACCOUNTSEGMENTVALUEID, t1.CREDITPDACCOUNTSEGMENTVALUEID, t2.ID as PAYMENTMETHODID, t3.ID as APPLICATIONTYPEID, t4.ID as REVENUETYPEID, t5.SUBTYPEID, t1.PDACCOUNTCODEMAPOFFICEID
  from PDACCOUNTCODEMAPPING t1 left join PDACCOUNTCODEMAPPAYMENTMETHODTYPE t2 on t1.PDACCOUNTCODEMAPOFFICEID = t2.PDACCOUNTCODEMAPOFFICEID and (t1.PAYMENTMETHOD & t2.NAMEID) > 0
  left join PDACCOUNTCODEMAPAPPLICATIONTYPE t3 on t1.PDACCOUNTCODEMAPOFFICEID = t3.PDACCOUNTCODEMAPOFFICEID and (t1.APPLICATIONTYPE & t3.NAMEID) > 0
  left join PDACCOUNTCODEMAPREVENUETYPE t4 on t1.PDACCOUNTCODEMAPOFFICEID = t4.PDACCOUNTCODEMAPOFFICEID and (t1.REVENUETYPE & t4.NAMEID) > 0
  left join PDACCOUNTCODEMAPPINGSUBTYPE t5 on t1.ID = t5.PDACCOUNTCODEMAPPINGID
  where t1.PDACCOUNTCODEMAPOFFICEID in (select PDACCOUNTCODEMAPOFFICEID from @InputTable)) V1
  right join
  @InputTable TV1 on (V1.PaymentMethodID = TV1.PaymentMethodID or TV1.PaymentMethodID is null)
        and (V1.APPLICATIONTYPEID = TV1.APPLICATIONTYPEID or TV1.APPLICATIONTYPEID is null)
        and (V1.REVENUETYPEID = TV1.REVENUETYPEID or TV1.REVENUETYPEID is null or TV1.REVENUETYPEID  = '00000000-0000-0000-0000-000000000000')
        and (V1.SubTypeID = TV1.SubTypeID or TV1.SubTypeID is null or V1.SubTypeID = '99999999-9999-9999-9999-999999999999')
        and V1.PDACCOUNTCODEMAPOFFICEID = TV1.PDACCOUNTCODEMAPOFFICEID
  cross join 
  (select @AccountCodeSegmentID as PDACCOUNTSTRUCTUREID, 1 as TransactionType union all select @AccountCodeSegmentID, 2) V3

  --Accounts where account code mapping specifies the account number

  insert into @FinalTable (ID, GLACCOUNTID, ACCOUNTNUMBER, TransactionType)
  select t1.ID, GLAccountID, t2.ACCOUNTNUMBER, TransactionType
  from @AccountCodeMappingTable t1 inner join dbo.GLACCOUNT t2 on t1.GLACCOUNTID = t2.ID

    --The next three steps translate the input arguments into PDACCOUNTSTRUCTUREID and PDACCOUNTSEGMENTVALUEID for each segment 

    --Insert the account code segments (1 each for debit (1) and credit (2))

  insert into @SegmentsWorkTable (ID, PDAccountStructureID, PDAccountsegmentValueID, TransactionType, SegType)
  select ID, PDAccountStructureID, PDAccountSegmentValueID, TransactionType, 1
  from @AccountCodeMappingTable t1
  where not exists (select * from @FinalTable where ID = t1.ID and TransactionType = t1.TransactionType)

    if (@@RowCount > 0)
      begin
        --Table of the values I'm asking for

        insert into @InputLines (ID, TableID, MappedID)          
        select ID, TABLEID, MAPPEDID
        from @InputTable
        UNPIVOT (TABLEID for WhichTable IN (SEGMENT03TABLEID, SEGMENT04TABLEID, SEGMENT05TABLEID, SEGMENT06TABLEID, SEGMENT07TABLEID, SEGMENT08TABLEID, SEGMENT09TABLEID, SEGMENT10TABLEID, SEGMENT11TABLEID, SEGMENT12TABLEID, SEGMENT13TABLEID, SEGMENT14TABLEID, SEGMENT15TABLEID, SEGMENT16TABLEID, SEGMENT17TABLEID, SEGMENT18TABLEID, SEGMENT19TABLEID, SEGMENT20TABLEID, SEGMENT21TABLEID, SEGMENT22TABLEID, SEGMENT23TABLEID, SEGMENT24TABLEID, SEGMENT25TABLEID, SEGMENT26TABLEID, SEGMENT27TABLEID, SEGMENT28TABLEID, SEGMENT29TABLEID, SEGMENT30TABLEID)) as TABLEID
        UNPIVOT (MAPPEDID for WhichTable2 IN (SEGMENT03ID, SEGMENT04ID, SEGMENT05ID, SEGMENT06ID, SEGMENT07ID, SEGMENT08ID, SEGMENT09ID, SEGMENT10ID, SEGMENT11ID, SEGMENT12ID, SEGMENT13ID, SEGMENT14ID, SEGMENT15ID, SEGMENT16ID, SEGMENT17ID, SEGMENT18ID, SEGMENT19ID, SEGMENT20ID, SEGMENT21ID, SEGMENT22ID, SEGMENT23ID, SEGMENT24ID, SEGMENT25ID, SEGMENT26ID, SEGMENT27ID, SEGMENT28ID, SEGMENT29ID, SEGMENT30ID)) as MAPPEDID
        where left(WhichTable,9) = left(WhichTable2,9)
        and MAPPEDID is not null

        --Insert the normal segments

        insert into @SegmentsWorkTable(ID, PDAccountStructureID, PDAccountSegmentValueID, TransactionType, SegType)
        select V1.ID, V1.PDACCOUNTSTRUCTUREID, t3.PDACCOUNTSEGMENTVALUEID, V3.TransactionType, 2
        from
        (select IT.ID, t1.ID as PDACCOUNTSTRUCTUREID, IL.MAPPEDID
        from @InputTable as IT inner join dbo.PDACCOUNTSTRUCTURE t1 on t1.SEGMENTTYPE = 3
        inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT t2 on t1.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = t2.ID
        left join @InputLines as IL on t2.TABLEID = IL.TableID and IT.ID = IL.ID) V1
        inner join dbo.PDACCOUNTSEGMENTMAPPING t3 on V1.PDACCOUNTSTRUCTUREID = t3.PDACCOUNTSTRUCTUREID and (V1.MappedID = t3.LONGDESCRIPTIONID or (nullif(V1.MappedID,'99999999-9999-9999-9999-999999999999') is null and t3.ISDEFAULT = 1))
        cross join (select 1 as TransactionType union all select 2) V3
      where not exists (select * from @FinalTable where ID = V1.ID and TransactionType = V3.TransactionType)

        --Insert the composite segments

          if exists(select * from pdaccountstructure where segmenttype = 4)
            with CTE (ID, PDAccountStructureID, Sequence, TableID)
            as 
            (select IT.ID, t1.ID as PDAccountStructureID, t2.SEQUENCE, t3.TABLEID
            from @InputTable IT inner join dbo.PDACCOUNTSTRUCTURE t1 on t1.SEGMENTTYPE = 4
            inner join dbo.PDCOMPOSITESEGMENTTABLELIST t2 on t1.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = t2.PDCOMPOSITESEGMENTID
            inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT t3 on t2.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = t3.ID)

            insert into @SegmentsWorkTable(ID, PDAccountStructureID, PDAccountSegmentValueID, TransactionType, SegType)
            select V1.ID, V1.PDAccountStructureID, t4.PDACCOUNTSEGMENTVALUEID, V3.TransactionType,2
            from
            (select ID, PDAccountStructureID,
                (select (''+cast(isnull(MAPPEDID,'99999999-9999-9999-9999-999999999999') as nchar(36)))
                from CTE as CTE_2 left join @InputLines IL on CTE_2.ID = IL.ID and CTE_2.TableID = IL.TableID
                where CTE_2.ID = CTE_1.ID and CTE_2.PDAccountStructureID = CTE_1.PDAccountStructureID
                order by CTE_2.Sequence
                for XML path('')) as MappedID
            from CTE as CTE_1
            group by ID, PDAccountStructureID) V1
            inner join dbo.PDCOMPOSITESEGMENTMAPPING t4 on V1.PDACCOUNTSTRUCTUREID = t4.PDACCOUNTSTRUCTUREID and V1.MappedID = t4.COMPOSITESEGMENTKEY    
            cross join (select 1 as TransactionType union all select 2) V3
        where not exists (select * from @FinalTable where ID = V1.ID and TransactionType = V3.TransactionType)


        --Using a table variable instead of a CTE so I can get the index on ID and TransactionType

        --Compare what I want to what is available in PDACCOUNTSEGMENT

        insert into @FinalTable(ID, GLACCOUNTID, ACCOUNTNUMBER, TransactionType)
        select V1.ID, V1.GLACCOUNTID, GLACCOUNT.ACCOUNTNUMBER, V1.TransactionType
        from
        (select SWT.ID, t1.GLACCOUNTID, SWT.TransactionType    
        from @SegmentsWorkTable SWT inner join dbo.PDACCOUNTSEGMENT t1 on SWT.PDACCOUNTSTRUCTUREID = t1.PDACCOUNTSTRUCTUREID and SWT.PDACCOUNTSEGMENTVALUEID = t1.PDACCOUNTSEGMENTVALUEID
        group by SWT.ID, SWT.TransactionType, t1.GLACCOUNTID
        having count(*) = @NumSegments) V1
        inner join dbo.GLACCOUNT on V1.GLACCOUNTID = GLACCOUNT.ID

      end

    --PIVOT the results and add an errorcode for lines that don't have both debit and credit accounts

    --simple self join is simpler and easier to read than the PIVOT command

    insert into @OutputTable (ID, DEBITGLACCOUNTSID, DEBITGLACCOUNTNUMBER, CREDITGLACCOUNTSID, CREDITGLACCOUNTNUMBER, ACCOUNTNUMBERERROR)
    select t1.ID, t2.GLACCOUNTID, t2.ACCOUNTNUMBER, t3.GLACCOUNTID, t3.ACCOUNTNUMBER, 
    case when t2.GLACCOUNTID is null or t3.GLACCOUNTID is null then (select case count(isnull(PDAccountSegmentValueID,GLAccountID)) when 2 then 2 else 1 end from @AccountCodeMappingTable where ID = t1.ID group by ID) 
    else 0
    end
    from @InputTable t1 left join @FinalTable t2 on t1.ID = t2.ID and t2.TransactionType = 1
    left join @FinalTable t3 on t1.ID = t3.ID and t3.TransactionType = 2

    return
    end