USP_EVENT_ADDPAYMENT_1_1
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @APPLICATIONID | uniqueidentifier | IN | |
| @APPLIEDAMOUNT | money | IN | |
| @CREATIONDATE | datetime | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @APPLIEDBASEAMOUNT | money | IN | |
| @APPLIEDORGANIZATIONAMOUNT | money | IN | |
| @CATEGORYCODEID | uniqueidentifier | IN | |
| @BUSINESSUNITSAPPLIED | bit | IN | |
| @EVENTCAMPAIGNS | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_EVENT_ADDPAYMENT_1_1
(
@REVENUEID uniqueidentifier,
@APPLICATIONID uniqueidentifier,
@APPLIEDAMOUNT money,
@CREATIONDATE datetime = null,
@CHANGEAGENTID uniqueidentifier = null,
@APPLIEDBASEAMOUNT money = null,
@APPLIEDORGANIZATIONAMOUNT money = null,
@CATEGORYCODEID uniqueidentifier = null,
@BUSINESSUNITSAPPLIED bit = 0,
@EVENTCAMPAIGNS xml =null
)
as
set nocount on
declare @EVENTID uniqueidentifier = null;
declare @APPLICATIONCURRENCYID uniqueidentifier;
declare @APPLICATIONEXCHANGERATEID uniqueidentifier;
declare @AMOUNTCONVERTED money;
declare @PAYMENTTRANSACTIONCURRENCYID uniqueidentifier;
declare @PAYMENTBASECURRENCYID uniqueidentifier;
declare @PAYMENTBASEEXCHANGERATEID uniqueidentifier;
declare @PAYMENTORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
declare @REVENUEDATE datetime;
declare @POSTDATE date;
declare @POSTSTATUSCODE tinyint;
declare @REVENUESPLITID uniqueidentifier
declare @DESIGNATIONID uniqueidentifier;
declare @CAMPAIGNS xml;
declare @EVENTREVENUESPLITID uniqueidentifier;
-- pick up eventid from registrant table
select
@EVENTID = REGISTRANT.EVENTID,
@APPLICATIONCURRENCYID = EVENT.BASECURRENCYID
from dbo.REGISTRANT
inner join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
where REGISTRANT.ID = @APPLICATIONID;
-- get currency values from the event registration payment
select
@PAYMENTTRANSACTIONCURRENCYID = FT.TRANSACTIONCURRENCYID,
@PAYMENTBASECURRENCYID = V.BASECURRENCYID,
@PAYMENTBASEEXCHANGERATEID = FT.BASEEXCHANGERATEID,
@PAYMENTORGANIZATIONEXCHANGERATEID = FT.ORGEXCHANGERATEID,
@REVENUEDATE = cast(FT.DATE as datetime),
@POSTDATE = FT.POSTDATE,
@POSTSTATUSCODE = case FT.POSTSTATUSCODE when 2 then 1 else FT.POSTSTATUSCODE end
from dbo.FINANCIALTRANSACTION FT
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V on V.FINANCIALTRANSACTIONID = FT.ID
where FT.ID = @REVENUEID;
-- if the event currency does not equal the payment transaction currency, get an exchange rate from payment transaction currency to event currency
if @APPLICATIONCURRENCYID <> @PAYMENTTRANSACTIONCURRENCYID
begin
if @APPLICATIONCURRENCYID = @PAYMENTBASECURRENCYID
set @APPLICATIONEXCHANGERATEID = @PAYMENTBASEEXCHANGERATEID;
else
set @APPLICATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@PAYMENTTRANSACTIONCURRENCYID,@APPLICATIONCURRENCYID,@REVENUEDATE,1,null);
-- no exchange rate exists - error
if @APPLICATIONEXCHANGERATEID is null
begin
raiserror('BBERR_APPLICATIONEXCHANGERATEDOESNOTEXIST : You cannot apply the payment toward this application. No exchange rate exists between the payment and application currencies.', 13, 1);
return 1;
end
end
--If the payment is not in the same currency as the event registration, convert the payment amount to the event reg currency
if @APPLICATIONCURRENCYID = @PAYMENTTRANSACTIONCURRENCYID
set @AMOUNTCONVERTED = @APPLIEDAMOUNT;
else
set @AMOUNTCONVERTED = dbo.UFN_CURRENCY_CONVERT(@APPLIEDAMOUNT, @APPLICATIONEXCHANGERATEID);
--If the converted amount is greater than the balance of the event registration in the event's currency, raise an error
if (@AMOUNTCONVERTED > dbo.UFN_EVENTREGISTRANT_GETBALANCE(@APPLICATIONID))
raiserror('BBERR_OVERPAIDCOMMITMENT', 13, 1);
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CREATIONDATE is null
set @CREATIONDATE = getdate();
set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
-- Convert the applied amount into base and organization amounts.
if @APPLIEDBASEAMOUNT is null or @APPLIEDORGANIZATIONAMOUNT is null
exec dbo.USP_CURRENCY_GETCURRENCYVALUES
@APPLIEDAMOUNT,
@REVENUEDATE,
@PAYMENTBASECURRENCYID,
@PAYMENTBASEEXCHANGERATEID output,
@PAYMENTTRANSACTIONCURRENCYID output,
@APPLIEDBASEAMOUNT output,
null,
@APPLIEDORGANIZATIONAMOUNT output,
@PAYMENTORGANIZATIONEXCHANGERATEID output,
0;
declare @SPLITSTABLE table
(
REVENUESPLITID uniqueidentifier,
DESIGNATIONID uniqueidentifier,
TRANSACTIONAMOUNT money,
EVENTBASEAMOUNT money,
BASEAMOUNT money,
ORGANIZATIONAMOUNT money,
CAMPAIGNS xml
);
declare @ISEVENTWITHDESIGNATIONS bit = 0;
-- check if this application is towards an event registration that allows designations on fees, and registrant has designations set up.
if exists
(
select 1 from dbo.REGISTRANT
join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
join dbo.REGISTRANTDESIGNATION on REGISTRANTDESIGNATION.REGISTRANTID = REGISTRANT.ID
where
REGISTRANT.ID = @APPLICATIONID
and EVENT.DESIGNATIONSONFEES = 1
and REGISTRANTDESIGNATION.AMOUNT > 0
) and exists
(
select 1
from dbo.REGISTRANTDESIGNATION
where REGISTRANTID = @APPLICATIONID and AMOUNT > 0
)
begin
if @APPLICATIONCURRENCYID <> @PAYMENTTRANSACTIONCURRENCYID
begin
raiserror('BBERR_PAYMENTCURRENCYDOESNOTMATCHEVENTDESIGNATIONCURRENCY', 13, 1);
return 1;
end
set @ISEVENTWITHDESIGNATIONS = 1;
declare @EVENTPORTIONBALANCE money;
declare @EVENTPRICE money;
declare @REGISTRANTREGISTRATION money;
declare @BENEFITVALUE money;
select
distinct @EVENTPRICE = EVENTPRICE.amount,
@BENEFITVALUE = isnull(REGISTRANTBENEFIT.unitvalue, 0)
from
dbo.REGISTRANTREGISTRATION
inner join dbo.EVENTPRICE on REGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
inner join dbo.EVENT on EVENTPRICE.EVENTID = EVENT.ID
left join REGISTRANTBENEFIT on REGISTRANTBENEFIT.REGISTRANTID = REGISTRANTREGISTRATION.REGISTRANTID
where
REGISTRANTREGISTRATION.REGISTRANTID = @APPLICATIONID;
select
@REGISTRANTREGISTRATION = sum(REGISTRANTREGISTRATION.AMOUNT)
from
dbo.REGISTRANTREGISTRATION
inner join dbo.EVENTPRICE on EVENTPRICE.ID = REGISTRANTREGISTRATION.EVENTPRICEID
where
REGISTRANTREGISTRATION.REGISTRANTID = @APPLICATIONID
and (
REGISTRANTREGISTRATION.AMOUNT > 0
or EVENTPRICE.AMOUNT = 0
);
-- checking payment amount is in multiple of event price,just to check is partial paymentor fully payment
if(@EVENTPRICE <>0 and @APPLIEDAMOUNT % @EVENTPRICE = 0 and @APPLIEDAMOUNT <> @REGISTRANTREGISTRATION and @BENEFITVALUE <> 0)
set
@EVENTPORTIONBALANCE = @BENEFITVALUE *(@APPLIEDAMOUNT / @EVENTPRICE);
else
set
@EVENTPORTIONBALANCE = dbo.UFN_EVENTREGISTRANT_GETBALANCEEVENTPORTION(@APPLICATIONID);
declare @EVENTPORTIONAMOUNTAPPLIED money;
declare @REMAININGAMOUNTTOAPPLY money;
declare @DESIGNATIONSTOPRORATE xml;
--Apply as much as possible towards the event portion before applying towards designations.
if @AMOUNTCONVERTED >= @EVENTPORTIONBALANCE
begin
set @EVENTPORTIONAMOUNTAPPLIED = @EVENTPORTIONBALANCE;
set @REMAININGAMOUNTTOAPPLY = (@AMOUNTCONVERTED - @EVENTPORTIONBALANCE);
end
else
begin
set @EVENTPORTIONAMOUNTAPPLIED = @AMOUNTCONVERTED;
set @REMAININGAMOUNTTOAPPLY = 0;
end
declare @TOTALDESIGNATIONBALANCE as money;
select @TOTALDESIGNATIONBALANCE = sum(coalesce(BALANCE,0))
from dbo.UFN_EVENTREGISTRANT_GETBALANCEDESIGNATIONPORTION(@APPLICATIONID);
set @DESIGNATIONSTOPRORATE =
(
select
BALANCE as AMOUNT,
DESIGNATIONID as ID
from dbo.UFN_EVENTREGISTRANT_GETBALANCEDESIGNATIONPORTION(@APPLICATIONID)
where BALANCE > 0
order by BALANCE
for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),BINARY BASE64
);
declare @SPLITS xml =
(
select
d.DESIGNATIONID,
d.AMOUNT
from
(
select
null as DESIGNATIONID,
@EVENTPORTIONAMOUNTAPPLIED as AMOUNT
union all
select
ID as DESIGNATIONID,
AMOUNT
from dbo.UFN_SPLITS_PRORATEAMOUNTS(@TOTALDESIGNATIONBALANCE ,@REMAININGAMOUNTTOAPPLY,(select DECIMALDIGITS from dbo.UFN_CURRENCY_GETPROPERTIES(@APPLICATIONCURRENCYID)),@DESIGNATIONSTOPRORATE)) as d
for xml raw('ITEM'), type, elements, root('ITEMLIST'), BINARY BASE64
);
-- split the total transaction across the event base amounts by proportion
declare @TRANSACTIONS xml =
(
select
ITEM.value('(ITEM/DESIGNATIONID)[1]', 'uniqueidentifier') as DESIGNATIONID,
ITEM.value('(ITEM/AMOUNT)[1]', 'money') as EVENTBASEAMOUNT,
BASEAMOUNT as AMOUNT
from dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTIONINXML(
@SPLITS,
@APPLICATIONCURRENCYID, -- event base
@PAYMENTTRANSACTIONCURRENCYID, -- transaction
null, -- org
@AMOUNTCONVERTED, -- event base amount
@APPLIEDAMOUNT, -- full transaction amount
(select DECIMALDIGITS from dbo.UFN_CURRENCY_GETPROPERTIES(@APPLICATIONCURRENCYID)),
null, -- full org amount
0)
for xml raw('ITEM'), type, elements, root('ITEMLIST'), BINARY BASE64
);
insert into @SPLITSTABLE
(
REVENUESPLITID,
DESIGNATIONID,
TRANSACTIONAMOUNT,
EVENTBASEAMOUNT,
BASEAMOUNT,
ORGANIZATIONAMOUNT,
CAMPAIGNS
)
select
newid(),
ITEM.value('(ITEM/DESIGNATIONID)[1]', 'uniqueidentifier') as DESIGNATIONID,
ITEM.value('(ITEM/AMOUNT)[1]', 'money') as TRANSACTIONAMOUNT,
ITEM.value('(ITEM/EVENTBASEAMOUNT)[1]', 'money') as EVENTBASEAMOUNT,
c.BASEAMOUNT as BASEAMOUNT,
c.ORGANIZATIONAMOUNT as ORGANIZATIONAMOUNT,
(
select -- campaigns for designation
CAMPAIGNID,
CAMPAIGNSUBPRIORITYID
from dbo.UFN_DESIGNATION_GETCAMPAIGNSTODEFAULT(ITEM.value('(ITEM/DESIGNATIONID)[1]', 'uniqueidentifier'), getdate())
for xml raw('ITEM'), type, elements, root('CAMPAIGNS'), BINARY BASE64
) as CAMPAIGNS
from dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTIONINXML(
@TRANSACTIONS,
@PAYMENTTRANSACTIONCURRENCYID,
@PAYMENTBASECURRENCYID,
@ORGANIZATIONCURRENCYID,
@APPLIEDAMOUNT,
@APPLIEDBASEAMOUNT,
(select DECIMALDIGITS from dbo.UFN_CURRENCY_GETPROPERTIES(@PAYMENTBASECURRENCYID)),
@APPLIEDORGANIZATIONAMOUNT,
(select DECIMALDIGITS from dbo.UFN_CURRENCY_GETPROPERTIES(@ORGANIZATIONCURRENCYID))) c;
delete from @SPLITSTABLE where TRANSACTIONAMOUNT = 0;
select @EVENTREVENUESPLITID = REVENUESPLITID
from @SPLITSTABLE
where DESIGNATIONID is null;
end -- registrant designations
else
begin -- no registrant designations; just plain event registration
insert into @SPLITSTABLE
(
REVENUESPLITID,
DESIGNATIONID,
TRANSACTIONAMOUNT,
BASEAMOUNT,
ORGANIZATIONAMOUNT,
EVENTBASEAMOUNT,
CAMPAIGNS
)
values
(
newid(),
null,
@APPLIEDAMOUNT,
@APPLIEDBASEAMOUNT,
@APPLIEDORGANIZATIONAMOUNT,
@AMOUNTCONVERTED,
null
)
end
declare APPLICATIONSCURSOR cursor local fast_forward for
select
REVENUESPLITID,
DESIGNATIONID,
TRANSACTIONAMOUNT,
BASEAMOUNT,
ORGANIZATIONAMOUNT,
EVENTBASEAMOUNT,
CAMPAIGNS
from @SPLITSTABLE;
open APPLICATIONSCURSOR;
fetch next from APPLICATIONSCURSOR into @REVENUESPLITID, @DESIGNATIONID, @APPLIEDAMOUNT, @APPLIEDBASEAMOUNT, @APPLIEDORGANIZATIONAMOUNT, @AMOUNTCONVERTED, @CAMPAIGNS;
while @@FETCH_STATUS = 0
begin
-- SHL BBIS Bug 356778; When @AMOUNTCONVERTED is less than 0 then the constituent has overpaid (Which is also taken care of above) so we do not keep looping through transactions
if @AMOUNTCONVERTED >= 0
begin
declare @ADJUSTMENTPOSTSTATUSCODE tinyint;
declare @ADJUSTMENTPOSTDATE date;
declare @ADJUSTMENTID uniqueidentifier;
select top 1
@ADJUSTMENTPOSTSTATUSCODE = case A.POSTSTATUSCODE when 2 then 3 else 1 end,
@ADJUSTMENTPOSTDATE = A.POSTDATE,
@ADJUSTMENTID = ALI.ID
from dbo.ADJUSTMENT A
left join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT ALI on A.ID = ALI.ID
where A.REVENUEID = @REVENUEID
order by A.DATEADDED desc;
insert into dbo.FINANCIALTRANSACTIONLINEITEM
(
ID,
FINANCIALTRANSACTIONID,
TRANSACTIONAMOUNT,
VISIBLE,
DESCRIPTION,
SEQUENCE,
TYPECODE,
POSTDATE,
POSTSTATUSCODE,
BASEAMOUNT,
ORGAMOUNT,
FINANCIALTRANSACTIONLINEITEMADJUSTMENTID,
SOURCELINEITEMID
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(
@REVENUESPLITID,
@REVENUEID,
@APPLIEDAMOUNT,
1,
'',
1,
0,
isnull(@ADJUSTMENTPOSTDATE, @POSTDATE),
isnull(@ADJUSTMENTPOSTSTATUSCODE, @POSTSTATUSCODE),
@APPLIEDBASEAMOUNT,
@APPLIEDORGANIZATIONAMOUNT,
@ADJUSTMENTID,
case
when @DESIGNATIONID is null then null
else @EVENTREVENUESPLITID
end,
@CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE
);
declare @eventIsActive bit
Exec @eventIsActive=dbo.UFN_CHECK_EVENTISACTIVE @DESIGNATIONID
if @eventIsActive=0
begin
raiserror('Data could not be saved. Revenue cannot be added to inactive designations', 13, 1);
end
merge dbo.REVENUESPLIT_EXT as target
using (select @REVENUESPLITID [ID]) as source
on (source.ID = target.ID)
when matched then
update set
DESIGNATIONID = @DESIGNATIONID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CREATIONDATE
when not matched then
insert
(
ID,
DESIGNATIONID,
TYPECODE,
APPLICATIONCODE,
OVERRIDEBUSINESSUNITS,
REVENUESPLITBUSINESSUNITOVERRIDECODEID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@REVENUESPLITID,
@DESIGNATIONID,
case when @DESIGNATIONID is null then 1 else 0 end,
1,
0,
null,
@CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE
);
if @DESIGNATIONID is not null and @CAMPAIGNS is not null
exec dbo.USP_REVENUESPLIT_GETCAMPAIGNS_ADDFROMXML @REVENUESPLITID, @CAMPAIGNS, @CHANGEAGENTID;
declare @RECEIPTAMOUNT money = null;
declare @RACOUNT int;
declare @PAYMENTCOUNT int;
select
@PAYMENTCOUNT = count(*),
@RACOUNT = count(all RECEIPTAMOUNT)
from dbo.EVENTREGISTRANTPAYMENT
where REGISTRANTID = @APPLICATIONID;
-- until recent changes, receipt amount was not recorded in eventregistrantpayment.
-- only calculate it if receipt amount is recorded for all prior payments
if @RACOUNT = @PAYMENTCOUNT
begin
declare @PERCENTOFREMAINING float = 1;
declare @AMOUNTREMAINING money;
declare @TOTALAMOUNT money;
declare @TOTALRECEIPTAMOUNT money;
declare @PRIORRECEIPTAMOUNT money;
select
@TOTALRECEIPTAMOUNT = isnull(sum(RECEIPTAMOUNT), 0),
@TOTALAMOUNT = isnull(sum(AMOUNT), 0)
from dbo.REGISTRANTREGISTRATION
where REGISTRANTID = @APPLICATIONID;
if @PAYMENTCOUNT = 0
begin
set @AMOUNTREMAINING = @TOTALAMOUNT;
set @PRIORRECEIPTAMOUNT = 0;
end
else
begin
declare @REFUNDEDPAYMENTS table (ID uniqueidentifier);
insert into @REFUNDEDPAYMENTS
select EVENTREGISTRANTPAYMENT.ID
from
(
select distinct FT.ID as CREDITID
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = LI.FINANCIALTRANSACTIONID
inner join dbo.SALESORDERITEMEVENTREGISTRATION as ER on ER.ID = EXT.SALESORDERITEMID
where
ER.REGISTRANTID = @APPLICATIONID
and FT.TYPECODE = 23 -- Refund
) as C
cross apply dbo.UFN_REFUND_GETPRORATEDSPLITS(C.CREDITID) as SPLITS
inner join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = SPLITS.REVENUESPLITID;
select @PRIORRECEIPTAMOUNT = isnull(sum(RECEIPTAMOUNT), 0)
from dbo.EVENTREGISTRANTPAYMENT
where REGISTRANTID = @APPLICATIONID and ID not in (select ID from @REFUNDEDPAYMENTS);
set @AMOUNTREMAINING = @TOTALAMOUNT -
(
select isnull(sum(EVENTREGISTRANTPAYMENT.AMOUNT), 0)
from dbo.EVENTREGISTRANTPAYMENT
where REGISTRANTID = @APPLICATIONID and ID not in (select ID from @REFUNDEDPAYMENTS)
);
end
-- Uses this receipt amount when event cost is zero and total receipt amount is equal to registration fee only
if @AMOUNTREMAINING = @TOTALRECEIPTAMOUNT - @PRIORRECEIPTAMOUNT
set @RECEIPTAMOUNT = @AMOUNTCONVERTED;
else
begin
if @AMOUNTREMAINING > 0 and @AMOUNTCONVERTED <= @AMOUNTREMAINING
set @PERCENTOFREMAINING = @AMOUNTCONVERTED / @AMOUNTREMAINING;
set @RECEIPTAMOUNT = round((@TOTALRECEIPTAMOUNT - @PRIORRECEIPTAMOUNT) * @PERCENTOFREMAINING, 2);
end
if @RECEIPTAMOUNT > @AMOUNTCONVERTED or @RECEIPTAMOUNT < 0
set @RECEIPTAMOUNT = 0;
end
--If this is an event with designations and this split is towards the event portion, none of it is receiptable.
--If it is towards a designation portion, the entire split is receiptable.
if @ISEVENTWITHDESIGNATIONS = 1
begin
if @DESIGNATIONID is null
begin
set @RECEIPTAMOUNT = 0;
end
else
begin
set @RECEIPTAMOUNT = @AMOUNTCONVERTED;
end
end
insert into dbo.EVENTREGISTRANTPAYMENT
(
ID,
PAYMENTID,
REGISTRANTID,
AMOUNT,
RECEIPTAMOUNT,
APPLICATIONCURRENCYID,
APPLICATIONEXCHANGERATEID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
newid(),
@REVENUESPLITID,
@APPLICATIONID,
@AMOUNTCONVERTED,
@RECEIPTAMOUNT,
@APPLICATIONCURRENCYID,
@APPLICATIONEXCHANGERATEID,
@CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE
);
if(@EVENTCAMPAIGNS is not null)
begin
declare @CAMPAIGNTABLE as table
(
CAMPAIGNID uniqueidentifier ,
CAMPAIGNSUBPRIORITYID uniqueidentifier
)
insert into @CAMPAIGNTABLE(CampaignId,CAMPAIGNSUBPRIORITYID)
select
CAMP.C.value('CAMPAIGNID[1]', 'uniqueidentifier'),
CAMP.C.value('CAMPAIGNSUBPRIORITYID[1]', 'uniqueidentifier')
FROM @EVENTCAMPAIGNS.nodes('//CAMPAIGNS/ITEM') CAMP(C)
-- Populate revenuesplitcampaigns table with campaigns associated with this event.
insert into dbo.REVENUESPLITCAMPAIGN
(
REVENUESPLITID,
CAMPAIGNID,
CAMPAIGNSUBPRIORITYID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select distinct
@REVENUESPLITID,
CAMPAIGNTABLE.CAMPAIGNID,
CAMPAIGNTABLE.CAMPAIGNSUBPRIORITYID,
@CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE
from @CAMPAIGNTABLE as CAMPAIGNTABLE
inner join dbo.CAMPAIGN on CAMPAIGNTABLE.CAMPAIGNID = CAMPAIGN.ID
where
CAMPAIGN.ISACTIVE=1
and CAMPAIGN.ID not in (select CAMPAIGNID from dbo.REVENUESPLITCAMPAIGN where REVENUESPLITID = @REVENUESPLITID); -- not already added by Designation Campaigns above
end
else
begin
-- Populate revenuesplitcampaigns table with campaigns associated with this event.
insert into dbo.REVENUESPLITCAMPAIGN
(
REVENUESPLITID,
CAMPAIGNID,
CAMPAIGNSUBPRIORITYID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
@REVENUESPLITID,
CAMPAIGNID,
CAMPAIGNSUBPRIORITYID,
@CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE
from dbo.EVENTCAMPAIGN
inner join dbo.CAMPAIGN on EVENTCAMPAIGN.CAMPAIGNID = CAMPAIGN.ID
where
EVENTID = @EVENTID
and CAMPAIGN.ISACTIVE=1
and CAMPAIGN.ID not in (select CAMPAIGNID from dbo.REVENUESPLITCAMPAIGN where REVENUESPLITID = @REVENUESPLITID); -- not already added by Designation Campaigns above
end
-- create recognitions
exec dbo.USP_REVENUEDETAIL_CREATERECOGNITIONS @REVENUESPLITID, @CHANGEAGENTID, @CREATIONDATE;
-- Clear opportunity link
delete from dbo.REVENUEOPPORTUNITY where ID = @REVENUESPLITID;
-- Copy opportunity from source
insert into dbo.REVENUEOPPORTUNITY
(
ID,
OPPORTUNITYID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
@REVENUESPLITID,
EVENTREGISTRATIONOPPORTUNITY.OPPORTUNITYID,
@CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE
from dbo.EVENTREGISTRATIONOPPORTUNITY
where EVENTREGISTRATIONOPPORTUNITY.ID = @APPLICATIONID;
end
if @CATEGORYCODEID is not null
exec dbo.USP_REVENUECATEGORY_ADDEDIT @REVENUESPLITID, @CATEGORYCODEID, @CHANGEAGENTID;
fetch next from APPLICATIONSCURSOR into @REVENUESPLITID, @DESIGNATIONID, @APPLIEDAMOUNT, @APPLIEDBASEAMOUNT, @APPLIEDORGANIZATIONAMOUNT, @AMOUNTCONVERTED, @CAMPAIGNS;
end
close APPLICATIONSCURSOR;
deallocate APPLICATIONSCURSOR;
/* Apply business units */
if @BUSINESSUNITSAPPLIED = 0
exec dbo.USP_REVENUESPLIT_APPLYBUSINESSUNITS @REVENUEID, @CHANGEAGENTID, @CREATIONDATE;