UFN_REVENUEHISTORY_REVENUECHILDTABLES_AUDIT2

    CREATE function [dbo].[UFN_REVENUEHISTORY_REVENUECHILDTABLES_AUDIT2]
      (
        @REVENUEID uniqueidentifier,
        @REVENUEDATEADDED datetime,
        @ACTIONTYPECODE tinyint,
        @RECORDTYPECODE tinyint,
        @STARTDATE datetime,
        @ENDDATE datetime
      )
      returns @RESULTS table
      (
        RECORDID uniqueidentifier,
        AUDITDATE datetime,
        CHANGEDBYUSER nvarchar(128),
        PROCESS nvarchar(255),
        FIELD nvarchar(128),
        OLD nvarchar(4000),
        NEW nvarchar(4000),
        SEQUENCE int,
        ACTION nvarchar(8),
        CHANGEDBYAPP nvarchar(200)
      )
      as
      begin
        declare @DETAILID uniqueidentifier;
        declare @REVENUETYPECODE tinyint;

        --Letter

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 3
          insert into @RESULTS
          select          
            RECORDID,
            case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
            CHANGEDBYUSER,
            PROCESS,
            FIELD,
            OLD,
            NEW,
            200 as [SEQUENCE],
            ACTION,
            CHANGEDBYAPP         
          from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('LETTERCODEID, PROCESSDATE, ACKNOWLEDGEDATE, ACKNOWLEDGEEID , MKTPACKAGEID','REVENUELETTER','REVENUEID',@REVENUEID,null,'Letter','',@STARTDATE,@ENDDATE)    

        --Benefits

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 4
        insert into @RESULTS (
          RECORDID
          ,AUDITDATE
          ,CHANGEDBYUSER
          ,PROCESS
          ,FIELD
          ,OLD
          ,NEW
          ,SEQUENCE
          ,ACTION
          ,CHANGEDBYAPP)
        select 
          RECORDID
          ,AUDITDATE
          ,CHANGEDBYUSER
          ,PROCESS
          ,FIELD
          ,OLD
          ,NEW
          ,SEQUENCE
          ,ACTION
          ,CHANGEDBYAPP    
        from dbo.UFN_AUDIT_GETDETAILSFORREVENUEBENEFIT(@REVENUEID)
        where AUDITDATE >= isnull(@STARTDATE, AUDITDATE) and AUDITDATE <= isnull(@ENDDATE, AUDITDATE)

        --GL

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 5
          insert into @RESULTS (
            RECORDID
            ,AUDITDATE
            ,CHANGEDBYUSER
            ,PROCESS
            ,FIELD
            ,OLD
            ,NEW
            ,SEQUENCE
            ,ACTION
            ,CHANGEDBYAPP)
          select 
            RECORDID
            ,AUDITDATE
            ,CHANGEDBYUSER
            ,PROCESS
            ,FIELD
            ,OLD
            ,NEW
            ,SEQUENCE
            ,ACTION
            ,CHANGEDBYAPP
          from dbo.UFN_AUDIT_GETDETAILSFORREVENUEGLDISTRIBUTION(@REVENUEID)
          where AUDITDATE >= isnull(@STARTDATE, AUDITDATE) and AUDITDATE <= isnull(@ENDDATE, AUDITDATE)

        --Schedule

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 6
          insert into @RESULTS
          select          
            RECORDID,
            case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
            CHANGEDBYUSER,
            PROCESS,
            FIELD,

            -- 10/23/2014 431380 JasonPe "Completed" to "Canceled" mapping. This is required for new Recurring Gift status codes. "Completed" is deprecated.

            case FIELD
            when 'Schedule:STATUSCODE' then
              case OLD
              when 'Completed' then 'Canceled' 
              else OLD end 
            else OLD
            end,                                
            case FIELD
            when 'Schedule:STATUSCODE' then
              case NEW
              when 'Completed' then 'Canceled'
              else NEW end 
            else NEW
            end,
            60 as [SEQUENCE],
            ACTION,
            CHANGEDBYAPP         
          from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('STARTDATE,ENDDATE,NUMBEROFINSTALLMENTS,FREQUENCYCODE,NEXTTRANSACTIONDATE,STATUSCODE,SENDPLEDGEREMINDER,ISPENDING,CREDITCARDID','REVENUESCHEDULE','ID',@REVENUEID,@REVENUEDATEADDED,'Schedule','',@STARTDATE,@ENDDATE)

        --Installments

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 7
          insert into @RESULTS
          select          
          RECORDID,
          case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
          CHANGEDBYUSER,
          PROCESS,
          FIELD,
          OLD,
          NEW,
          70 as [SEQUENCE],
          ACTION,
          CHANGEDBYAPP         
          from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('DATE,AMOUNT','INSTALLMENT','REVENUEID',@REVENUEID,@REVENUEDATEADDED,'Installment','',@STARTDATE,@ENDDATE)

        --RGA

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 8
          insert into @RESULTS
          select
          RECORDID,
          case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
          CHANGEDBYUSER,
          PROCESS,
          FIELD,
          OLD,
          NEW,
          80 as [SEQUENCE],
          ACTION,
          CHANGEDBYAPP         
          from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('TYPECODE, AMOUNT, SCHEDULEDATE','RECURRINGGIFTACTIVITY','SOURCEREVENUEID',@REVENUEID,null,'Recurring gift activity','',@STARTDATE,@ENDDATE)

        --MG Claim

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 9
          insert into @RESULTS
          select          
          RECORDID,
          case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
          CHANGEDBYUSER,
          PROCESS,
          FIELD,
          OLD,
          NEW,
          90 as [SEQUENCE],
          ACTION,
          CHANGEDBYAPP         
          from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('MGSOURCEREVENUEID, ISACTIVE','REVENUEMATCHINGGIFT','ID',@REVENUEID,null,'Matching gift claim for','',@STARTDATE,@ENDDATE)    

        --Donor Challenge

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 10
          insert into @RESULTS
          select          
          RECORDID,
          case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
          CHANGEDBYUSER,
          PROCESS,
          FIELD,
          OLD,
          NEW,
          170 as [SEQUENCE],
          ACTION,
          CHANGEDBYAPP         
          from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('REVENUESPLITID','DONORCHALLENGEENCUMBERED','MATCHEDREVENUEID',@REVENUEID,null,'Donor Challenge claim for','',@STARTDATE,@ENDDATE)    

        declare @REVENUESPLITSTIDS table (ID uniqueidentifier)
        if(@RECORDTYPECODE in (0,11,12,13,14,15,16,18))
        begin
          insert into @REVENUESPLITSTIDS
            select ID from dbo.REVENUESPLIT where REVENUEID = @REVENUEID
            union 
            select AUDITRECORDID as ID
            from dbo.FINANCIALTRANSACTIONLINEITEMAUDIT
            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEMAUDIT.AUDITRECORDID
            where FINANCIALTRANSACTIONID = @REVENUEID
        end

        --Application details

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 11
          insert into @RESULTS
          select 
            RECORDID
            ,AUDITDATE
            ,CHANGEDBYUSER
            ,PROCESS
            ,FIELD
            ,OLD
            ,NEW
            ,SEQUENCE
            ,ACTION
            ,CHANGEDBYAPP
          from dbo.UFN_AUDIT_GETDETAILSFORREVENUESPLIT(@REVENUEID)
        where AUDITDATE >= isnull(@STARTDATE, AUDITDATE) and AUDITDATE <= isnull(@ENDDATE, AUDITDATE)
          union all
          select 
            RECORDID
            ,AUDITDATE
            ,CHANGEDBYUSER
            ,PROCESS
            ,FIELD
            ,OLD
            ,NEW
            ,SEQUENCE
            ,ACTION
            ,CHANGEDBYAPP
          from dbo.UFN_AUDIT_GETDETAILSFORREVENUESPLITOTHER(@REVENUEID)
          where AUDITDATE >= isnull(@STARTDATE, AUDITDATE) and AUDITDATE <= isnull(@ENDDATE, AUDITDATE)
          union all
          select 
            RECORDID
            ,AUDITDATE
            ,CHANGEDBYUSER
            ,PROCESS
            ,FIELD
            ,OLD
            ,NEW
            ,SEQUENCE
            ,ACTION
            ,CHANGEDBYAPP
          from dbo.UFN_AUDIT_GETDETAILSFORREVENUECATEGORY(@REVENUEID)
          where AUDITDATE >= isnull(@STARTDATE, AUDITDATE) and AUDITDATE <= isnull(@ENDDATE, AUDITDATE)

        --Solicitors

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 12
          insert into @RESULTS
          select 
            DETAILS.RECORDID,
            case when DETAILS.ACTION = 'Insert' then DETAILS.DATEADDED when DETAILS.ACTION = 'Update' then DETAILS.DATECHANGED else DETAILS.AUDITDATE end as [AUDITDATE],
            DETAILS.CHANGEDBYUSER,
            DETAILS.PROCESS,
            DETAILS.FIELD,
            DETAILS.OLD,
            DETAILS.NEW,
            120 as [SEQUENCE],
            DETAILS.ACTION,
            DETAILS.CHANGEDBYAPP
          from @REVENUESPLITSTIDS as REVENUESPLIT
          cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('CONSTITUENTID, AMOUNT', 'REVENUESOLICITOR', 'REVENUESPLITID', REVENUESPLIT.ID, @REVENUEDATEADDED, 'Solicitors', '',@STARTDATE,@ENDDATE) DETAILS

        --Recognition

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 13
          insert into @RESULTS (
            RECORDID
            ,AUDITDATE
            ,CHANGEDBYUSER
            ,PROCESS
            ,FIELD
            ,OLD
            ,NEW
            ,SEQUENCE
            ,ACTION
            ,CHANGEDBYAPP)
          select 
            RECORDID
            ,AUDITDATE
            ,CHANGEDBYUSER
            ,PROCESS
            ,FIELD
            ,OLD
            ,NEW
            ,SEQUENCE
            ,ACTION
            ,CHANGEDBYAPP
          from dbo.UFN_AUDIT_GETDETAILSFORREVENUERECOGNITION(@REVENUEID)
          where AUDITDATE >= isnull(@STARTDATE, AUDITDATE) and AUDITDATE <= isnull(@ENDDATE, AUDITDATE)

        --Named Recognition

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 14
          insert into @RESULTS
          select 
            DETAILS.RECORDID,
            case when DETAILS.ACTION = 'Insert' then DETAILS.DATEADDED when DETAILS.ACTION = 'Update' then DETAILS.DATECHANGED else DETAILS.AUDITDATE end as [AUDITDATE],
            DETAILS.CHANGEDBYUSER,
            DETAILS.PROCESS,
            DETAILS.FIELD,
            DETAILS.OLD,
            DETAILS.NEW,
            160 as [SEQUENCE],
            DETAILS.ACTION,
            DETAILS.CHANGEDBYAPP
          from @REVENUESPLITSTIDS as REVENUESPLIT
          cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('NAMINGOPPORTUNITYRECOGNITIONID', 'NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT', 'REVENUESPLITID', REVENUESPLIT.ID, '01/01/1900', 'Named recognition', '',@STARTDATE,@ENDDATE) DETAILS

        --Campaigns

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 15
          insert into @RESULTS
          select 
            RECORDID
            ,AUDITDATE
            ,CHANGEDBYUSER
            ,PROCESS
            ,FIELD
            ,OLD
            ,NEW
            ,SEQUENCE
            ,ACTION
            ,CHANGEDBYAPP
          from dbo.UFN_AUDIT_GETDETAILSFORREVENUESPLITCAMPAIGN(@REVENUEID)
          where AUDITDATE >= isnull(@STARTDATE, AUDITDATE) and AUDITDATE <= isnull(@ENDDATE, AUDITDATE)

        --Business Units

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 16
          insert into @RESULTS
          select 
            RECORDID
            ,AUDITDATE
            ,CHANGEDBYUSER
            ,PROCESS
            ,FIELD
            ,OLD
            ,NEW
            ,SEQUENCE
            ,ACTION
            ,CHANGEDBYAPP
          from dbo.UFN_AUDIT_GETDETAILSFORREVENUESPLITBUSINESSUNIT(@REVENUEID)
          where AUDITDATE >= isnull(@STARTDATE, AUDITDATE) and AUDITDATE <= isnull(@ENDDATE, AUDITDATE)

        --Tributes

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 17
          insert into @RESULTS
          select          
            RECORDID,
            case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
            CHANGEDBYUSER,
            PROCESS,
            FIELD,
            OLD,
            NEW,
            210 as [SEQUENCE],
            ACTION,
            CHANGEDBYAPP         
          from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('TRIBUTEID, AMOUNT','REVENUETRIBUTE','REVENUEID',@REVENUEID,null,'Tribute','',@STARTDATE,@ENDDATE)    

        --Gift Aid/VAT

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 18
        begin
          insert into @RESULTS
          select 
            DETAILS.RECORDID,
            case when DETAILS.ACTION = 'Insert' then DETAILS.DATEADDED when DETAILS.ACTION = 'Update' then DETAILS.DATECHANGED else DETAILS.AUDITDATE end as [AUDITDATE],
            DETAILS.CHANGEDBYUSER,
            DETAILS.PROCESS,
            DETAILS.FIELD,
            DETAILS.OLD,
            DETAILS.NEW,
            220 as [SEQUENCE],
            DETAILS.ACTION,
            DETAILS.CHANGEDBYAPP
          from @REVENUESPLITSTIDS as REVENUESPLIT
          cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('DECLINESGIFTAID, TAXCLAIMNUMBER, BASETAXCLAIMAMOUNT, TRANSITIONALTAXCLAIMAMOUNT, ISCOVENANT, ISSPONSORSHIP, CHARITYCLAIMREFERENCENUMBER', 'REVENUESPLITGIFTAID', 'ID', REVENUESPLIT.ID, @REVENUEDATEADDED, 'Gift Aid', '',@STARTDATE,@ENDDATE) DETAILS

          insert into @RESULTS
          select
            RECORDID,
            case when ACTION = 'Insert' then DATEADDED when ACTION = 'Update' then DATECHANGED else AUDITDATE end as [AUDITDATE],
            CHANGEDBYUSER,
            PROCESS,
            FIELD,
            OLD,
            NEW,
            230 as [SEQUENCE],
            ACTION,
            CHANGEDBYAPP
          from dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('AMOUNTTOTAX, VATTAXRATEID, VATAMOUNT','REVENUEVAT', 'ID', @REVENUEID, @REVENUEDATEADDED, 'VAT', '',@STARTDATE,@ENDDATE)
        end

        --Revenue schedule credit card

        if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 6
        begin
          with REVENUESCHEDULECREDITCARDS_CTE as
          (
            select distinct CREDITCARDID
            from 
              (select CREDITCARDID from dbo.REVENUESCHEDULE where ID = @REVENUEID
              union all
              select CREDITCARDID from dbo.REVENUESCHEDULEAUDIT where AUDITRECORDID = @REVENUEID
            ) a
            where CREDITCARDID is not null
          )
          insert into @RESULTS
          select 
            DETAILS.RECORDID,
            case when DETAILS.ACTION = 'Insert' then DETAILS.DATEADDED when DETAILS.ACTION = 'Update' then DETAILS.DATECHANGED else DETAILS.AUDITDATE end as [AUDITDATE],
            DETAILS.CHANGEDBYUSER,
            DETAILS.PROCESS,
            DETAILS.FIELD,
            DETAILS.OLD,
            DETAILS.NEW,
            60 as [SEQUENCE],
            DETAILS.ACTION,
            DETAILS.CHANGEDBYAPP
          from REVENUESCHEDULECREDITCARDS_CTE as CREDITCARD
          cross apply dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST_WITHDATERANGE('CARDHOLDERNAME, CREDITTYPECODEID, CREDITCARDPARTIALNUMBER, EXPIRESON', 'CREDITCARD', 'ID', CREDITCARD.CREDITCARDID, @REVENUEDATEADDED, 'Credit card', '',@STARTDATE,@ENDDATE) DETAILS
        end

        if @ACTIONTYPECODE = 1
          delete from @RESULTS where ACTION = 'Delete' or ACTION = 'Update';
        if @ACTIONTYPECODE = 2
          delete from @RESULTS where ACTION = 'Insert' or ACTION = 'Delete';
        if @ACTIONTYPECODE = 3
          delete from @RESULTS where ACTION = 'Insert' or ACTION = 'Update';

        return;
      end