USP_SALESORDER_ADDSPONSORSHIPS

CREATE procedure dbo.USP_SALESORDER_ADDSPONSORSHIPS (
    @SALESORDERID uniqueidentifier,
    @TRANSACTIONDATE date,
    @CONSTITUENTID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @CURRENTDATE datetime
)
as
begin
    declare SPONSORSHIPSCUR cursor local fast_forward for
        select
            SOIS.ID,
            SOIS.SPONSORSHIPPROGRAMID,
            SOIS.SPONSORSHIPOPPORTUNITYID,
            SOIS.STARTDATE,
            isnull(SOIS.GIFTRECIPIENTID,@CONSTITUENTID),
            SOIS.SPONSORSHIPLOCATIONID,
            SOIS.GENDERCODE,
            SOIS.SPONSORSHIPOPPORTUNITYAGERANGEID,
            SOIS.ISHIVPOSITIVECODE,
            SOIS.HASCONDITIONCODE,
            SOIS.ISORPHANEDCODE,
            SOIS.SPROPPPROJECTCATEGORYCODEID,
            SOIS.ISSOLESPONSORSHIP,
            SOIS.EXPIRATIONDATE,
            SOIS.EXPIRATIONREASONID,
            SOIRS.AMOUNT,
            SOIRS.FREQUENCYCODE,
            SOIRS.SCHEDULESTARTDATE,
            SOIRS.PAYMENTMETHODCODE,
            SOIRS.REFERENCEDATE,
            SOIRS.REFERENCENUMBER,
            SOIRS.CARDHOLDERNAME,
            SOIRS.CREDITCARDPARTIALNUMBER,
            SOIRS.CREDITTYPECODEID,
            SOIRS.EXPIRESON,
            SOIRS.CREDITCARDTOKEN,
            SOIRS.CONSTITUENTACCOUNTID,
            SOIRS.AUTOPAY,
            SOI.DATA,
            SOIS.CURRENCYISO,
            SOIS.PDACCOUNTSYSTEMID,
            SOIS.APPEALID
        from dbo.SALESORDERITEMSPONSORSHIP as SOIS
        inner join dbo.SALESORDERITEM SOI on SOI.ID = SOIS.ID
        inner join dbo.SALESORDERITEMREVENUESCHEDULE as SOIRS on SOIRS.ID = SOIS.ID
        where SOI.SALESORDERID = @SALESORDERID;

    declare @SALESORDERITEMSPONSORSHIPID uniqueidentifier;
    declare @SPONSORSHIPPROGRAMID uniqueidentifier;
    declare @SPONSORSHIPOPPORTUNITYID uniqueidentifier;
    declare @STARTDATE date;
    declare @CORRESPONDINGSPONSORID uniqueidentifier;
    declare @SPONSORSHIPLOCATIONID uniqueidentifier;
    declare @GENDERCODE tinyint;
    declare @SPONSORSHIPOPPORTUNITYAGERANGEID uniqueidentifier;
    declare @ISHIVPOSITIVECODE tinyint;
    declare @HASCONDITIONCODE tinyint;
    declare @ISORPHANEDCODE tinyint;
    declare @SPROPPPROJECTCATEGORYCODEID uniqueidentifier;
    declare @ISSOLESPONSORSHIP bit;
    declare @EXPIRATIONDATE date;
    declare @EXPIRATIONREASONID uniqueidentifier;
    declare @AMOUNT money;
    declare @FREQUENCYCODE tinyint;
    declare @SCHEDULESTARTDATE date;
    declare @PAYMENTMETHODCODE tinyint;
    declare @REFERENCEDATE dbo.UDT_FUZZYDATE;
    declare @REFERENCENUMBER nvarchar(20);
    declare @CARDHOLDERNAME nvarchar(255);
    declare @CREDITCARDNUMBER nvarchar(20);
    declare @CREDITTYPECODEID uniqueidentifier;
    declare @EXPIRESON dbo.UDT_FUZZYDATE;
    declare @CREDITCARDTOKEN uniqueidentifier;
    declare @CONSTITUENTACCOUNTID uniqueidentifier;
    declare @AUTOPAY bit;
    declare @DATA xml;
    declare @CURRENCYISO nvarchar(3);
    declare @PDACCOUNTSYSTEMID uniqueidentifier;
    declare @APPEALID uniqueidentifier;

  --Sponsorship should carry the channel code defined in the web transactions configuration (WI#154664)

  declare @CHANNELCODEID uniqueidentifier;
  select top 1 @CHANNELCODEID = CHANNELCODEID from dbo.NETCOMMUNITYDEFAULTCODEMAP;

    open SPONSORSHIPSCUR;
    fetch next from SPONSORSHIPSCUR into
        @SALESORDERITEMSPONSORSHIPID,
        @SPONSORSHIPPROGRAMID,
        @SPONSORSHIPOPPORTUNITYID,
        @STARTDATE,
        @CORRESPONDINGSPONSORID,
        @SPONSORSHIPLOCATIONID,
        @GENDERCODE,
        @SPONSORSHIPOPPORTUNITYAGERANGEID,
        @ISHIVPOSITIVECODE,
        @HASCONDITIONCODE,
        @ISORPHANEDCODE,
        @SPROPPPROJECTCATEGORYCODEID,
        @ISSOLESPONSORSHIP,
        @EXPIRATIONDATE,
        @EXPIRATIONREASONID,
        @AMOUNT,
        @FREQUENCYCODE,
        @SCHEDULESTARTDATE,
        @PAYMENTMETHODCODE,
        @REFERENCEDATE,
        @REFERENCENUMBER,
        @CARDHOLDERNAME,
        @CREDITCARDNUMBER,
        @CREDITTYPECODEID,
        @EXPIRESON,
        @CREDITCARDTOKEN,
        @CONSTITUENTACCOUNTID,
        @AUTOPAY,
        @DATA,
        @CURRENCYISO,
        @PDACCOUNTSYSTEMID,
        @APPEALID;

    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    declare @BASECURRENCYID uniqueidentifier;
    declare @BASEEXCHANGERATEID uniqueidentifier;
    declare @EXCHANGERATE decimal(20,8);

    while @@FETCH_STATUS = 0
    begin
        declare @ID uniqueidentifier;

        set @ID = null; --reset for multiple sponsorships

        set @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETCURRENCYFROMISO(@CURRENCYISO);

        select @BASECURRENCYID = cs.BASECURRENCYID 
        from PDACCOUNTSYSTEM pdas
        inner join CURRENCYSET cs on cs.ID = pdas.CURRENCYSETID
        where pdas.ID = @PDACCOUNTSYSTEMID;

        set @BASEEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID, @BASECURRENCYID, @CURRENTDATE, 1, null);

        exec dbo.USP_SPONSORSHIP_CREATECOMMITMENT
            @ID output,
            @CHANGEAGENTID,
            @CONSTITUENTID,
            @CORRESPONDINGSPONSORID,
            null, --@REASSIGNEDFROMSPONSORSHIPID,

            null, --@SPONSORSHIPREASONID,

            @SPONSORSHIPPROGRAMID,
            @SPONSORSHIPLOCATIONID,
            @GENDERCODE,
            @SPONSORSHIPOPPORTUNITYAGERANGEID,
            @ISHIVPOSITIVECODE,
            @HASCONDITIONCODE,
            @ISORPHANEDCODE,
            @SPROPPPROJECTCATEGORYCODEID,
            @SPONSORSHIPOPPORTUNITYID,
            @ISSOLESPONSORSHIP,
            @STARTDATE,
            @AMOUNT,
            @PAYMENTMETHODCODE,
            @REFERENCEDATE,
            @REFERENCENUMBER,
            @CARDHOLDERNAME,
            @CREDITCARDNUMBER,
            @CREDITTYPECODEID,
            @EXPIRESON,
            @CONSTITUENTACCOUNTID,
            @FREQUENCYCODE,
            null, --@REVENUESCHEDULEENDDATE,

            @SCHEDULESTARTDATE,
            @AUTOPAY,
            @CREDITCARDTOKEN,
            @EXPIRATIONDATE,
            1, --@SENDREMINDER,

            null, --@FINDERNUMBER,

            '', --@SOURCECODE,

            @APPEALID, --@APPEALID,

            null, --@MAILINGID,

            @CHANNELCODEID,
            null, --@REFERENCE,

            null, --@CATEGORYCODEID,

            @EXPIRATIONREASONID,
            @TRANSACTIONCURRENCYID,
            @BASEEXCHANGERATEID, 
            null, --@EXCHANGERATE,

            null, --@CURRENTAPPUSERID,

            '', --@BATCHNUMBER,

            0, --@DONOTACKNOWLEDGE,

            @DATA,
            @BASECURRENCYID,
            default --SEPAMANDATEID


        -- customized program should be here.  @ID is the sponsorship ID.

        declare @CUSTOMSP nvarchar(500);
        select @CUSTOMSP = ADDSPONSORSHIPCUSTOMPROCEDURE 
        from dbo.SPONSORSHIPINFO
        if  @CUSTOMSP is not null and len(@CUSTOMSP)>0
        begin
            set @CUSTOMSP = @CUSTOMSP + '  ''' + CONVERT(NVARCHAR(36),@ID) + ''''
            EXEC sp_executesql @CUSTOMSP
        end


        -- write the sponsorship ID back to salesorderitemsponsorship

        -- so that the payment can be applied to it

        update dbo.SALESORDERITEMSPONSORSHIP
        set SPONSORSHIPID = @ID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @SALESORDERITEMSPONSORSHIPID;

        fetch next from SPONSORSHIPSCUR into
            @SALESORDERITEMSPONSORSHIPID,
            @SPONSORSHIPPROGRAMID,
            @SPONSORSHIPOPPORTUNITYID,
            @STARTDATE,
            @CORRESPONDINGSPONSORID,
            @SPONSORSHIPLOCATIONID,
            @GENDERCODE,
            @SPONSORSHIPOPPORTUNITYAGERANGEID,
            @ISHIVPOSITIVECODE,
            @HASCONDITIONCODE,
            @ISORPHANEDCODE,
            @SPROPPPROJECTCATEGORYCODEID,
            @ISSOLESPONSORSHIP,
            @EXPIRATIONDATE,
            @EXPIRATIONREASONID,
            @AMOUNT,
            @FREQUENCYCODE,
            @SCHEDULESTARTDATE,
            @PAYMENTMETHODCODE,
            @REFERENCEDATE,
            @REFERENCENUMBER,
            @CARDHOLDERNAME,
            @CREDITCARDNUMBER,
            @CREDITTYPECODEID,
            @EXPIRESON,
            @CREDITCARDTOKEN,
            @CONSTITUENTACCOUNTID,
            @AUTOPAY,
            @DATA,
            @CURRENCYISO,
            @PDACCOUNTSYSTEMID,
            @APPEALID;
    end

    close SPONSORSHIPSCUR;
    deallocate SPONSORSHIPSCUR;
end