USP_PLEDGE_CLEARANDADDCAMPAIGNS

CREATE procedure dbo.USP_PLEDGE_CLEARANDADDCAMPAIGNS
(
  @REVENUEID uniqueidentifier,
  @OPPORTUNITYID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @CHANGEDATE datetime
)
as
  set nocount on;

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

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

  begin try
    exec dbo.USP_REVENUE_CLEARCAMPAIGNS @REVENUEID, @CHANGEAGENTID;

    if @OPPORTUNITYID is not null
    begin
      -- Default in campaigns from opportunity

      insert into dbo.REVENUESPLITCAMPAIGN (REVENUESPLITID, CAMPAIGNID, CAMPAIGNSUBPRIORITYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select distinct
          REVENUESPLIT.ID,
          OPPORTUNITYDESIGNATIONCAMPAIGN.CAMPAIGNID,
          OPPORTUNITYDESIGNATIONCAMPAIGN.CAMPAIGNSUBPRIORITYID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CHANGEDATE,
          @CHANGEDATE
        from dbo.OPPORTUNITYDESIGNATION
          inner join dbo.OPPORTUNITYDESIGNATIONCAMPAIGN on OPPORTUNITYDESIGNATION.ID = OPPORTUNITYDESIGNATIONCAMPAIGN.OPPORTUNITYDESIGNATIONID
          inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = @REVENUEID and REVENUESPLIT.DESIGNATIONID = OPPORTUNITYDESIGNATION.DESIGNATIONID
          inner join dbo.CAMPAIGN on CAMPAIGN.ID=OPPORTUNITYDESIGNATIONCAMPAIGN.CAMPAIGNID
        where OPPORTUNITYDESIGNATION.OPPORTUNITYID = @OPPORTUNITYID
          and CAMPAIGN.ISACTIVE = 1;

      -- Insert any campaigns for designations that didn't default from the opportunity

      insert into dbo.REVENUESPLITCAMPAIGN (REVENUESPLITID, CAMPAIGNID, CAMPAIGNSUBPRIORITYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select distinct
          REVENUESPLIT.ID,
          DESIGNATIONCAMPAIGN.CAMPAIGNID,
          DESIGNATIONCAMPAIGN.CAMPAIGNSUBPRIORITYID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CHANGEDATE,
          @CHANGEDATE
        from dbo.REVENUE
          inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
          inner join dbo.DESIGNATIONCAMPAIGN on DESIGNATIONCAMPAIGN.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
            --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

            and (DESIGNATIONCAMPAIGN.DATEFROM is null or cast(DESIGNATIONCAMPAIGN.DATEFROM as date) <= REVENUE.DATE)
            --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

            and (DESIGNATIONCAMPAIGN.DATETO is null or dateadd(ms, -003, dateadd(d, 1, cast(cast(DESIGNATIONCAMPAIGN.DATETO as date) as datetime))) >= REVENUE.DATE)
          inner join dbo.CAMPAIGN on CAMPAIGN.ID=DESIGNATIONCAMPAIGN.CAMPAIGNID
        where 
          REVENUE.ID = @REVENUEID and
          REVENUESPLIT.DESIGNATIONID not in (select DESIGNATIONID from dbo.OPPORTUNITYDESIGNATION where OPPORTUNITYID = @OPPORTUNITYID)
          and CAMPAIGN.ISACTIVE = 1;
    end
    else
    begin
      exec dbo.USP_REVENUE_ADDCAMPAIGNS @REVENUEID, @CHANGEAGENTID, @CHANGEDATE;
    end
  end try

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

  return 0;