UFN_AUDIT_GETDETAILSFORREVENUERECOGNITION

CREATE function [dbo].[UFN_AUDIT_GETDETAILSFORREVENUERECOGNITION](@REVENUEID uniqueidentifier)
returns @RESULTS table
    (RECORDID uniqueidentifier
    ,AUDITDATE datetime
    ,CHANGEDBYUSER varchar(128)
    ,PROCESS varchar(255)
    ,FIELD varchar(128)
    ,OLD varchar(4000)
    ,NEW varchar(4000)
    ,SEQUENCE int
    ,[ACTION] varchar(8)
    ,CHANGEDBYAPP varchar(200))

as
begin
    declare @IDs table (ID uniqueidentifier primary key)
    insert into @IDs (ID)
    select distinct T.ID from (
        select LI.ID 
        from dbo.FINANCIALTRANSACTIONLINEITEM LI
        inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = LI.ID
        where LI.FINANCIALTRANSACTIONID = @REVENUEID
            and LI.REVERSEDLINEITEMID is null

        union all

        select LI.AUDITRECORDID 
        from dbo.FINANCIALTRANSACTIONLINEITEMAUDIT LI
        inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = LI.AUDITRECORDID
        where LI.FINANCIALTRANSACTIONID = @REVENUEID
            and LI.REVERSEDLINEITEMID is null

        union all

        select LI.AUDITRECORDID 
        from dbo.FINANCIALTRANSACTIONLINEITEMAUDIT LI
        inner join dbo.REVENUESPLIT_EXTAUDIT RSEA on RSEA.AUDITRECORDID = LI.AUDITRECORDID
        where LI.FINANCIALTRANSACTIONID = @REVENUEID
            and LI.REVERSEDLINEITEMID is null) T

    insert into @RESULTS 
        (RECORDID
        ,AUDITDATE
        ,CHANGEDBYUSER
        ,PROCESS
        ,FIELD
        ,OLD
        ,NEW
        ,SEQUENCE
        ,[ACTION]
        ,CHANGEDBYAPP)
    select 
        V4.RECORDID
        ,V4.AUDITDATE
        ,CHANGEAGENT.USERNAME as CHANGEDBYUSER
        ,CHANGEAGENT.PROCESSDISPLAYNAME as PROCESS
        ,V4.FIELD
        ,V4.OLD
        ,V4.NEW
        ,130 as SEQUENCE
        ,V4.[ACTION]
        ,CHANGEAGENT.APPLICATIONNAME as CHANGEDBYAPP
    from
        (select 
            V3.RECORDID
            ,V3.AUDITDATE
            ,case V3.AUDITTYPECODE2 when 2 then 'Delete' when 0 then 'Update' else 'Insert' end as [ACTION]
            ,'Recognition:' + case NUMS.NUM when 1 then 'CONSTITUENTID' when 2 then 'AMOUNT' when 3 then 'EFFECTIVEDATE' when 4 then 'REVENUERECOGNITIONTYPECODEID' end as FIELD
            ,case NUMS.NUM
                when 1 then case V3.AUDITTYPECODE1 when -1 then 'n/a' else case when V3.CONSTITID1 = V3.CONSTITID2 then null else isnull(V3.CONSTITUENTNAME2,'n/a') end end
                when 2 then case V3.AUDITTYPECODE1 when -1 then 'n/a' else case when V3.AMOUNT1 = V3.AMOUNT2 then null else isnull(convert(varchar,V3.AMOUNT2,1),'n/a') end end
                when 3 then case V3.AUDITTYPECODE1 when -1 then 'n/a' else case when V3.EFFECTIVEDATE1 = V3.EFFECTIVEDATE2 then null else isnull(convert(varchar,V3.EFFECTIVEDATE2,101),'n/a') end end
                when 4 then case V3.AUDITTYPECODE1 when -1 then 'n/a' else case when V3.REVENUERECOGNITIONTYPECODE1 = V3.REVENUERECOGNITIONTYPECODE2 then null else isnull(V3.REVENUERECOGNITIONTYPE2,'n/a') end end
            end as OLD
            ,case NUMS.NUM
                when 1 then case V3.AUDITTYPECODE2 when 2 then 'n/a' else case when V3.CONSTITID1 = V3.CONSTITID2 then null else isnull(V3.CONSTITUENTNAME1,'n/a') end end
                when 2 then case V3.AUDITTYPECODE2 when 2 then 'n/a' else case when V3.AMOUNT1 = V3.AMOUNT2 then null else isnull(convert(varchar,V3.AMOUNT1,1),'n/a') end end
                when 3 then case V3.AUDITTYPECODE2 when 2 then 'n/a' else case when V3.EFFECTIVEDATE1 = V3.EFFECTIVEDATE2 then null else isnull(convert(varchar,V3.EFFECTIVEDATE1,101),'n/a') end end
                when 4 then case V3.AUDITTYPECODE2 when 2 then 'n/a' else case when V3.REVENUERECOGNITIONTYPECODE1 = V3.REVENUERECOGNITIONTYPECODE2 then null else isnull(V3.REVENUERECOGNITIONTYPE1,'n/a') end end
            end as NEW
            ,ChangeAgentID
        from
            (select 
                isnull(V1.AUDITRECORDID, V2.AUDITRECORDID) as RECORDID
                ,isnull(V1.AUDITCHANGEAGENTID,V2.AUDITCHANGEAGENTID) as CHANGEAGENTID
                ,isnull(V1.AUDITDATE, V2.AUDITDATE) as AUDITDATE
                ,V1.AUDITTYPECODE as AUDITTYPECODE1
                ,V2.AUDITTYPECODE as AUDITTYPECODE2
                ,V1.CONSTITUENTID as CONSTITID1
                ,V2.CONSTITUENTID as CONSTITID2
                ,V1.AMOUNT as AMOUNT1
                ,V2.AMOUNT as AMOUNT2
                ,V1.EFFECTIVEDATE as EFFECTIVEDATE1
                ,V2.EFFECTIVEDATE as EFFECTIVEDATE2
                ,V1.REVENUERECOGNITIONTYPECODEID as REVENUERECOGNITIONTYPECODE1
                ,V2.REVENUERECOGNITIONTYPECODEID as REVENUERECOGNITIONTYPECODE2
                ,CONSTITUENT.NAME as CONSTITUENTNAME1
                ,CONSTITUENT2.NAME as CONSTITUENTNAME2
                ,REVENUERECOGNITIONTYPECODE.DESCRIPTION as REVENUERECOGNITIONTYPE1
                ,REVENUERECOGNITIONTYPECODE2.DESCRIPTION as REVENUERECOGNITIONTYPE2
            from
                (select 
                    RRA.AUDITRECORDID
                    ,RRA.AuditKey
                    ,RRA.AuditChangeAgentID
                    ,RRA.DATECHANGED as AUDITDATE
                    ,RRA.AUDITTYPECODE
                    ,RRA.CONSTITUENTID
                    ,RRA.AMOUNT
                    ,RRA.EFFECTIVEDATE
                    ,RRA.REVENUERECOGNITIONTYPECODEID
                from REVENUERECOGNITIONAUDIT RRA
                inner join @IDs as IDs on RRA.REVENUESPLITID = IDs.ID
                where RRA.AUDITTYPECODE = 1
                union all
                select 
                    RR.ID
                    ,'00000000-0000-0000-0000-000000000000' as AUDITKEY
                    ,RR.ADDEDBYID
                    ,RR.DATEADDED
                    ,-1 as AUDITTYPECODE
                    ,RR.CONSTITUENTID
                    ,RR.AMOUNT
                    ,RR.EFFECTIVEDATE
                    ,RR.REVENUERECOGNITIONTYPECODEID
                from dbo.REVENUERECOGNITION RR
                inner join @IDs as IDs on RR.REVENUESPLITID = IDs.ID
                where RR.ID not in (select RRA.AUDITRECORDID
                    from dbo.REVENUERECOGNITIONAUDIT RRA
                    inner join @IDs as IDs on RRA.REVENUESPLITID = IDs.ID
                    where RRA.AUDITTYPECODE = 0)
                union all
                select
                    AUDITRECORDID
                    ,AUDITKEY
                    ,AUDITCHANGEAGENTID
                    ,AUDITDATE
                    ,AUDITTYPECODE
                    ,CONSTITUENTID
                    ,AMOUNT
                    ,EFFECTIVEDATE
                    ,REVENUERECOGNITIONTYPECODEID
                from (
                    select
                        RRA.AUDITRECORDID
                        ,'00000000-0000-0000-0000-000000000000' as AUDITKEY
                        ,RRA.AUDITCHANGEAGENTID
                        ,RRA.DATEADDED [AUDITDATE]
                        ,RRA.AUDITTYPECODE
                        ,RRA.CONSTITUENTID
                        ,RRA.AMOUNT
                        ,RRA.EFFECTIVEDATE
                        ,RRA.REVENUERECOGNITIONTYPECODEID
                        ,ROW_NUMBER() over (partition by RRA.AUDITRECORDID order by RRA.AUDITDATE asc) SEQUENCE
                    from REVENUERECOGNITIONAUDIT RRA
                    inner join @IDs as IDs on RRA.REVENUESPLITID = IDs.ID
                    where RRA.AUDITTYPECODE = 0) BEFOREUPDATES
                where BEFOREUPDATES.SEQUENCE = 1
                union all
                select
                    RRA.AUDITRECORDID
                    ,'00000000-0000-0000-0000-000000000000' as AUDITKEY
                    ,RRA.ADDEDBYID
                    ,RRA.DATEADDED [AUDITDATE]
                    ,RRA.AUDITTYPECODE
                    ,RRA.CONSTITUENTID
                    ,RRA.AMOUNT
                    ,RRA.EFFECTIVEDATE
                    ,RRA.REVENUERECOGNITIONTYPECODEID
                from REVENUERECOGNITIONAUDIT RRA
                inner join @IDs as IDs on RRA.REVENUESPLITID = IDs.ID
                where RRA.AUDITTYPECODE = 2 and RRA.AUDITRECORDID not in (
                    select ID from dbo.REVENUERECOGNITION
                    union all
                    select AUDITRECORDID from REVENUERECOGNITIONAUDIT where REVENUERECOGNITIONAUDIT.AUDITTYPECODE != 2)
            ) V1
            full outer join
                (select 
                    RRA.AUDITRECORDID
                    ,RRA.AUDITKEY
                    ,RRA.AUDITCHANGEAGENTID
                    ,RRA.AUDITDATE
                    ,RRA.AUDITTYPECODE
                    ,RRA.CONSTITUENTID
                    ,RRA.AMOUNT
                    ,RRA.EFFECTIVEDATE
                    ,RRA.REVENUERECOGNITIONTYPECODEID
                from REVENUERECOGNITIONAUDIT RRA
                inner join @IDs as IDs on RRA.REVENUESPLITID = IDs.ID
                where RRA.AUDITTYPECODE in (0,2)) V2 on V1.AUDITRECORDID = V2.AUDITRECORDID and V1.AUDITKEY = V2.AUDITKEY
            left join dbo.CONSTITUENT on V1.CONSTITUENTID = CONSTITUENT.ID
            left join dbo.CONSTITUENT as CONSTITUENT2 on V2.CONSTITUENTID = CONSTITUENT2.ID
            left join dbo.REVENUERECOGNITIONTYPECODE on V1.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
            left join dbo.REVENUERECOGNITIONTYPECODE as REVENUERECOGNITIONTYPECODE2 on V2.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE2.ID
            where 
                (V1.CONSTITUENTID != V2.CONSTITUENTID 
                    or (V1.CONSTITUENTID is null and V2.CONSTITUENTID is not null) 
                    or (V1.CONSTITUENTID is not null and V2.CONSTITUENTID is null)
                )
                or (V1.AMOUNT != V2.AMOUNT 
                    or (V1.AMOUNT is null and V2.AMOUNT is not null) 
                    or (V1.AMOUNT is not null and V2.AMOUNT is null)
                )
                or (V1.EFFECTIVEDATE != V2.EFFECTIVEDATE 
                    or (V1.EFFECTIVEDATE is null and V2.EFFECTIVEDATE is not null) 
                    or (V1.EFFECTIVEDATE is not null and V2.EFFECTIVEDATE is null)
                )
                or (V1.REVENUERECOGNITIONTYPECODEID != V2.REVENUERECOGNITIONTYPECODEID 
                    or (V1.REVENUERECOGNITIONTYPECODEID is null and V2.REVENUERECOGNITIONTYPECODEID is not null) 
                    or (V1.REVENUERECOGNITIONTYPECODEID is not null and V2.REVENUERECOGNITIONTYPECODEID is null)
                )
            ) V3
        cross join (
            select 1 as NUM union select 2 union select 3 union select 4) as NUMS) V4
        inner join dbo.CHANGEAGENT on V4.CHANGEAGENTID = CHANGEAGENT.ID         
        where OLD is not null

    return;
end