USP_DONORCHALLENGE_ADDREVENUE

This stored procedure is used by the donor challenge to create a the match per gift revenue record
Parameters

Parameter	Parameter Type	Mode
@REVENUEID	uniqueidentifier	INOUT
@REVENUESPLITID	uniqueidentifier	INOUT
@SPONSORID	uniqueidentifier	IN
@DESIGNATIONID	uniqueidentifier	IN
@AMOUNT	money	IN
@DATE	datetime	IN
@CHANGEAGENTID	uniqueidentifier	IN
@DATECHANGED	datetime	IN
@CURRENCYID	uniqueidentifier	IN
Definition

Copy


CREATE procedure dbo.USP_DONORCHALLENGE_ADDREVENUE
(
    @REVENUEID uniqueidentifier output,
    @REVENUESPLITID uniqueidentifier output,
    @SPONSORID uniqueidentifier,
    @DESIGNATIONID uniqueidentifier,
    @AMOUNT money,
    @DATE datetime,
    @CHANGEAGENTID uniqueidentifier,
    @DATECHANGED datetime,
  @CURRENCYID uniqueidentifier = null
)
as
    set nocount on

    begin try

        if @DATECHANGED is null 
          set @DATECHANGED = getdate();

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

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

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

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

      declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
      declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
      declare @BASEEXCHANGERATEID uniqueidentifier;
      declare @BASEAMOUNT money;
      declare @ORGANIZATIONAMOUNT money;

      exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT, 
        @DATE, 
        @CURRENCYID, 
        @BASEEXCHANGERATEID output,
        @CURRENCYID,
        @BASEAMOUNT output,
        @ORGANIZATIONCURRENCYID,
        @ORGANIZATIONAMOUNT output,
        @ORGANIZATIONEXCHANGERATEID output;

        --Add Revenue

        insert into dbo.FINANCIALTRANSACTION (
        ID, 
        CONSTITUENTID, 
        DATE, 
        POSTSTATUSCODE, 
        POSTDATE, 
        BASEAMOUNT, 
        TYPECODE, 
        TRANSACTIONCURRENCYID,
        TRANSACTIONAMOUNT,
        ORGAMOUNT,
        ORGEXCHANGERATEID,
        BASEEXCHANGERATEID,
        PDACCOUNTSYSTEMID,
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED
      ) values (
        @REVENUEID, 
        @SPONSORID, 
        @DATE, 
        3, 
        null, 
        @AMOUNT, 
        8, 
        @CURRENCYID,
        @AMOUNT,
        @ORGANIZATIONAMOUNT,
        @ORGANIZATIONEXCHANGERATEID,
        @BASEEXCHANGERATEID,
        '4B121C2C-CCE6-440D-894C-EA0DEF80D50B',
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @DATECHANGED,
        @DATECHANGED
      );

      insert into dbo.REVENUE_EXT (
        ID,
        DONOTRECEIPT,
        RECEIPTAMOUNT,
        NONPOSTABLEBASECURRENCYID,
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED
      ) values (
        @REVENUEID,
        1,
        0,
        @CURRENCYID,
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @DATECHANGED,
        @DATECHANGED
      );

        --Add origination source

        exec dbo.USP_REVENUE_ADDORIGIN @REVENUEID, @SPONSORID, @CHANGEAGENTID, @DATECHANGED;

        --Add application

        insert into dbo.REVENUESPLIT(
        ID, 
        REVENUEID, 
        TYPECODE, 
        APPLICATIONCODE, 
        AMOUNT, 
        DESIGNATIONID, 
        BASECURRENCYID,
        TRANSACTIONCURRENCYID,
        TRANSACTIONAMOUNT,
        ORGANIZATIONAMOUNT,
        ORGANIZATIONEXCHANGERATEID,
        BASEEXCHANGERATEID,
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED
      ) values (
        @REVENUESPLITID, 
        @REVENUEID, 
        0, 
        0, 
        @AMOUNT, 
        @DESIGNATIONID, 
        @CURRENCYID,
        @CURRENCYID,
        @AMOUNT,
        @ORGANIZATIONAMOUNT,
        @ORGANIZATIONEXCHANGERATEID,
        @BASEEXCHANGERATEID,
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @DATECHANGED, 
        @DATECHANGED
      )

      exec dbo.USP_REVENUE_ADDCAMPAIGNS @REVENUEID, @CHANGEAGENTID, @DATECHANGED;

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

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

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

        exec dbo.USP_PLEDGE_GENERATEINSTALLMENTSPLITS @REVENUEID, @CHANGEAGENTID, @DATECHANGED;

        --Add default Recognition Credits

        exec dbo.USP_REVENUEDETAIL_CREATERECOGNITIONS @REVENUESPLITID, @CHANGEAGENTID, @DATECHANGED, null;

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