USP_MATCHINGGIFTPLEDGE_ADD

CREATE procedure dbo.USP_MATCHINGGIFTPLEDGE_ADD
(
    @ID uniqueidentifier = null output, 
    @CHANGEAGENTID uniqueidentifier,
    @ORIGINALGIFTID uniqueidentifier,
    @MATCHINGORGANIZATIONID uniqueidentifier,
    @DATE datetime,
    @AMOUNT money,
    @SPLITS xml,
    @MATCHINGGIFTCONDITIONID uniqueidentifier = null,
    @RELATIONSHIPID uniqueidentifier = null,
    @TRANSACTIONCURRENCYID uniqueidentifier = null,
    @BASECURRENCYID uniqueidentifier = null,
    @BASEEXCHANGERATEID uniqueidentifier = null
)
as
    set nocount on;

    declare @CURRENTDATE datetime;    
    declare @SOURCECONSTITUENTID uniqueidentifier;
  declare @PDACCOUNTSYSTEMID uniqueidentifier = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B';

    declare @STARTDATE datetime;
    declare @FREQUENCYCODE tinyint;
    declare @NUMBEROFINSTALLMENTS int;
    declare @INSTALLMENTSEQUENCE int;

    set @STARTDATE = @DATE;
    set @FREQUENCYCODE = 5; --Single Installment

    set @NUMBEROFINSTALLMENTS = 1;
    set @INSTALLMENTSEQUENCE = 1;

    if @ID is null
        set @ID = newid();

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    set @CURRENTDATE = getdate();          

    --Set currency parameters for backwards compatibility

    if @TRANSACTIONCURRENCYID is null
        set @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    if @BASECURRENCYID is null
        set @BASECURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    declare @SUM money;
    declare @COUNT int;

    begin try
        exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @AMOUNT, 3, default, @TRANSACTIONCURRENCYID;

        select 
            @COUNT = count(FINANCIALTRANSACTION.ID) 
        from dbo.FINANCIALTRANSACTION
        inner join dbo.REVENUEMATCHINGGIFT RMG on FINANCIALTRANSACTION.ID = RMG.ID
        where FINANCIALTRANSACTION.CONSTITUENTID = @MATCHINGORGANIZATIONID 
            and RMG.MGSOURCEREVENUEID = @ORIGINALGIFTID;

        --if @COUNT > 0 

        --raiserror('An organization cannot match a single gift more than once.', 13, 1);


        declare @paymentid uniqueidentifier;
        set @paymentid=NewID();

        declare @MAILINGID uniqueidentifier;
        declare @APPEALID uniqueidentifier;
        declare @SOURCECODE nvarchar(50);

        select @MAILINGID = MAILINGID, @APPEALID = APPEALID, @SOURCECODE = SOURCECODE
        from dbo.REVENUE_EXT R
        where R.ID = @ORIGINALGIFTID;

        --Multicurrency - AdamBu 5/7/10 - Retrieve and calculate the necessary multicurrency values.    

        declare @BASEAMOUNT money;                    
        declare @ORGANIZATIONCURRENCYID uniqueidentifier;
        declare @ORGANIZATIONAMOUNT money;
        declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

        exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT, @DATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 1;

    insert into dbo.FINANCIALTRANSACTION (ID, CONSTITUENTID, DATE, POSTSTATUSCODE, POSTDATE, BASEAMOUNT, TYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ORGAMOUNT, ORGEXCHANGERATEID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, PDACCOUNTSYSTEMID)
      values (@ID, @MATCHINGORGANIZATIONID, @DATE, 3, null, @BASEAMOUNT, 3, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @ORGANIZATIONAMOUNT, @ORGANIZATIONEXCHANGERATEID, @AMOUNT, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID, @PDACCOUNTSYSTEMID);

    insert into dbo.REVENUE_EXT (ID, DONOTRECEIPT, RECEIPTAMOUNT, NONPOSTABLEBASECURRENCYID, MAILINGID, APPEALID, SOURCECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
      values (@ID, 1, 0, @BASECURRENCYID, @MAILINGID, @APPEALID, @SOURCECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE ,@CURRENTDATE)

        --Add origination source

        exec dbo.USP_REVENUE_ADDORIGIN @ID, @MATCHINGORGANIZATIONID, @CHANGEAGENTID, @CURRENTDATE;

        insert into dbo.REVENUEPAYMENTMETHOD (REVENUEID, PAYMENTMETHODCODE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values (@ID, 9, @BASEAMOUNT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE ,@CURRENTDATE);

        insert into dbo.REVENUEMATCHINGGIFT (ID, MATCHINGGIFTCONDITIONID, MGSOURCEREVENUEID, RELATIONSHIPID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values (@ID, @MATCHINGGIFTCONDITIONID, @ORIGINALGIFTID, @RELATIONSHIPID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        insert into dbo.REVENUESCHEDULE (ID, STARTDATE, FREQUENCYCODE, NUMBEROFINSTALLMENTS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values (@ID, @STARTDATE, @FREQUENCYCODE, @NUMBEROFINSTALLMENTS, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        insert into dbo.INSTALLMENT (ID, REVENUEID, AMOUNT, DATE, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,BASECURRENCYID,ORGANIZATIONAMOUNT,ORGANIZATIONEXCHANGERATEID,TRANSACTIONAMOUNT,TRANSACTIONCURRENCYID,BASEEXCHANGERATEID)
            values (newid(), @ID, @BASEAMOUNT, @DATE, @INSTALLMENTSEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,@BASECURRENCYID,@ORGANIZATIONAMOUNT,@ORGANIZATIONEXCHANGERATEID,@AMOUNT,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID);

        --Multicurrency - AdamBu 3/30/10 - Process the splits xml to calculate the base and organization amounts and place them in proper nodes.

        set @SPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@SPLITS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)
        exec dbo.USP_REVENUE_GETSPLITS_2_ADDFROMXML @ID, @SPLITS, @CHANGEAGENTID, @CURRENTDATE;

        exec dbo.USP_PLEDGE_GENERATEINSTALLMENTSPLITS @ID, @CHANGEAGENTID, @CURRENTDATE;

        if dbo.UFN_PLEDGE_INSTALLMENTSPLITSBALANCE(@ID) = 0
            raiserror('INSTALLMENTSPLITSBALANCE', 13, 10);

        --If recognition credits should be added automatically when a MG claim is created, add them.

        declare @AUTOADDMGCLAIMCREDIT bit;
        declare @CLAIMDEFAULTCREDITTYPEID uniqueidentifier;
        declare @AUTOADDMGCLAIMCREDITFORORG bit;
        declare @CLAIMDEFAULTCREDITTYPEIDFORORG uniqueidentifier;
        select 
            @AUTOADDMGCLAIMCREDIT = ADDRECOGNITIONCREDITSONMGCLAIMADD,
            @CLAIMDEFAULTCREDITTYPEID = 
                case MGCLAIMADDRECOGNITIONTYPECODE
                    when 0 then CLAIMREVENUERECOGNITIONTYPECODEID
                    when 1 then null
                end,
            @AUTOADDMGCLAIMCREDITFORORG = ADDRECOGNITIONCREDITSTOMATCHINGORGONMGCLAIMADD,
            @CLAIMDEFAULTCREDITTYPEIDFORORG = MATCHINGORGCLAIMREVENUERECOGNITIONTYPECODEID
        from MATCHINGGIFTPREFERENCEINFO;

        declare @MGSPLITS xml;
        set @MGSPLITS = dbo.UFN_REVENUE_GETSPLITS_2_TOITEMLISTXML(@ID);
    set @MGSPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@MGSPLITS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID);

    declare @DEFAULTANONYMOUSRECOGNITION bit = 0;

    --Select the value for default anonymous recognition setting

    select @DEFAULTANONYMOUSRECOGNITION = DEFAULTANONYMOUSRECOGNITION
    from dbo.RECOGNITIONDEFAULT

        if @AUTOADDMGCLAIMCREDIT = 1
        begin
            declare @ORIGINALDONORID uniqueidentifier
            declare @ORIGINALDONATIONDATE datetime
            select 
                @ORIGINALDONORID=FINANCIALTRANSACTION.CONSTITUENTID,
                @ORIGINALDONATIONDATE=FINANCIALTRANSACTION.DATE
            from dbo.REVENUE_EXT
      inner join dbo.FINANCIALTRANSACTION on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
            where 
                REVENUE_EXT.ID = @ORIGINALGIFTID
                and (REVENUE_EXT.GIVENANONYMOUSLY = 0 or @DEFAULTANONYMOUSRECOGNITION = 1)

            if @ORIGINALDONORID is not null
            begin                        
                if (select count(*) from dbo.MATCHINGGIFTPREFERENCEINFO
                    where MGCLAIMADDRECOGNITIONTYPECODE = 0) > 0
                begin                    
                    exec dbo.USP_RECOGNITIONCREDIT_ADDDEFAULTSFORMGSPLITS @MGSPLITS, @ORIGINALDONORID, @ORIGINALDONATIONDATE, @CLAIMDEFAULTCREDITTYPEID, @CHANGEAGENTID, @CURRENTDATE;
                end

                if (select count(*) from dbo.MATCHINGGIFTPREFERENCEINFO
                    where MGCLAIMADDRECOGNITIONTYPECODE = 1) > 0
                begin
                    exec dbo.USP_RECOGNITIONCREDIT_ADDTOGROUPMEMBERS @MGSPLITS, @ORIGINALDONORID, @ORIGINALDONATIONDATE, @ORIGINALGIFTID, @CLAIMDEFAULTCREDITTYPEID, @CHANGEAGENTID, @CURRENTDATE, 1;
                end
            end
        end

        declare @GIFTISANONYMOUS bit = 0;
        select @GIFTISANONYMOUS = GIVENANONYMOUSLY from dbo.REVENUE_EXT where ID = @ID
        if @AUTOADDMGCLAIMCREDITFORORG = 1 and (@GIFTISANONYMOUS = 0 or @DEFAULTANONYMOUSRECOGNITION = 1)
            exec dbo.USP_RECOGNITIONCREDIT_ADDDEFAULTSFORMGSPLITS @MGSPLITS, @MATCHINGORGANIZATIONID, @DATE, @CLAIMDEFAULTCREDITTYPEIDFORORG, @CHANGEAGENTID, @CURRENTDATE;

    declare @MATCHINGFACTOR decimal(5, 2) = 0
    select
        @MATCHINGFACTOR = MATCHINGFACTOR
    from
        dbo.MATCHINGGIFTCONDITION
    where
        MATCHINGGIFTCONDITION.ID = @MATCHINGGIFTCONDITIONID

        insert into dbo.REVENUESOLICITOR
        (
            ID,
            REVENUESPLITID,
            CONSTITUENTID,
            AMOUNT,
            BUSINESSUNITCODEID,
            BASECURRENCYID,
            ORGANIZATIONAMOUNT,
            ORGANIZATIONEXCHANGERATEID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            newid(),
            MGSPLITS.ID,
            CONSTITUENTID,
            MGSPLITS.AMOUNT,
            BUSINESSUNITCODEID,
            REVENUESOLICITOR.BASECURRENCYID,
            MGSPLITS.ORGANIZATIONAMOUNT,
            REVENUESOLICITOR.ORGANIZATIONEXCHANGERATEID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from
            dbo.REVENUESOLICITOR
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = REVENUESOLICITOR.REVENUESPLITID
            inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = LI.ID
            inner join dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@MGSPLITS) as MGSPLITS on RSE.DESIGNATIONID = MGSPLITS.DESIGNATIONID
        where
            LI.FINANCIALTRANSACTIONID = @ORIGINALGIFTID
        group by
            MGSPLITS.ID,
            MGSPLITS.DESIGNATIONID,
      MGSPLITS.AMOUNT,
      MGSPLITS.ORGANIZATIONAMOUNT,
            REVENUESOLICITOR.CONSTITUENTID,
            BUSINESSUNITCODEID,
            REVENUESOLICITOR.BASECURRENCYID,
            REVENUESOLICITOR.ORGANIZATIONEXCHANGERATEID


    insert into dbo.REVENUECATEGORY(ID, GLREVENUECATEGORYMAPPINGID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select 
            SPLITS.ID, REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from
            dbo.FINANCIALTRANSACTIONLINEITEM
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            inner join dbo.REVENUECATEGORY on REVENUESPLIT_EXT.ID = REVENUECATEGORY.ID
            inner join dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@MGSPLITS) as SPLITS on REVENUESPLIT_EXT.DESIGNATIONID = SPLITS.DESIGNATIONID and REVENUESPLIT_EXT.APPLICATIONCODE = SPLITS.APPLICATIONCODE
        where
            FINANCIALTRANSACTIONLINEITEM.DELETEDON is NULL and FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ORIGINALGIFTID

        declare CUR_MGCAMPAIGNS cursor local fast_forward 
        for 
            with UNIQUEORIGINALGIFTSPLITDESIGNATION
            as
            (
                select
                    --Because of the group by and having clauses there wil be only

                    --one ID per designation returned and the aggregator does not matter

                    cast(max(cast(REVENUESPLIT_EXT.ID as binary(16))) as uniqueidentifier) as ID,

                    REVENUESPLIT_EXT.DESIGNATIONID
                from 
                    dbo.FINANCIALTRANSACTIONLINEITEM 
                    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                where 
                    FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ORIGINALGIFTID
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
                group by
                    REVENUESPLIT_EXT.DESIGNATIONID
                having
                    count(*) = 1
            )
            select 
                FINANCIALTRANSACTIONLINEITEM.ID MGSPLITID,
                case
                    when UNIQUEORIGINALGIFTSPLITDESIGNATION.DESIGNATIONID is null then(
                            select CAMPAIGNID,CAMPAIGNSUBPRIORITYID 
                            from dbo.UFN_DESIGNATION_GETCAMPAIGNSTODEFAULT(REVENUESPLIT_EXT.DESIGNATIONID, @DATE) 
                            for xml raw('ITEM'),type,elements,root('CAMPAIGNS'),BINARY BASE64
                        )
                    else(
                        select CAMPAIGNID,CAMPAIGNSUBPRIORITYID 
                        from dbo.UFN_REVENUESPLIT_GETCAMPAIGNS(UNIQUEORIGINALGIFTSPLITDESIGNATION.ID) 
                        for xml raw('ITEM'),type,elements,root('CAMPAIGNS'),BINARY BASE64
                    )
                end
            from 
                dbo.FINANCIALTRANSACTIONLINEITEM 
                inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                left join UNIQUEORIGINALGIFTSPLITDESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = UNIQUEORIGINALGIFTSPLITDESIGNATION.DESIGNATIONID
            where 
                FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
                and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1;

        open CUR_MGCAMPAIGNS
        declare @SPLITID uniqueidentifier
        declare @CAMPAIGNS xml

        fetch next from CUR_MGCAMPAIGNS into @SPLITID, @CAMPAIGNS
        while @@fetch_status <> -1
        begin
            if @@fetch_status <> -2
            begin
                exec dbo.USP_REVENUESPLIT_GETCAMPAIGNS_ADDFROMXML @SPLITID, @CAMPAIGNS, @CHANGEAGENTID;
            end

            fetch next from CUR_MGCAMPAIGNS into @SPLITID, @CAMPAIGNS
        end

        close CUR_MGCAMPAIGNS
        deallocate CUR_MGCAMPAIGNS
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;