USP_SPONSORSHIP_CREATECOMMITMENT

CREATE procedure dbo.USP_SPONSORSHIP_CREATECOMMITMENT
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @REVENUECONSTITUENTID uniqueidentifier = null,
    @SPONSORSHIPCONSTITUENTID uniqueidentifier = null,
    @REASSIGNEDFROMSPONSORSHIPID uniqueidentifier = null,
    @SPONSORSHIPREASONID uniqueidentifier = null,
    @SPONSORSHIPPROGRAMID uniqueidentifier = null,
    @SPONSORSHIPLOCATIONID uniqueidentifier = null,
    @GENDERCODE int = 0,
    @SPROPPAGERANGEID uniqueidentifier = null,
    @ISHIVPOSITIVECODE int = 0,
    @HASCONDITIONCODE int = 0,
    @ISORPHANEDCODE int = 0,
    @SPROPPPROJECTCATEGORYCODEID uniqueidentifier = null,
    @SPONSORSHIPOPPORTUNITYID uniqueidentifier = null,
    @ISSOLESPONSORSHIP bit = 0,
    @STARTDATE date = null,
    @AMOUNT money = 0,
    @PAYMENTMETHODCODE tinyint = 2,
    @REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
    @REFERENCENUMBER nvarchar(20) = '',
    @CARDHOLDERNAME nvarchar(255) = '',
    @CREDITCARDNUMBER nvarchar(20) = '',
    @CREDITTYPECODEID uniqueidentifier = null,
    @EXPIRESON dbo.UDT_FUZZYDATE = null,
    @CONSTITUENTACCOUNTID uniqueidentifier = null,
    @FREQUENCYCODE tinyint = 3,
    @REVENUESCHEDULEENDDATE datetime = null,
    @REVENUESCHEDULESTARTDATE datetime = null,
    @AUTOPAY bit = 1,
    @CREDITCARDTOKEN uniqueidentifier = null,
    @PLANNEDENDDATE date = null,
    @SENDREMINDER bit = 0,
    @FINDERNUMBER bigint = null,
    @SOURCECODE nvarchar(50) = null,
    @APPEALID uniqueidentifier = null,
    @MAILINGID uniqueidentifier = null,
    @CHANNELCODEID uniqueidentifier = null,
    @REFERENCE nvarchar(255) = null,
    @CATEGORYCODEID uniqueidentifier = null,
    @EXPIRATIONREASONID uniqueidentifier = null,
    @TRANSACTIONCURRENCYID uniqueidentifier = null,
    @BASEEXCHANGERATEID uniqueidentifier = null,
    @EXCHANGERATE decimal(20,8) = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @BATCHNUMBER nvarchar(100) = '',
    @DONOTACKNOWLEDGE bit = 0,
    @DATA xml = null,
    @BASECURRENCYID uniqueidentifier = null,
    @SEPAMANDATEID uniqueidentifier = null,
    @OTHERPAYMENTMETHODCODEID uniqueidentifier = null
)
as
begin
    set nocount on;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

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

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

    if @BATCHNUMBER is null
        set @BATCHNUMBER = '';

    begin try
        if @REVENUECONSTITUENTID = @SPONSORSHIPCONSTITUENTID
        begin
            --if we have a finder number make sure the constituent has been added to the tables

            if isnull(@FINDERNUMBER, 0) > 0
            begin
                exec dbo.USP_DATAFORMTEMPLATE_ADD_ACQUISITIONLISTMEMBER @SPONSORSHIPCONSTITUENTID, @CHANGEAGENTID, @FINDERNUMBER;
            end
            exec dbo.USP_SPONSOR_MAKEACTIVE @SPONSORSHIPCONSTITUENTID, @STARTDATE, 0, @CHANGEAGENTID
        end
        else
        begin
            exec dbo.USP_SPONSOR_MAKEACTIVE @SPONSORSHIPCONSTITUENTID, @STARTDATE, 1, @CHANGEAGENTID

            begin try
                exec dbo.USP_SPONSOR_MAKEACTIVE @REVENUECONSTITUENTID, @STARTDATE, 2, @CHANGEAGENTID
            end try
            begin catch
                raiserror('BBERR_INVALIDREVENUECONSTITUENTID',13,1);
            end catch
        end

        --------------------------------------------------------------

        -- Add affiliate constituency and relationship

        --------------------------------------------------------------

        declare @ISAFFILIATE bit
        declare @CONSTITUENCYCODEID uniqueidentifier
        declare @AFFILIATEID uniqueidentifier
        declare @RELATIONSHIPTYPECODEID uniqueidentifier
        declare @RECIPROCALTYPECODEID uniqueidentifier

        select
            @ISAFFILIATE = case when P.ID is not null then 1 else 0 end,
            @CONSTITUENCYCODEID = P.CONSTITUENCYCODEID,
            @AFFILIATEID = P.AFFILIATEID,
            @RELATIONSHIPTYPECODEID = S.AFFILIATERELATIONSHIPTYPECODEID,
            @RECIPROCALTYPECODEID = S.AFFILIATESPONSORRELATIONSHIPTYPECODEID
        from dbo.SPONSORSHIPINFO S
        inner join dbo.SPONSORSHIPAFFILIATEPROGRAM P on P.ID = @SPONSORSHIPPROGRAMID;

        if @ISAFFILIATE = 1
        begin
            -- constituency

            if @CONSTITUENCYCODEID is not null
            begin
                if not exists(select 'x' from dbo.CONSTITUENCY
                              where CONSTITUENTID = @SPONSORSHIPCONSTITUENTID
                              and CONSTITUENCYCODEID = @CONSTITUENCYCODEID)

                    exec dbo.USP_DATAFORMTEMPLATE_ADD_CONSTITUENCY
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @CONSTITUENTID = @SPONSORSHIPCONSTITUENTID,
                        @CONSTITUENCYCODEID = @CONSTITUENCYCODEID
            end

            -- relationship

            if @RELATIONSHIPTYPECODEID is not null
            begin
                if not exists(select 'x' from dbo.RELATIONSHIP
                              where    RELATIONSHIPCONSTITUENTID = @AFFILIATEID
                              and RECIPROCALCONSTITUENTID = @SPONSORSHIPCONSTITUENTID
                              and RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID
                              and RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID) and (@AFFILIATEID <> @SPONSORSHIPCONSTITUENTID)

                    exec dbo.USP_DATAFORMTEMPLATE_ADD_ORGTOIND_RELATIONSHIP
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @CONSTITUENTID = @AFFILIATEID,
                        @RECIPROCALCONSTITUENTID = @SPONSORSHIPCONSTITUENTID,
                        @RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID,
                        @RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID,
                        @STARTDATE = @STARTDATE
            end
        end

        --------------------------------------------------------------

        -- Acquire opportunity

        --------------------------------------------------------------


        if @SPONSORSHIPOPPORTUNITYID is null
          exec dbo.USP_SPONSORSHIP_ACQUIREOPPORTUNITY
              @SPONSORSHIPOPPORTUNITYID output,
              @CHANGEAGENTID,
              0,
              @SPONSORSHIPCONSTITUENTID,
              @SPONSORSHIPPROGRAMID,
              @SPONSORSHIPLOCATIONID,
              @GENDERCODE,
              @SPROPPAGERANGEID,
              @ISHIVPOSITIVECODE,
              @HASCONDITIONCODE,
              @ISORPHANEDCODE,
              @SPROPPPROJECTCATEGORYCODEID,
              @ISSOLESPONSORSHIP,
              null,
              null,
              @REVENUECONSTITUENTID

        --------------------------------------------------------------

        -- REVENUE

        --------------------------------------------------------------

        declare @DOREVENUE bit
        set @DOREVENUE = 1

        select @DOREVENUE = 0
        from dbo.SPONSORSHIPAFFILIATEPROGRAM
        where ID = @SPONSORSHIPPROGRAMID;

        if @DOREVENUE = 1
        begin
            declare @SPLITS xml;
            declare @REVENUESPLITID uniqueidentifier;
            declare @DESIGNATIONID uniqueidentifier;

            set @DESIGNATIONID = dbo.UFN_SPONSORSHIPOPPORTUNITY_GETDESIGNATION(@SPONSORSHIPOPPORTUNITYID)

            if @DESIGNATIONID is null
              raiserror('BBERR_NODESIGNATIONFOUND',13,1)

            set @REVENUESPLITID = newid()

            set @SPLITS = (
            select @REVENUESPLITID ID,
                   @DESIGNATIONID DESIGNATIONID,
                   @AMOUNT AMOUNT,
                   0 APPLICATIONCODE,
                   9 TYPECODE,
           @TRANSACTIONCURRENCYID TRANSACTIONCURRENCYID
            for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
            )

            if @REFERENCEDATE is null
              set @REFERENCEDATE = '00000000'

            declare @REVENUEID uniqueidentifier = @ID;

            exec dbo.USP_DATAFORMTEMPLATE_ADD_RECURRINGGIFT 
                @REVENUEID output,
                @CHANGEAGENTID,
                @REVENUECONSTITUENTID,                    
                @STARTDATE,
                @AMOUNT,
                @PAYMENTMETHODCODE,
                @REFERENCEDATE,
                @REFERENCENUMBER,
                @CARDHOLDERNAME,
                @CREDITCARDNUMBER,
                @CREDITTYPECODEID,
                @EXPIRESON,
                @CONSTITUENTACCOUNTID,
                @SPLITS,
                @FREQUENCYCODE,
                @REVENUESCHEDULEENDDATE,
                @REVENUESCHEDULESTARTDATE,
                @FINDERNUMBER,
                @SOURCECODE,
                @APPEALID,
                0,
                @MAILINGID,
                @CHANNELCODEID,
                @DONOTACKNOWLEDGE,
                @AUTOPAY,
                @REFERENCE,
                @CATEGORYCODEID,
                @CREDITCARDTOKEN,
                0, --@STANDINGORDERSETUP,

                null, --@STANDINGORDERSETUPDATE,

                null, --@DDISOURCECODEID,

                null, --@DDISOURCEDATE,

                @SENDREMINDER,
                @TRANSACTIONCURRENCYID,
                @BASEEXCHANGERATEID,
                @EXCHANGERATE,
                @CURRENTAPPUSERID,
                default, --BUSINESSUNITSAPPLIED

                default, --@GENERATEREFERENCENUMBER,

                default, --@STANDINGORDERREFERENCENUMBER,

                default, --EVENTID

                default, --LOCALCORPID

                default, --ISMEMBERSHIPRECURRINGGIFT

                default, --BATCHNUMBER

                @BASECURRENCYID,
                @SEPAMANDATEID,
                @OTHERPAYMENTMETHODCODEID;


            if len(@BATCHNUMBER) > 0
                update dbo.REVENUE 
                    set BATCHNUMBER = @BATCHNUMBER,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = DATEADDED
                where ID = @REVENUEID;

      declare @BBNCPAGENAME nvarchar(100);
      declare @BBNCPAGEID int;

      with xmlnamespaces('urn:blackbaud.RE7.XDATA' as bb)
      select @BBNCPAGENAME = T.c.value('bb:PageName[1]','nvarchar(100)'),
             @BBNCPAGEID = T.c.value('bb:PageID[1]','int')
        from @DATA.nodes('/SponsorshipTransactionData/OriginInformation') T(c)

      if isnull(@BBNCPAGENAME,'') <> '' and isnull(@BBNCPAGEID,0) <> 0
        insert into dbo.REVENUEBBNC(ID, NETCOMMUNITYPAGENAME, NETCOMMUNITYPAGEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                  values(@REVENUEID, @BBNCPAGENAME, @BBNCPAGEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
        end

        --------------------------------------------------------------

        -- SPONSORSHIPCOMMITMENT

        --------------------------------------------------------------

        declare @COMMITMENTID uniqueidentifier
        set @COMMITMENTID = newid()

        declare @COMMITMENTSEQUENCE smallint

        select @COMMITMENTSEQUENCE = isnull(max(COMMITMENTSEQUENCE),0)+1
        from dbo.SPONSORSHIPCOMMITMENT
        where CONSTITUENTID = @SPONSORSHIPCONSTITUENTID

        insert into dbo.SPONSORSHIPCOMMITMENT
        (
            ID,
            CONSTITUENTID,
            COMMITMENTSEQUENCE,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (
            @COMMITMENTID,
            @SPONSORSHIPCONSTITUENTID,
            @COMMITMENTSEQUENCE,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        )

        --------------------------------------------------------------

        -- SPONSORSHIP

        --------------------------------------------------------------

    insert into dbo.SPONSORSHIP
    (
      ID,
      SPONSORSHIPCOMMITMENTID,
      CONSTITUENTID,
      SPONSORSHIPPROGRAMID,
      SPONSORSHIPOPPORTUNITYID,
      STATUSCODE,
      STARTDATE,
      SPONSORSHIPLOCATIONID,
      CHILDGENDERCODE,
      SPONSORSHIPOPPORTUNITYAGERANGEID,
      ISHIVPOSITIVECODE,
      HASCONDITIONCODE,
      ISORPHANEDCODE,
      SPROPPPROJECTCATEGORYCODEID,
      ISSOLESPONSORSHIP,
      REVENUESPLITID,
      PLANNEDENDDATE,
      EXPIRATIONREASONID,
      ISMOSTRECENTFORCOMMITMENT,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    )
    values
    (
      @ID,
      @COMMITMENTID,
      @SPONSORSHIPCONSTITUENTID,
      @SPONSORSHIPPROGRAMID,
      @SPONSORSHIPOPPORTUNITYID,
      1,
      @STARTDATE,
      @SPONSORSHIPLOCATIONID,
      @GENDERCODE,
      @SPROPPAGERANGEID,
      @ISHIVPOSITIVECODE,
      @HASCONDITIONCODE,
      @ISORPHANEDCODE,
      @SPROPPPROJECTCATEGORYCODEID,
      @ISSOLESPONSORSHIP,
      @REVENUESPLITID,
      @PLANNEDENDDATE,
      @EXPIRATIONREASONID,
      1,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    )

        --------------------------------------------------------------

        -- SPONSORSHIPTRANSACTION

        --------------------------------------------------------------

        insert into dbo.SPONSORSHIPTRANSACTION
        (
            ID,
            SPONSORSHIPCOMMITMENTID,
            TRANSACTIONSEQUENCE,
            ACTIONCODE,
            SPONSORSHIPREASONID,
            CONTEXTSPONSORSHIPID,
            TARGETSPONSORSHIPID,
            TRANSACTIONDATE,
            GIFTFINANCIALSPONSORID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (
            newid(),
            @COMMITMENTID,
            1,
            case when @REASSIGNEDFROMSPONSORSHIPID is null then 0 else 5 end,
            @SPONSORSHIPREASONID,
            @REASSIGNEDFROMSPONSORSHIPID,
            @ID,
            @STARTDATE,
            case when @REVENUECONSTITUENTID <> @SPONSORSHIPCONSTITUENTID then @REVENUECONSTITUENTID end,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        )

        --------------------------------------------------------------

        -- Update and unlock opportunity.

        --------------------------------------------------------------

    if @REASSIGNEDFROMSPONSORSHIPID is null
      exec dbo.USP_SPONSORSHIPOPPORTUNITY_SPONSORSHIPADDED
        @SPONSORSHIPOPPORTUNITYID,
        @ISSOLESPONSORSHIP,
        @CHANGEAGENTID,
        @CURRENTDATE,
        1

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

  return 0
end