UFN_GLACCOUNT_GETACCOUNTSFROMPARAMETERS_3

    CREATE function [dbo].[UFN_GLACCOUNT_GETACCOUNTSFROMPARAMETERS_3] (@InputTable UDT_MAPPEDVALUESFORGETACCOUNTS_2 readonly,
                                                                       @SegmentValues UDT_GLACCOUNT_SEGMENTVALUES 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; 3, Account does not exist

    as
    begin
    declare @NumSegments int = (select count(*) from dbo.PDACCOUNTSTRUCTURE as t1 inner join @InputTable as t2 on t1.PDACCOUNTSYSTEMID = t2.PDACCOUNTSYSTEMID )
    declare @AccountCodeSegmentID uniqueidentifier = (select t1.ID from dbo.PDACCOUNTSTRUCTURE as t1 inner join @InputTable as t2 on t1.PDACCOUNTSYSTEMID = t2.PDACCOUNTSYSTEMID  where t1.SEGMENTTYPE = 1)

    declare @AddToString int = (select count(*) from dbo.PDACCOUNTSTRUCTURE as t1 inner join @InputTable as t2 on t1.PDACCOUNTSYSTEMID = t2.PDACCOUNTSYSTEMID  where t1.sequence = @NumSegments and t1.SEPARATORCODE = 6)

    --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))

    declare @AvailableAcctCodeMappings table (DEBITGLACCOUNTID uniqueidentifier, CREDITGLACCOUNTID uniqueidentifier, DEBITPDACCOUNTSEGMENTVALUEID uniqueidentifier, CREDITPDACCOUNTSEGMENTVALUEID uniqueidentifier,
      PAYMENTMETHODID uniqueidentifier, APPLICATIONTYPEID uniqueidentifier, REVENUETYPEID uniqueidentifier, SUBTYPEID uniqueidentifier, PDACCOUNTCODEMAPOFFICEID uniqueidentifier, PDACCOUNTSYSTEMID uniqueidentifier)

    insert into @AvailableAcctCodeMappings (DEBITGLACCOUNTID, CREDITGLACCOUNTID, DEBITPDACCOUNTSEGMENTVALUEID, CREDITPDACCOUNTSEGMENTVALUEID, PAYMENTMETHODID, APPLICATIONTYPEID, REVENUETYPEID, SUBTYPEID, PDACCOUNTCODEMAPOFFICEID, PDACCOUNTSYSTEMID)
        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, t1.PDACCOUNTSYSTEMID
        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 and (t5.ADDITIONALSUBTYPE in (t2.ADDITIONALSUBTYPE, t3.ADDITIONALSUBTYPE, t4.ADDITIONALSUBTYPE))  -- Assumes ADDITIONALSUBTYPE values are unique for each type

        where t1.PDACCOUNTCODEMAPOFFICEID in (select PDACCOUNTCODEMAPOFFICEID from @InputTable)
        and t1.PDACCOUNTSYSTEMID in (select PDACCOUNTSYSTEMID from @InputTable)


    insert into @AccountCodeMappingTable (ID, PDAccountStructureID, PDAccountSegmentValueid, GLAccountID, TransactionType)
    select TV1.ID, V3.PDACCOUNTSTRUCTUREID,
            case V3.TransactionType when 1 then case when COALESCE (V1.REVENUETYPEID,V1.PAYMENTMETHODID,V1.APPLICATIONTYPEID) is not null then V1.DEBITPDACCOUNTSEGMENTVALUEID else V2.DEBITPDACCOUNTSEGMENTVALUEID end when 2 then case when COALESCE (V1.REVENUETYPEID,V1.PAYMENTMETHODID,V1.APPLICATIONTYPEID) is not null then V1.CREDITPDACCOUNTSEGMENTVALUEID else V2.CREDITPDACCOUNTSEGMENTVALUEID end end,
            case V3.TransactionType when 1 then case when COALESCE (V1.REVENUETYPEID,V1.PAYMENTMETHODID,V1.APPLICATIONTYPEID) is not null then V1.DEBITGLACCOUNTID else V2.DEBITGLACCOUNTID end when 2 then case when COALESCE (V1.REVENUETYPEID,V1.PAYMENTMETHODID,V1.APPLICATIONTYPEID) is not null then V1.CREDITGLACCOUNTID else V2.CREDITGLACCOUNTID end end,
        V3.TransactionType
    from @InputTable TV1 
    left join @AvailableAcctCodeMappings V1
        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)
        and V1.PDACCOUNTCODEMAPOFFICEID = TV1.PDACCOUNTCODEMAPOFFICEID      
        and V1.PDACCOUNTSYSTEMID = TV1.PDACCOUNTSYSTEMID  
    left join @AvailableAcctCodeMappings V2
        on (V2.PaymentMethodID = TV1.PaymentMethodID or TV1.PaymentMethodID is null)
        and (V2.APPLICATIONTYPEID = TV1.APPLICATIONTYPEID or TV1.APPLICATIONTYPEID is null)
        and (V2.REVENUETYPEID = TV1.REVENUETYPEID or TV1.REVENUETYPEID is null or TV1.REVENUETYPEID  = '00000000-0000-0000-0000-000000000000')
        and V2.SubTypeID = '99999999-9999-9999-9999-999999999999'
        and V2.PDACCOUNTCODEMAPOFFICEID = TV1.PDACCOUNTCODEMAPOFFICEID
        and V2.PDACCOUNTSYSTEMID = TV1.PDACCOUNTSYSTEMID  
    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   
  inner join @InputTable as t3 on t2.PDACCOUNTSYSTEMID = t3.PDACCOUNTSYSTEMID

  if exists (select top 1 * from @SegmentValues)
    begin
        declare @hashTable table(ID uniqueidentifier, TRANSACTIONTYPE tinyint, HASHVALUE uniqueidentifier);

        insert into @hashTable
        select 
            id, 
            TransactionType, 
            dbo.UFN_GLACCOUNT_MD5_HASH_SEGMENTS(
               (
                    select           
                      pvt.[1] SEGMENT01ID, pvt.[2] SEGMENT02ID, pvt.[3] SEGMENT03ID, pvt.[4] SEGMENT04ID, pvt.[5] SEGMENT05ID, pvt.[6] SEGMENT06ID, pvt.[7] SEGMENT07ID, pvt.[8] SEGMENT08ID, pvt.[9] SEGMENT09ID, pvt.[10] SEGMENT10ID, pvt.[11] SEGMENT11ID, pvt.[12] SEGMENT12ID, pvt.[13] SEGMENT13ID, pvt.[14] SEGMENT14ID, pvt.[15] SEGMENT15ID, pvt.[16] SEGMENT16ID, pvt.[17] SEGMENT17ID, pvt.[18] SEGMENT18ID, pvt.[19] SEGMENT19ID, pvt.[20] SEGMENT20ID, pvt.[21] SEGMENT21ID, pvt.[22] SEGMENT22ID, pvt.[23] SEGMENT23ID, pvt.[24] SEGMENT24ID, pvt.[25] SEGMENT25ID, pvt.[26] SEGMENT26ID, pvt.[27] SEGMENT27ID, pvt.[28] SEGMENT28ID, pvt.[29] SEGMENT29ID, pvt.[30] SEGMENT30ID
                    for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64
               )) ACCOUNTHASH
        from(
             select id, swt.TransactionType, cast(swt.PDAccountSegmentValueID as nvarchar(36)) PDAccountSegmentValueID, ROW_NUMBER() over(partition by ID, TransactionType order by swt.PDAccountSegmentValueID) segment
             from @SegmentValues swt
        ) source
        pivot
        (
            MAX(PDAccountSegmentValueID)
            for segment in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30])
        ) pvt


        --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 @SegmentValues SWT 
        inner join dbo.PDACCOUNTSEGMENT t1 on SWT.PDACCOUNTSTRUCTUREID = t1.PDACCOUNTSTRUCTUREID and SWT.PDACCOUNTSEGMENTVALUEID = t1.PDACCOUNTSEGMENTVALUEID
        inner join dbo.GLACCOUNT g on t1.GLACCOUNTID = g.id
        inner join @hashTable ht on g.MD5HASHVALUE = ht.HASHVALUE and swt.ID = ht.ID and swt.TRANSACTIONTYPE = ht.TRANSACTIONTYPE  
        group by SWT.ID, SWT.TransactionType, t1.GLACCOUNTID
    having count(*) = @NumSegments -- Handle collisions from the hash

        ) 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

  --if no errors, don't waste time doing error messages

  if exists (select 1 from @OutputTable where ACCOUNTNUMBERERROR != 0)
    begin
      --Get all of the segments for the transactions that have all of their segments properly mapped but the account does not exist

      declare @NotExistingAccounts table (ID uniqueidentifier, TransactionType tinyint, SHORTDESCRIPTION nvarchar(30), SEPARATOR nvarchar(20), SEPARATORCODE tinyint, SEQUENCE int)
      insert into @NotExistingAccounts
      select OT.ID, FT.TransactionType, VALUE.SHORTDESCRIPTION, STRUCT.SEPARATOR, STRUCT.SEPARATORCODE, STRUCT.SEQUENCE
      from @OutputTable OT
      inner join (select SWT.ID, SWT.TransactionType    
          from @SegmentValues SWT
          group by SWT.ID, SWT.TransactionType
          having count(PDAccountSegmentValueID) = @NumSegments) FT on OT.ID = FT.ID
      inner join @SegmentValues SWT on SWT.ID = FT.ID and FT.TransactionType = SWT.TransactionType
      inner join dbo.PDACCOUNTSEGMENTVALUE VALUE on VALUE.ID = SWT.PDAccountSegmentValueID
      inner join dbo.PDACCOUNTSTRUCTURE STRUCT on STRUCT.ID = SWT.PDAccountStructureID
      where (OT.DEBITGLACCOUNTSID is null and FT.TransactionType = 1) or (OT.CREDITGLACCOUNTSID is null and FT.TransactionType = 2) and OT.ACCOUNTNUMBERERROR = 2
      group by OT.ID, FT.TransactionType, VALUE.SHORTDESCRIPTION, STRUCT.SEPARATOR, STRUCT.SEPARATORCODE, STRUCT.SEQUENCE

      --Update the output table with the account numbers that do not exist

      update @OutputTable set
          DEBITGLACCOUNTNUMBER = case when T.TransactionType = 1 and OT.DEBITGLACCOUNTSID is null then substring(T.ACCOUNT, 0, LEN(T.ACCOUNT) + @AddToString) else OT.DEBITGLACCOUNTNUMBER end
          ,CREDITGLACCOUNTNUMBER = case when T.TransactionType = 2 and OT.CREDITGLACCOUNTSID is null then substring(T.ACCOUNT, 0, LEN(T.ACCOUNT) + @AddToString) else OT.CREDITGLACCOUNTNUMBER end
          ,ACCOUNTNUMBERERROR = 3
      from @OutputTable OT
      inner join (select t1.ID, t1.TransactionType,
          (select t2.SHORTDESCRIPTION + case t2.SEPARATORCODE when 6 then '' else SUBSTRING(t2.SEPARATOR, 0, 2) end 
              from @NotExistingAccounts t2 
              where t1.ID = t2.ID and t1.TransactionType = t2.TransactionType
              order by t2.SEQUENCE 
              for xml path('')) [ACCOUNT]
          from @NotExistingAccounts t1 
          group by t1.ID, t1.TransactionType) T on OT.ID = T.ID
      where OT.ACCOUNTNUMBERERROR = 2
        end
    return
    end