UFN_REVENUERECOGNITIONWITHGIFTAID_GETAMOUNTSINCURRENCY_BULK_2

            CREATE function [dbo].[UFN_REVENUERECOGNITIONWITHGIFTAID_GETAMOUNTSINCURRENCY_BULK_2]
            (
                @CURRENCYID uniqueidentifier,
                @ORGANIZATIONCURRENCYID uniqueidentifier,
                @DECIMALDIGITS tinyint,
                @ROUNDINGTYPECODE tinyint,
                @ISUK bit,
                @CONSTITUENTID uniqueidentifier
            )
            returns @REVENUERECOGNITIONWITHGIFTAIDDATA table(
                    ID uniqueidentifier,
                    AMOUNTINCURRENCY decimal,
                    DATEAMOUNTCHANGED datetime,
                    REVENUESPLITID uniqueidentifier,
                    CONSTITUENTID uniqueidentifier,
                    EFFECTIVEDATE datetime,
                    DATEADDED datetime,
                    REVENUERECOGNITIONTYPECODEID uniqueidentifier,
                    TSLONG bigint,
                    REVENUEID uniqueidentifier,
                    TRANSACTIONTYPECODE tinyint,
                    REVENUECONSTITUENTID uniqueidentifier,
                    DATE datetime,
                    REVENUEDATEADDED datetime,
                    APPLICATIONCODE tinyint,
                    REVENUESPLITTYPECODE tinyint,
                    DESIGNATIONID uniqueidentifier,
                    REVENUESPLITTSLONG bigint,
                    TRANSACTIONTYPE nvarchar(200),
                    ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                    ORGANIZATIONAMOUNT decimal
            )
            as
                begin
                declare @TMP_DATA_REVENUERECOGNITIONSUMMARYWITHGIFTAID table    
                (    
                     ID uniqueidentifier,    
                     AMOUNTINCURRENCY decimal,    
                     REVENUESPLITID uniqueidentifier,    
                     CONSTITUENTID uniqueidentifier,    
                     EFFECTIVEDATE datetime,    
                     DATEADDED datetime,    
                     REVENUERECOGNITIONTYPECODEID uniqueidentifier,    
                     TSLONG bigint,    
                     REVENUEID uniqueidentifier,    
                     TRANSACTIONTYPECODE tinyint,    
                     REVENUECONSTITUENTID uniqueidentifier,    
                     DATE datetime,    
                     REVENUEDATEADDED datetime,    
                     APPLICATIONCODE tinyint,    
                     REVENUESPLITTYPECODE tinyint,    
                     DESIGNATIONID uniqueidentifier,    
                     REVENUESPLITTSLONG bigint,    
                     TRANSACTIONTYPE nvarchar(200),    
                     ORGANIZATIONEXCHANGERATEID uniqueidentifier,    
                     ORGANIZATIONAMOUNT decimal,    
                     REVENUESPLITREVENUEID uniqueidentifier,    
                     DELETEDON datetime,    
                     TYPECODE tinyint,    
                     DATEAMOUNTCHANGED datetime,    
                     TAXCLAIMAMOUNTINCURRENCY decimal,    
                     DATETAXDECLARATIONCHANGED datetime,    
                     ELIGIBLEGIFTAIDID uniqueidentifier    
                );    
                declare @TMP_INTERMEDIATE_DATA_REVENUERECOGNITIONSUMMARYWITHGIFTAID table    
                (    
                     ID uniqueidentifier,    
                     AMOUNTINCURRENCY decimal,    
                     REVENUESPLITID uniqueidentifier,    
                     CONSTITUENTID uniqueidentifier,    
                     EFFECTIVEDATE datetime,    
                     DATEADDED datetime,    
                     REVENUERECOGNITIONTYPECODEID uniqueidentifier,    
                     TSLONG bigint,    
                     REVENUEID uniqueidentifier,    
                     TRANSACTIONTYPECODE tinyint,    
                     REVENUECONSTITUENTID uniqueidentifier,    
                     DATE datetime,    
                     REVENUEDATEADDED datetime,    
                     APPLICATIONCODE tinyint,    
                     REVENUESPLITTYPECODE tinyint,    
                     DESIGNATIONID uniqueidentifier,    
                     REVENUESPLITTSLONG bigint,    
                     TRANSACTIONTYPE nvarchar(200),    
                     ORGANIZATIONEXCHANGERATEID uniqueidentifier,    
                     ORGANIZATIONAMOUNT decimal,    
                     REVENUESPLITREVENUEID uniqueidentifier,      
                     DATEAMOUNTCHANGED datetime,    
                     TAXCLAIMAMOUNTINCURRENCY decimal,    
                     DATETAXDECLARATIONCHANGED datetime,    
                     ELIGIBLEGIFTAIDID uniqueidentifier    
                ); 

                if @ISUK = 1
                begin
                        insert into @TMP_INTERMEDIATE_DATA_REVENUERECOGNITIONSUMMARYWITHGIFTAID 
                        select    
                                REVENUERECOGNITION.ID,    
                                REVENUERECOGNITION.AMOUNTINCURRENCY,    
                                REVENUERECOGNITION.REVENUESPLITID,    
                                REVENUERECOGNITION.CONSTITUENTID,    
                                REVENUERECOGNITION.EFFECTIVEDATE,    
                                REVENUERECOGNITION.DATEADDED,    
                                REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID,    
                                REVENUERECOGNITION.TSLONG,    
                                REVENUERECOGNITION.REVENUEID,    
                                REVENUERECOGNITION.TRANSACTIONTYPECODE,    
                                REVENUERECOGNITION.REVENUECONSTITUENTID,    
                                REVENUERECOGNITION.DATE,    
                                REVENUERECOGNITION.REVENUEDATEADDED,    
                                REVENUERECOGNITION.APPLICATIONCODE,    
                                REVENUERECOGNITION.REVENUESPLITTYPECODE,    
                                REVENUERECOGNITION.DESIGNATIONID,    
                                REVENUERECOGNITION.REVENUESPLITTSLONG,    
                                REVENUERECOGNITION.TRANSACTIONTYPE,    
                                REVENUERECOGNITION.ORGANIZATIONEXCHANGERATEID,    
                                REVENUERECOGNITION.ORGANIZATIONAMOUNT,    
                                REVENUESPLIT.REVENUEID REVENUESPLITREVENUEID,    
                                REVENUESPLITGIFTAID.DATEAMOUNTCHANGED,    
                                REVENUESPLITGIFTAID.TAXCLAIMAMOUNTINCURRENCY,    
                                ELIGIBLEGIFTAID.DATETAXDECLARATIONCHANGED,    
                                ELIGIBLEGIFTAID.ID ELIGIBLEGIFTAIDID    
                            from    
                                dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) REVENUERECOGNITION    
                                inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) REVENUESPLIT on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID     
                                left join dbo.UFN_REVENUESPLITGIFTAID_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID    
                                left join dbo.UFN_REVENUESPLITGIFTAID_GETELIGIBLE(1) ELIGIBLEGIFTAID on ELIGIBLEGIFTAID.ID = REVENUESPLITGIFTAID.ID    
                            where    
                                REVENUERECOGNITION.CONSTITUENTID = @CONSTITUENTID 
                            option(RECOMPILE);
                end

                insert into @TMP_DATA_REVENUERECOGNITIONSUMMARYWITHGIFTAID     
                    select     
                     RECOGNITIONDETAIL.ID,    
                     RECOGNITIONDETAIL.AMOUNTINCURRENCY,    
                     RECOGNITIONDETAIL.REVENUESPLITID,    
                     RECOGNITIONDETAIL.CONSTITUENTID,    
                     RECOGNITIONDETAIL.EFFECTIVEDATE,    
                     RECOGNITIONDETAIL.DATEADDED,    
                     RECOGNITIONDETAIL.REVENUERECOGNITIONTYPECODEID,    
                     RECOGNITIONDETAIL.TSLONG,    
                     RECOGNITIONDETAIL.REVENUEID,    
                     RECOGNITIONDETAIL.TRANSACTIONTYPECODE,    
                     RECOGNITIONDETAIL.REVENUECONSTITUENTID,    
                     RECOGNITIONDETAIL.DATE,    
                     RECOGNITIONDETAIL.REVENUEDATEADDED,    
                     RECOGNITIONDETAIL.APPLICATIONCODE,    
                     RECOGNITIONDETAIL.REVENUESPLITTYPECODE,    
                     RECOGNITIONDETAIL.DESIGNATIONID,    
                     RECOGNITIONDETAIL.REVENUESPLITTSLONG,    
                     RECOGNITIONDETAIL.TRANSACTIONTYPE,    
                     RECOGNITIONDETAIL.ORGANIZATIONEXCHANGERATEID,    
                     RECOGNITIONDETAIL.ORGANIZATIONAMOUNT,    
                     RECOGNITIONDETAIL.REVENUESPLITREVENUEID,    
                     FINANCIALTRANSACTION.DELETEDON,    
                     FINANCIALTRANSACTION.TYPECODE,    
                     RECOGNITIONDETAIL.DATEAMOUNTCHANGED,    
                     RECOGNITIONDETAIL.TAXCLAIMAMOUNTINCURRENCY,    
                     RECOGNITIONDETAIL.DATETAXDECLARATIONCHANGED,    
                     RECOGNITIONDETAIL.ELIGIBLEGIFTAIDID    
                    from    
                     @TMP_INTERMEDIATE_DATA_REVENUERECOGNITIONSUMMARYWITHGIFTAID RECOGNITIONDETAIL    
                     inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = REVENUESPLITREVENUEID;    

                    insert into @REVENUERECOGNITIONWITHGIFTAIDDATA    
                    select     
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.ID,    
                     case    
                        when dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.REVENUESPLITID, @CURRENCYID) > 0 then    
                         case TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.TYPECODE    
                         when 0 then    
                            case    
                            when TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.AMOUNTINCURRENCY > dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.REVENUESPLITID, @CURRENCYID) then case when TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.ELIGIBLEGIFTAIDID is not null then TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.TAXCLAIMAMOUNTINCURRENCY else 0 end + TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.AMOUNTINCURRENCY    
                            else TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.AMOUNTINCURRENCY/dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.REVENUESPLITID, @CURRENCYID) * (case when TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.ELIGIBLEGIFTAIDID is not null then TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.TAXCLAIMAMOUNTINCURRENCY else 0 end) + TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.AMOUNTINCURRENCY    
                         end    
                         when 1 then    
                         case    
                            when TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.AMOUNTINCURRENCY > dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.REVENUESPLITID, @CURRENCYID)    
                            then coalesce(PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT.TAXCLAIMAMOUNTINCURRENCY,0)    
                            + TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.AMOUNTINCURRENCY    
                            else TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.AMOUNTINCURRENCY/dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.REVENUESPLITID, @CURRENCYID) * coalesce(PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT.TAXCLAIMAMOUNTINCURRENCY,0)    
                            + TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.AMOUNTINCURRENCY    
                         end    
                         else TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.AMOUNTINCURRENCY    
                         end    
                        else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.REVENUESPLITID, @CURRENCYID)    
                     end [AMOUNTINCURRENCY],    
                     --Returns the date when amount changed    

                     -- considers when the net revenue changes and when the gift aid eligibility changes    

                     case    
                        when TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.ELIGIBLEGIFTAIDID is not null    
                        then     
                         case    
                            when TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.DATETAXDECLARATIONCHANGED >= TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.DATEAMOUNTCHANGED    
                            then TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.DATETAXDECLARATIONCHANGED    
                            else TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.DATEAMOUNTCHANGED    
                         end    
                        else TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.DATEAMOUNTCHANGED    
                     end as DATEGROSSAMOUNTCHANGED,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.REVENUESPLITID,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.CONSTITUENTID,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.EFFECTIVEDATE,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.DATEADDED,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.REVENUERECOGNITIONTYPECODEID,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.TSLONG,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.REVENUEID,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.TRANSACTIONTYPECODE,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.REVENUECONSTITUENTID,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.DATE,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.REVENUEDATEADDED,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.APPLICATIONCODE,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.REVENUESPLITTYPECODE,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.DESIGNATIONID,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.REVENUESPLITTSLONG,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.TRANSACTIONTYPE,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.ORGANIZATIONEXCHANGERATEID,    
                     TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.ORGANIZATIONAMOUNT    
                    from     
                     @TMP_DATA_REVENUERECOGNITIONSUMMARYWITHGIFTAID TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID    
                     left join dbo.UFN_PLEDGEINSTALLMENTSPLIT_CALCULATETAXCLAIMAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT    
                     on PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT.REVENUESPLITID = TMPDATAREVENUERECOGNITIONSUMMARYWITHGIFTAID.REVENUESPLITID    
                    where    
                     DELETEDON is NULL and ((TYPECODE between 0 and 9) or (TYPECODE = 15));    
                    return;
            end