UFN_GLACCOUNT_GETSEGMENTWORKTABLE

CREATE function dbo.UFN_GLACCOUNT_GETSEGMENTWORKTABLE(@InputTable UDT_MAPPEDVALUESFORGETACCOUNTS_2 readonly)
returns @SegmentsWorkTable table (ID uniqueidentifier, PDAccountStructureID uniqueidentifier, PDAccountSegmentValueID uniqueidentifier NULL, TransactionType tinyint, SegType tinyint, PRIMARY KEY (ID, PDAccountStructureID, TransactionType))
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 @FinalTable table (ID uniqueidentifier, GLACCOUNTID uniqueidentifier, ACCOUNTNUMBER varchar(100), TransactionType tinyint, PRIMARY KEY (ID, TransactionType, GLACCOUNTID))
    declare @InputLines table (ID uniqueidentifier, TableID uniqueidentifier, MappedID uniqueidentifier, PRIMARY KEY (ID, TableID))
    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 

    --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 (SEGMENT02TABLEID,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 (SEGMENT02ID, 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

        declare @SegmentMappedIds table(id uniqueidentifier, structid uniqueidentifier, mappedid uniqueidentifier, transactiontype tinyint, primary key(id, structid, transactiontype))

        insert into @SegmentMappedIds
        select IT.ID ,t1.ID, IL.MappedID, v3.TransactionType
        from @InputTable as IT 
        inner join dbo.PDACCOUNTSTRUCTURE t1 on t1.SEGMENTTYPE = 3 and t1.PDACCOUNTSYSTEMID = IT.PDACCOUNTSYSTEMID 
        inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT t2 on t1.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = t2.ID 
        left join @InputLines IL on IL.TableID = t2.TABLEID
        cross join (select 1 as TransactionType union all select 2 as TransactionType) V3                
        where not exists (select * from @FinalTable where ID = IT.ID and TransactionType = V3.TransactionType);                        

        insert into @SegmentsWorkTable(ID, PDAccountStructureID, PDAccountSegmentValueID, TransactionType, SegType)
        select s.id, sm.PDACCOUNTSTRUCTUREID, sm.PDACCOUNTSEGMENTVALUEID, s.transactiontype, 2 
        from dbo.PDACCOUNTSEGMENTMAPPING sm
        inner join @SegmentMappedIds s on sm.PDACCOUNTSTRUCTUREID = s.structid and (sm.LONGDESCRIPTIONID = s.mappedid or (nullif(s.mappedid,'99999999-9999-9999-9999-999999999999') is null and sm.ISDEFAULT = 1))    

        --Insert the composite segments

        if exists(select 1 from pdaccountstructure t1 inner join @InputTable as t2 on t1.PDACCOUNTSYSTEMID = t2.PDACCOUNTSYSTEMID where t1.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 and  t1.PDACCOUNTSYSTEMID = IT.PDACCOUNTSYSTEMID 
            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)
    end

    return
end