USP_RECONCILEMATCHINGGIFT_UPDATECLAIMSPLITS

CREATE procedure dbo.USP_RECONCILEMATCHINGGIFT_UPDATECLAIMSPLITS
(
    @MGCLAIMINSTALLMENTSPLITID uniqueidentifier output,
    @APPLICATIONID uniqueidentifier,
    @MGCLAIMREVENUEID uniqueidentifier,
    @MGCLAIMDESIGNATIONID uniqueidentifier,
    @MGCLAIMAMOUNT money,
    @APPLIEDAMOUNT money,
    @APPLICATIONDESIGNATIONID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @CREATIONDATE datetime, 
    @MGCLAIMREVENUESPLITID uniqueidentifier = null output
)
as
    set nocount on

    if @MGCLAIMAMOUNT = @APPLIEDAMOUNT
    begin
        update dbo.REVENUESPLIT set
            DESIGNATIONID = @APPLICATIONDESIGNATIONID,
            DATECHANGED = @CREATIONDATE,
            CHANGEDBYID = @CHANGEAGENTID
        where ID = @APPLICATIONID

        --Bug 129338 - AdamBu - 12/20/10 - Clear existing campaigns and add the defaults from the new designation.

        delete dbo.REVENUESPLITCAMPAIGN
        where REVENUESPLITID = @APPLICATIONID

        insert into dbo.REVENUESPLITCAMPAIGN(
            REVENUESPLITID, 
            CAMPAIGNID, 
            CAMPAIGNSUBPRIORITYID, 
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED
        )
        select distinct
            REVENUESPLIT.ID,
            DESIGNATIONCAMPAIGN.CAMPAIGNID,
            DESIGNATIONCAMPAIGN.CAMPAIGNSUBPRIORITYID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CREATIONDATE,
            @CREATIONDATE
        from dbo.REVENUE
            inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
            cross apply dbo.UFN_DESIGNATION_GETCAMPAIGNSTODEFAULT(  
                REVENUESPLIT.DESIGNATIONID, 
                REVENUE.DATE
            ) as DESIGNATIONCAMPAIGN
        where REVENUESPLIT.ID = @APPLICATIONID

        update dbo.INSTALLMENTSPLIT set
            DESIGNATIONID = @APPLICATIONDESIGNATIONID,
            DATECHANGED = @CREATIONDATE,
            CHANGEDBYID = @CHANGEAGENTID
        where
            PLEDGEID = @MGCLAIMREVENUEID and
            DESIGNATIONID = @MGCLAIMDESIGNATIONID

        select @MGCLAIMINSTALLMENTSPLITID = ID
        from dbo.INSTALLMENTSPLIT
        where
            PLEDGEID = @MGCLAIMREVENUEID and
            DESIGNATIONID = @APPLICATIONDESIGNATIONID

        --Bug 129338 - AdamBu - 12/20/10 - Set the output value for the MG split ID.

        set @MGCLAIMREVENUESPLITID = @APPLICATIONID 
    end
    else
    begin
        declare @MGCLAIMBASEAMOUNT money, @APPLIEDBASEAMOUNT money, @APPLIEDORGANIZATIONAMOUNT money;
        declare @BASECURRENCYID uniqueidentifier, @BASEEXCHANGERATEID uniqueidentifier, @TRANSACTIONCURRENCYID uniqueidentifier, @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
        declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

        select 
            @MGCLAIMBASEAMOUNT = AMOUNT,
            @BASECURRENCYID = BASECURRENCYID,
            @BASEEXCHANGERATEID = BASEEXCHANGERATEID,
            @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
            @ORGANIZATIONEXCHANGERATEID = ORGANIZATIONEXCHANGERATEID
        from dbo.REVENUESPLIT
        where ID = @APPLICATIONID;

        exec dbo.USP_CURRENCY_GETCURRENCYVALUES
            @AMOUNT = @APPLIEDAMOUNT,
            @DATE = null,
            @BASECURRENCYID = @BASECURRENCYID,
            @BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
            @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
            @BASEAMOUNT = @APPLIEDBASEAMOUNT output,
            @ORGANIZATIONAMOUNT = @APPLIEDORGANIZATIONAMOUNT output,
            @ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID

        update dbo.REVENUESPLIT set
            TRANSACTIONAMOUNT = (@MGCLAIMAMOUNT - @APPLIEDAMOUNT),
            AMOUNT = @MGCLAIMBASEAMOUNT - @APPLIEDBASEAMOUNT,
            ORGANIZATIONAMOUNT = ORGANIZATIONAMOUNT - @APPLIEDORGANIZATIONAMOUNT,
            DATECHANGED = @CREATIONDATE,
            CHANGEDBYID = @CHANGEAGENTID
        where ID = @APPLICATIONID

        update dbo.INSTALLMENTSPLIT set
            TRANSACTIONAMOUNT = (@MGCLAIMAMOUNT - @APPLIEDAMOUNT),
            AMOUNT = @MGCLAIMBASEAMOUNT - @APPLIEDBASEAMOUNT,
            ORGANIZATIONAMOUNT = ORGANIZATIONAMOUNT - @APPLIEDORGANIZATIONAMOUNT,
            DATECHANGED = @CREATIONDATE,
            CHANGEDBYID = @CHANGEAGENTID
        where
            PLEDGEID = @MGCLAIMREVENUEID and
            DESIGNATIONID = @MGCLAIMDESIGNATIONID

        -- Update recognition credit and solicitor amounts

        declare @BASEDECIMALDIGITS tinyint;
        select @BASEDECIMALDIGITS = DECIMALDIGITS 
        from dbo.CURRENCY 
        where ID = @BASECURRENCYID;


        declare @MGCLAIMRECOGNITIONS xml
        set @MGCLAIMRECOGNITIONS = (
            select ID, AMOUNT
            from dbo.REVENUERECOGNITION
            where REVENUESPLITID = @APPLICATIONID
            for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
        )

        declare @MGCLAIMSOLICITORS xml
        set @MGCLAIMSOLICITORS = (
            select ID, AMOUNT
            from dbo.REVENUESOLICITOR
            where REVENUESPLITID = @APPLICATIONID
            for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
        )

        update dbo.REVENUERECOGNITION set
            AMOUNT = UPDATEDAMOUNT.AMOUNT,
            ORGANIZATIONAMOUNT = case
                when BASECURRENCYID <> @ORGANIZATIONCURRENCYID
                    then dbo.UFN_CURRENCY_CONVERT(UPDATEDAMOUNT.AMOUNT, ORGANIZATIONEXCHANGERATEID)
                else UPDATEDAMOUNT.AMOUNT
            end,    
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CREATIONDATE
        from dbo.UFN_SPLITS_PRORATEAMOUNTS(@MGCLAIMBASEAMOUNT, @MGCLAIMBASEAMOUNT - @APPLIEDBASEAMOUNT, @BASEDECIMALDIGITS, @MGCLAIMRECOGNITIONS) UPDATEDAMOUNT
        where REVENUERECOGNITION.ID = UPDATEDAMOUNT.ID

        update dbo.REVENUESOLICITOR set
            AMOUNT = UPDATEDAMOUNT.AMOUNT,
            ORGANIZATIONAMOUNT = case
                when BASECURRENCYID <> @ORGANIZATIONCURRENCYID
                    then dbo.UFN_CURRENCY_CONVERT(UPDATEDAMOUNT.AMOUNT, ORGANIZATIONEXCHANGERATEID)
                else UPDATEDAMOUNT.AMOUNT
            end,    
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CREATIONDATE
        from dbo.UFN_SPLITS_PRORATEAMOUNTS(@MGCLAIMBASEAMOUNT, @MGCLAIMBASEAMOUNT - @APPLIEDBASEAMOUNT, @BASEDECIMALDIGITS, @MGCLAIMSOLICITORS) UPDATEDAMOUNT
        where REVENUESOLICITOR.ID = UPDATEDAMOUNT.ID

        --Bug 129338 - AdamBu - 12/20/10 - @MGCLAIMREVENUESPLITID is now an output parameter.

        --declare @MGCLAIMREVENUESPLITID uniqueidentifier;

        set @MGCLAIMREVENUESPLITID = newid();
        insert into dbo.REVENUESPLIT (
            ID, 
            REVENUEID,
            DESIGNATIONID,
            TRANSACTIONAMOUNT,
            TRANSACTIONCURRENCYID,
            AMOUNT,
            BASECURRENCYID,
            BASEEXCHANGERATEID,
            ORGANIZATIONAMOUNT,
            ORGANIZATIONEXCHANGERATEID,
            DATEADDED, 
            DATECHANGED, 
            ADDEDBYID, 
            CHANGEDBYID
        )
        select 
            @MGCLAIMREVENUESPLITID,
            @MGCLAIMREVENUEID,
            @APPLICATIONDESIGNATIONID,
            @APPLIEDAMOUNT,
            TRANSACTIONCURRENCYID,
            @APPLIEDBASEAMOUNT,
            BASECURRENCYID,
            BASEEXCHANGERATEID,
            @APPLIEDORGANIZATIONAMOUNT,
            ORGANIZATIONEXCHANGERATEID,
            @CREATIONDATE, 
            @CREATIONDATE, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID
        from dbo.REVENUESPLIT
        where ID = @APPLICATIONID;

        --Bug 129338 - AdamBu - 12/20/10 - Add the default campaigns for the new MG split.

        insert into dbo.REVENUESPLITCAMPAIGN(
            REVENUESPLITID, 
            CAMPAIGNID, 
            CAMPAIGNSUBPRIORITYID, 
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED
        )
        select distinct
            REVENUESPLIT.ID,
            DESIGNATIONCAMPAIGN.CAMPAIGNID,
            DESIGNATIONCAMPAIGN.CAMPAIGNSUBPRIORITYID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CREATIONDATE,
            @CREATIONDATE
        from dbo.REVENUE
            inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
            cross apply dbo.UFN_DESIGNATION_GETCAMPAIGNSTODEFAULT(  
                REVENUESPLIT.DESIGNATIONID, 
                REVENUE.DATE
            ) as DESIGNATIONCAMPAIGN
        where REVENUESPLIT.ID = @MGCLAIMREVENUESPLITID

        set @MGCLAIMINSTALLMENTSPLITID = newid();
        insert into dbo.INSTALLMENTSPLIT (
            ID, 
            INSTALLMENTID,
            PLEDGEID,
            DESIGNATIONID,
            TRANSACTIONAMOUNT,
            TRANSACTIONCURRENCYID,
            AMOUNT,
            BASECURRENCYID,
            BASEEXCHANGERATEID,
            ORGANIZATIONAMOUNT,
            ORGANIZATIONEXCHANGERATEID,
            DATEADDED, 
            DATECHANGED, 
            ADDEDBYID, 
            CHANGEDBYID
        )
        select
            @MGCLAIMINSTALLMENTSPLITID,
            INSTALLMENT.ID,
            @MGCLAIMREVENUEID,
            @APPLICATIONDESIGNATIONID,
            @APPLIEDAMOUNT,
            TRANSACTIONCURRENCYID,
            @APPLIEDBASEAMOUNT,
            BASECURRENCYID,
            BASEEXCHANGERATEID,
            @APPLIEDORGANIZATIONAMOUNT,
            ORGANIZATIONEXCHANGERATEID,
            @CREATIONDATE, 
            @CREATIONDATE, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID
        from dbo.INSTALLMENT
        where REVENUEID = @MGCLAIMREVENUEID

        insert into dbo.REVENUERECOGNITION (
            REVENUESPLITID, 
            CONSTITUENTID, 
            AMOUNT, 
            BASECURRENCYID, 
            ORGANIZATIONAMOUNT, 
            ORGANIZATIONEXCHANGERATEID, 
            EFFECTIVEDATE, 
            REVENUERECOGNITIONTYPECODEID, 
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED
        )
        select
            @MGCLAIMREVENUESPLITID,
            REVENUERECOGNITION.CONSTITUENTID,
            UPDATEDAMOUNT.AMOUNT,
            REVENUERECOGNITION.BASECURRENCYID,
            case
                when REVENUERECOGNITION.BASECURRENCYID <> @ORGANIZATIONCURRENCYID
                    then dbo.UFN_CURRENCY_CONVERT(UPDATEDAMOUNT.AMOUNT, REVENUERECOGNITION.ORGANIZATIONEXCHANGERATEID)
                else UPDATEDAMOUNT.AMOUNT
            end ORGANIZATIONAMOUNT,    
            REVENUERECOGNITION.ORGANIZATIONEXCHANGERATEID,
            REVENUERECOGNITION.EFFECTIVEDATE,
            REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CREATIONDATE,
            @CREATIONDATE
        from dbo.UFN_SPLITS_PRORATEAMOUNTS(@MGCLAIMBASEAMOUNT, @APPLIEDBASEAMOUNT, @BASEDECIMALDIGITS, @MGCLAIMRECOGNITIONS) UPDATEDAMOUNT
            inner join dbo.REVENUERECOGNITION on REVENUERECOGNITION.ID = UPDATEDAMOUNT.ID

        insert into dbo.REVENUESOLICITOR (
            REVENUESPLITID, 
            CONSTITUENTID, 
            AMOUNT, 
            BASECURRENCYID, 
            ORGANIZATIONAMOUNT, 
            ORGANIZATIONEXCHANGERATEID, 
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED
        )
        select
            @MGCLAIMREVENUESPLITID,
            REVENUESOLICITOR.CONSTITUENTID,
            UPDATEDAMOUNT.AMOUNT,
            REVENUESOLICITOR.BASECURRENCYID,
            case
                when REVENUESOLICITOR.BASECURRENCYID <> @ORGANIZATIONCURRENCYID
                    then dbo.UFN_CURRENCY_CONVERT(UPDATEDAMOUNT.AMOUNT, REVENUESOLICITOR.ORGANIZATIONEXCHANGERATEID)
                else UPDATEDAMOUNT.AMOUNT
            end ORGANIZATIONAMOUNT,    
            REVENUESOLICITOR.ORGANIZATIONEXCHANGERATEID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CREATIONDATE,
            @CREATIONDATE
        from dbo.UFN_SPLITS_PRORATEAMOUNTS(@MGCLAIMBASEAMOUNT, @APPLIEDBASEAMOUNT, @BASEDECIMALDIGITS, @MGCLAIMSOLICITORS) UPDATEDAMOUNT
            inner join dbo.REVENUESOLICITOR on REVENUESOLICITOR.ID = UPDATEDAMOUNT.ID
    end