USP_MEMBERSHIP_ADDPAYMENT

CREATE procedure dbo.USP_MEMBERSHIP_ADDPAYMENT
(
  @REVENUEID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier,
  @MEMBERSHIPID uniqueidentifier output,
  @AMOUNT money,
  @TRANSACTIONDATE datetime,
  @MEMBERSHIPS xml,
  @CREATIONDATE datetime,
  @CHANGEAGENTID uniqueidentifier,
  @MEMBERSHIPTRANSACTIONID uniqueidentifier = null output,
  @BASEAMOUNT money = null,
  @ORGANIZATIONAMOUNT money = null
)
as
begin
  set nocount on;

  declare @ID uniqueidentifier;
  set @ID = newid();

  -- Get multicurrency values from the revenue.

  declare @TRANSACTIONCURRENCYID uniqueidentifier;
  declare @BASECURRENCYID uniqueidentifier;
  declare @BASEEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
  declare @DATE datetime;
    declare @POSTDATE date;
    declare @POSTSTATUSCODE tinyint;

  select
        @TRANSACTIONCURRENCYID = FT.TRANSACTIONCURRENCYID,
        @BASECURRENCYID = V.BASECURRENCYID,
        @BASEEXCHANGERATEID = FT.BASEEXCHANGERATEID,
        @ORGANIZATIONEXCHANGERATEID = FT.ORGEXCHANGERATEID,
        @DATE = cast(FT.DATE as datetime),
        @POSTDATE = FT.POSTDATE,
        @POSTSTATUSCODE = FT.POSTSTATUSCODE
    from dbo.FINANCIALTRANSACTION FT
    inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V on V.FINANCIALTRANSACTIONID = FT.ID
    where FT.ID = @REVENUEID;

  -- Convert the applied amount into base and organization amounts if it is not provided by the caller

  if @BASEAMOUNT is null or @ORGANIZATIONAMOUNT is null
    exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT, @DATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEAMOUNT output, null, @ORGANIZATIONAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 0;

  insert into dbo.FINANCIALTRANSACTIONLINEITEM 
        (ID
        ,FINANCIALTRANSACTIONID
        ,TRANSACTIONAMOUNT
        ,VISIBLE
        ,DESCRIPTION
        ,SEQUENCE
        ,TYPECODE
        ,POSTDATE
        ,POSTSTATUSCODE
        ,BASEAMOUNT
        ,ORGAMOUNT
        -- Boilerplate

        ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    values
        (@ID
        ,@REVENUEID
        ,@AMOUNT
        ,1
        ,''
        ,1
        ,0
        ,@POSTDATE
        ,@POSTSTATUSCODE
        ,@BASEAMOUNT
        ,@ORGANIZATIONAMOUNT
        ,@CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE)

    merge dbo.REVENUESPLIT_EXT as target
    using (select @ID [ID]) as source      
    on (source.ID = target.ID)      
        when matched then
    update set 
        DESIGNATIONID = null
        ,CHANGEDBYID = @CHANGEAGENTID
        ,DATECHANGED = @CREATIONDATE
    when not matched then
        insert 
        (
            ID
            ,DESIGNATIONID
            ,TYPECODE
            ,APPLICATIONCODE
            ,OVERRIDEBUSINESSUNITS
            ,REVENUESPLITBUSINESSUNITOVERRIDECODEID
            -- boilerplate

            ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        values 
        (    
            @ID
            ,null
            ,2
            ,5
            ,0
            ,null
            --  boilerplate

            ,@CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE);

  -- create recognitions

  exec dbo.USP_REVENUEDETAIL_CREATERECOGNITIONS @ID, @CHANGEAGENTID, @CREATIONDATE;

  declare @MEMBERSHIPLEVELID uniqueidentifier;
  declare @MEMBERSHIPLEVELTERMID uniqueidentifier;
  declare @MEMBERSHIPTYPECODEID uniqueidentifier;
  declare @NUMBEROFCHILDREN tinyint;
  declare @COMMENTS nvarchar(1000);
  declare @ISGIFT bit;
  declare @SENDRENEWALCODE tinyint;
  declare @EXPIRATIONDATE datetime;
  declare @MEMBERS xml;
  declare @GIVENBYID uniqueidentifier;

  select
    @MEMBERSHIPLEVELID = T.c.value('(MEMBERSHIPLEVELID)[1]','uniqueidentifier'),
    @MEMBERSHIPLEVELTERMID = T.c.value('(MEMBERSHIPLEVELTERMID)[1]','uniqueidentifier'),
    --Bug 81099 - AdamBu 3/23/10 - Depending on where the call into this SP comes from (Payment Add form vs. Revenue Batch),

    --    the following node's name is different.  Given that changing it in either place would be a breaking change and the

    --    large number of existing code paths that lead to this SP, we're forced to handle both possible names here.

    @MEMBERSHIPTYPECODEID = coalesce(T.c.value('(MEMBERSHIPTYPECODEID)[1]','uniqueidentifier'),T.c.value('(MEMBERSHIPLEVELTYPECODEID)[1]','uniqueidentifier')),
    @NUMBEROFCHILDREN = T.c.value('(NUMBEROFCHILDREN)[1]','tinyint'),
    @COMMENTS = T.c.value('(COMMENTS)[1]','nvarchar(1000)'),
    @ISGIFT = T.c.value('(ISGIFT)[1]','bit'),
    @SENDRENEWALCODE = T.c.value('(SENDRENEWALCODE)[1]','tinyint'),            
    @EXPIRATIONDATE = T.c.value('(EXPIRATIONDATE)[1]','datetime'),
    @MEMBERS = T.c.query('./MEMBERS'),
    @GIVENBYID = T.c.value('(GIVENBYID)[1]','uniqueidentifier')
  from
    @MEMBERSHIPS.nodes('/MEMBERSHIPFIELDS/ITEM') T(c)

  declare @BENEFITSWAIVED bit
  select @BENEFITSWAIVED = BENEFITSWAIVED from dbo.REVENUE where ID = @REVENUEID;

  if @BENEFITSWAIVED = 0 and @CONSTITUENTID is not null
  begin
    declare @NEWBENEFITS xml;
    set @NEWBENEFITS = dbo.UFN_REVENUE_GETBENEFITS2_TOITEMLISTXML(@REVENUEID);

    insert into dbo.BENEFITCONSTITUENTDECLINED
    (
      BENEFITID,
      CONSTITUENTID,
      ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    )
    select
      MLB.BENEFITID,
      @CONSTITUENTID,    
      @CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE    
    from
      dbo.MEMBERSHIPLEVELBENEFIT MLB
    where
      MLB.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID and 
      MLB.BENEFITID not in
      (
        select BCD.BENEFITID
        from dbo.BENEFITCONSTITUENTDECLINED BCD
        where BCD.CONSTITUENTID = @CONSTITUENTID
      ) and
      MLB.BENEFITID not in
      (
        select T.c.value('(BENEFITID)[1]','uniqueidentifier')
        from @NEWBENEFITS.nodes('/BENEFITS/ITEM') T(c)
      );

    -- If benefit is added constituent is no longer declining it

    delete from
      dbo.BENEFITCONSTITUENTDECLINED
    where
      CONSTITUENTID = @CONSTITUENTID
      and BENEFITID in
      (
        select T.c.value('(BENEFITID)[1]','uniqueidentifier')
        from @NEWBENEFITS.nodes('/BENEFITS/ITEM') T(c)
      )
  end

  exec dbo.USP_MEMBERSHIP_ADDFROMSALE
    @CONSTITUENTID,
    @TRANSACTIONDATE,
    @MEMBERSHIPID output,
    @MEMBERSHIPLEVELID,
    @MEMBERSHIPLEVELTERMID,
    @MEMBERSHIPTYPECODEID,
    @NUMBEROFCHILDREN,
    @COMMENTS,
    @ISGIFT,
    @SENDRENEWALCODE,
    @EXPIRATIONDATE,
    @MEMBERS,
    @GIVENBYID,
    @CHANGEAGENTID,
    @ID,
    @MEMBERSHIPTRANSACTIONID output;

  -- Insert campaigns into revenuesplitcampaign for this payment.

  insert into dbo.REVENUESPLITCAMPAIGN
  (
    REVENUESPLITID,
    CAMPAIGNID,
    CAMPAIGNSUBPRIORITYID,
    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
  )
  select
    @ID,
    -- Check override setting on this program/level. If on, use campaign on level if not use program.

    case ML.OVERRIDECAMPAIGNS when 1 then MLC.CAMPAIGNID else MPC.CAMPAIGNID end CAMPAIGNID,
    case ML.OVERRIDECAMPAIGNS when 1 then MLC.CAMPAIGNSUBPRIORITYID else MPC.CAMPAIGNSUBPRIORITYID end CAMPAIGNSUBPRIORITYID,
    @CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE
  from
    dbo.MEMBER M
  inner join
    dbo.MEMBERSHIP MS on M.MEMBERSHIPID = MS.ID
  inner join
    dbo.MEMBERSHIPLEVEL ML on ML.ID = MS.MEMBERSHIPLEVELID
  left outer join
    dbo.MEMBERSHIPPROGRAMCAMPAIGN MPC on MS.MEMBERSHIPPROGRAMID = MPC.MEMBERSHIPPROGRAMID
  left outer join
    dbo.MEMBERSHIPLEVELCAMPAIGN MLC on MS.MEMBERSHIPLEVELID = MLC.MEMBERSHIPLEVELID
  where
    MS.ID = @MEMBERSHIPID and
    MS.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID and
    (isnull(MS.LASTRENEWEDON,MS.JOINDATE) between MPC.DATEFROM and MPC.DATETO or (MPC.DATEFROM IS NULL and MPC.DATETO IS NULL)) and
    (isnull(MS.LASTRENEWEDON,MS.JOINDATE) between MLC.DATEFROM and MLC.DATETO or (MLC.DATEFROM IS NULL and MLC.DATETO IS NULL))    and
    (isnull(MLC.CAMPAIGNID,MPC.CAMPAIGNID) is not null) and
    (select CAMPAIGN.ISACTIVE from dbo.CAMPAIGN where CAMPAIGN.ID = (case ML.OVERRIDECAMPAIGNS when 1 then MLC.CAMPAIGNID else MPC.CAMPAIGNID end)) = 1;

  return 0;
end