USP_RECONCILEMATCHINGGIFT_GENERATEANDPAYCLAIM

Creates and pays a matching gift claim when reconciling matching gift claims.
Parameters

Parameter	Parameter Type	Mode
@INSTALLMENTSPLITPAYMENTID	uniqueidentifier	INOUT
@REVENUESPLITID	uniqueidentifier	IN
@APPLICATIONID	uniqueidentifier	IN
@CONSTITUENTID	uniqueidentifier	IN
@APPLICATIONDESIGNATIONID	uniqueidentifier	IN
@APPLIEDAMOUNT	money	IN
@MGCLAIMINSTALLMENTSPLITID	uniqueidentifier	IN
@DATE	datetime	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CREATIONDATE	datetime	IN
Definition

Copy


            CREATE procedure dbo.USP_RECONCILEMATCHINGGIFT_GENERATEANDPAYCLAIM
            (
                @INSTALLMENTSPLITPAYMENTID uniqueidentifier output,
                @REVENUESPLITID uniqueidentifier,
                @APPLICATIONID uniqueidentifier,
                @CONSTITUENTID uniqueidentifier,
                @APPLICATIONDESIGNATIONID uniqueidentifier,
                @APPLIEDAMOUNT money,
                @MGCLAIMINSTALLMENTSPLITID uniqueidentifier,
                @DATE datetime,
                @CHANGEAGENTID uniqueidentifier,
                @CREATIONDATE datetime
            )
            as
                set nocount on

                declare @MGMAILINGID uniqueidentifier;
                declare @MGAPPEALID uniqueidentifier;
                declare @MGSOURCECODE nvarchar(50);
                declare @MGSOURCEREVENUEID uniqueidentifier;
                select
                    @MGMAILINGID = REVENUE.MAILINGID,
                    @MGAPPEALID = REVENUE.APPEALID,
                    @MGSOURCECODE = REVENUE.SOURCECODE,
                    @MGSOURCEREVENUEID = REVENUE.ID
                from dbo.REVENUESPLIT
                inner join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
                where REVENUESPLIT.ID = @APPLICATIONID;

        declare @TRANSACTIONCURRENCYID uniqueidentifier;
        declare @BASECURRENCYID uniqueidentifier;
        declare @BASEEXCHANGERATEID uniqueidentifier;
        declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
        select
          @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
          @BASECURRENCYID = BASECURRENCYID,
          @BASEEXCHANGERATEID = BASEEXCHANGERATEID,
          @ORGANIZATIONEXCHANGERATEID = ORGANIZATIONEXCHANGERATEID
        from dbo.REVENUESPLIT
        where ID = @REVENUESPLITID;

        declare @BASEAMOUNT money;
        declare @ORGANIZATIONAMOUNT money;
        exec dbo.USP_CURRENCY_GETCURRENCYVALUES
          @AMOUNT = @APPLIEDAMOUNT,
          @DATE = null,
          @BASECURRENCYID = @BASECURRENCYID,
          @BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
          @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
          @BASEAMOUNT = @BASEAMOUNT output,
          @ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT output,
          @ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID

                declare @MGREVENUEID uniqueidentifier;
                set @MGREVENUEID = newid();
                insert into dbo.REVENUE (ID, CONSTITUENTID, DATE, DONOTPOST, POSTDATE, DONOTRECEIPT, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, AMOUNT, BASECURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, TRANSACTIONTYPECODE, RECEIPTAMOUNT, MAILINGID, APPEALID, SOURCECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values (@MGREVENUEID, @CONSTITUENTID, @DATE, 1, null, 1, @APPLIEDAMOUNT, @TRANSACTIONCURRENCYID, @BASEAMOUNT, @BASECURRENCYID, @BASEEXCHANGERATEID, @ORGANIZATIONAMOUNT, @ORGANIZATIONEXCHANGERATEID, 3, 0, @MGMAILINGID, @MGAPPEALID, @MGSOURCECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE);

                --Add origination source

                exec dbo.USP_REVENUE_ADDORIGIN @MGREVENUEID, @CONSTITUENTID, @CHANGEAGENTID, @CREATIONDATE;

                insert into dbo.REVENUESPLIT (REVENUEID, DESIGNATIONID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, AMOUNT, BASECURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values (@MGREVENUEID, @APPLICATIONDESIGNATIONID, @APPLIEDAMOUNT, @TRANSACTIONCURRENCYID, @BASEAMOUNT, @BASECURRENCYID, @BASEEXCHANGERATEID, @ORGANIZATIONAMOUNT, @ORGANIZATIONEXCHANGERATEID, @CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE);

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

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

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

                set @FREQUENCYCODE = 5; --Single Installment

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

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

                declare @INSTALLMENTID uniqueidentifier;
                set @INSTALLMENTID = newid();
                insert into dbo.INSTALLMENT (ID, REVENUEID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, AMOUNT, BASECURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, DATE, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values (@INSTALLMENTID, @MGREVENUEID, @APPLIEDAMOUNT, @TRANSACTIONCURRENCYID, @BASEAMOUNT, @BASECURRENCYID, @BASEEXCHANGERATEID, @ORGANIZATIONAMOUNT, @ORGANIZATIONEXCHANGERATEID, @DATE, @INSTALLMENTSEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE);

                set @MGCLAIMINSTALLMENTSPLITID = newid();
                insert into dbo.INSTALLMENTSPLIT (ID, INSTALLMENTID, PLEDGEID, DESIGNATIONID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, AMOUNT, BASECURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values (@MGCLAIMINSTALLMENTSPLITID, @INSTALLMENTID, @MGREVENUEID, @APPLICATIONDESIGNATIONID, @APPLIEDAMOUNT, @TRANSACTIONCURRENCYID, @BASEAMOUNT, @BASECURRENCYID, @BASEEXCHANGERATEID, @ORGANIZATIONAMOUNT, @ORGANIZATIONEXCHANGERATEID, @CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE);

                set @INSTALLMENTSPLITPAYMENTID = newid();
                insert into dbo.INSTALLMENTSPLITPAYMENT (ID, PAYMENTID, PLEDGEID, INSTALLMENTSPLITID, AMOUNT, APPLICATIONCURRENCYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values (@INSTALLMENTSPLITPAYMENTID, @REVENUESPLITID, @MGREVENUEID, @MGCLAIMINSTALLMENTSPLITID, @APPLIEDAMOUNT, @TRANSACTIONCURRENCYID, @CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE);