UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE

CREATE function dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE
(
    @PDACCOUNTSYSTEMID uniqueidentifier
    ,@OFFICEID int
    ,@REVENUETYPEID uniqueidentifier = null
    ,@APPLICATIONTYPEID uniqueidentifier = null
    ,@PAYMENTMETHODID uniqueidentifier = null
    ,@SUBTYPEID uniqueidentifier = null
)
returns table
as 
return
(
    select '99999999-9999-9999-9999-999999999999' as [ID]
        ,(select ID from dbo.PDACCOUNTCODEMAPOFFICE where OFFICEID = @OFFICEID) as [PDACCOUNTCODEMAPOFFICEID]
        ,isnull(@REVENUETYPEID, '00000000-0000-0000-0000-000000000000') as [REVENUETYPEID]
        ,isnull(@APPLICATIONTYPEID, '00000000-0000-0000-0000-000000000000') as [APPLICATIONTYPEID]
        ,isnull(@PAYMENTMETHODID, '00000000-0000-0000-0000-000000000000') as [PAYMENTMETHODID]
        ,isnull(@SUBTYPEID, '00000000-0000-0000-0000-000000000000') as [SUBTYPEID]
        ,@PDACCOUNTSYSTEMID as [PDACCOUNTSYSTEMID]
        ,[2] as [SEGMENT02TABLEID], case when [2] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT02ID]
        ,[3] as [SEGMENT03TABLEID], case when [3] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT03ID]
        ,[4] as [SEGMENT04TABLEID], case when [4] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT04ID]
        ,[5] as [SEGMENT05TABLEID], case when [5] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT05ID]
        ,[6] as [SEGMENT06TABLEID], case when [6] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT06ID]
        ,[7] as [SEGMENT07TABLEID], case when [7] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT07ID]
        ,[8] as [SEGMENT08TABLEID], case when [8] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT08ID]
        ,[9] as [SEGMENT09TABLEID], case when [9] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT09ID]
        ,[10] as [SEGMENT10TABLEID], case when [10] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT10ID]
        ,[11] as [SEGMENT11TABLEID], case when [11] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT11ID]
        ,[12] as [SEGMENT12TABLEID], case when [12] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT12ID]
        ,[13] as [SEGMENT13TABLEID], case when [13] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT13ID]
        ,[14] as [SEGMENT14TABLEID], case when [14] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT14ID]
        ,[15] as [SEGMENT15TABLEID], case when [15] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT15ID]
        ,[16] as [SEGMENT16TABLEID], case when [16] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT16ID]
        ,[17] as [SEGMENT17TABLEID], case when [17] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT17ID]
        ,[18] as [SEGMENT18TABLEID], case when [18] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT18ID]
        ,[19] as [SEGMENT19TABLEID], case when [19] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT19ID]
        ,[20] as [SEGMENT20TABLEID], case when [20] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT20ID]
        ,[21] as [SEGMENT21TABLEID], case when [21] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT21ID]
        ,[22] as [SEGMENT22TABLEID], case when [22] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT22ID]
        ,[23] as [SEGMENT23TABLEID], case when [23] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT23ID]
        ,[24] as [SEGMENT24TABLEID], case when [24] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT24ID]
        ,[25] as [SEGMENT25TABLEID], case when [25] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT25ID]
        ,[26] as [SEGMENT26TABLEID], case when [26] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT26ID]
        ,[27] as [SEGMENT27TABLEID], case when [27] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT27ID]
        ,[28] as [SEGMENT28TABLEID], case when [28] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT28ID]
        ,[29] as [SEGMENT29TABLEID], case when [29] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT29ID]
        ,[30] as [SEGMENT30TABLEID], case when [30] is null then null else '99999999-9999-9999-9999-999999999999' end as [SEGMENT30ID]
    from
    (select S.ID, convert(nvarchar(36),SEG.TABLEID) as TableID, S.SEQUENCE, S.ELEMENTDEFINITIONCODE
        from dbo.PDACCOUNTSTRUCTURE S
        left join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT SEG on SEG.ID = S.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
        where s.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID) as SourceTable
    pivot
    (
        MAX(TableID) for Sequence in ([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]) 
    ) as PivotTable
    where ELEMENTDEFINITIONCODE != 1
)