USP_DATAFORMTEMPLATE_EDIT_REVENUESPLITDETAILSADJUST_2
The save procedure used by the edit dataform template "Revenue Split Details Adjust Form 2".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @DESIGNATIONID | uniqueidentifier | IN | Designation |
| @CATEGORYCODEID | uniqueidentifier | IN | Revenue category |
| @OTHERTYPECODEID | uniqueidentifier | IN | Other type |
| @OPPORTUNITYID | uniqueidentifier | IN | Opportunity |
| @ADJUSTMENTPOSTDATE | datetime | IN | Adjustment post date |
| @ADJUSTMENTDATE | datetime | IN | Adjustment date |
| @ADJUSTMENTREASON | nvarchar(100) | IN | Adjustment reason |
| @DECLINESGIFTAID | bit | IN | Constituent declines Gift Aid for this application |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUESPLITDETAILSADJUST_2
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@DESIGNATIONID uniqueidentifier,
@CATEGORYCODEID uniqueidentifier,
@OTHERTYPECODEID uniqueidentifier,
@OPPORTUNITYID uniqueidentifier,
@ADJUSTMENTPOSTDATE datetime,
@ADJUSTMENTDATE datetime,
@ADJUSTMENTREASON nvarchar(100),
@DECLINESGIFTAID bit
)
as
set nocount on
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
set @ADJUSTMENTREASON = coalesce(@ADJUSTMENTREASON, '')
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
-- Validate that the all the values passed in apply for the application code
declare @REVENUEID uniqueidentifier, @APPLICATIONCODE tinyint
select
@REVENUEID = REVENUEID,
@APPLICATIONCODE = APPLICATIONCODE
from dbo.REVENUESPLIT
where ID = @ID
if @DESIGNATIONID is null and
@APPLICATIONCODE = 0 -- Donation
begin
raiserror('DESIGNATIONREQUIRED', 13, 1)
return 1
end
if @OTHERTYPECODEID is not null and @APPLICATIONCODE <> 4
begin
raiserror('OTHERTYPECODENOTVALID', 13, 1)
return 1
end
if @OTHERTYPECODEID is null and @APPLICATIONCODE = 4
begin
raiserror('OTHERTYPECODEREQUIRED', 13, 1)
return 1
end
if @OPPORTUNITYID is not null and @APPLICATIONCODE <> 0
begin
raiserror('OPPORTUNITYCODENOTVALID', 13, 1)
return 1
end
-- Verify the transaction has already been posted
if not exists (select 1 from dbo.REVENUEPOSTED where ID = @REVENUEID)
begin
raiserror('TRANSACTIONMUSTBEPOSTED', 13, 1)
return 1
end
--check to see if designation changed, if so re-receipt, re-acknowledge and adjust (if posted)
declare @CURRENTDESIGNATIONID uniqueidentifier, @CURRENTCATEGORYID uniqueidentifier
select @CURRENTDESIGNATIONID = DESIGNATIONID from dbo.REVENUESPLIT where ID = @ID
select @CURRENTCATEGORYID = GLREVENUECATEGORYMAPPINGID from dbo.REVENUECATEGORY where ID = @ID
declare @DESIGNATIONCHANGED bit, @CATEGORYCHANGED bit, @ALREADYADJUSTED bit
if (@CURRENTDESIGNATIONID is null and @DESIGNATIONID is null) or @CURRENTDESIGNATIONID = @DESIGNATIONID
set @DESIGNATIONCHANGED = 0
else
set @DESIGNATIONCHANGED = 1
if (@CURRENTCATEGORYID is null and @CATEGORYCODEID is null) or @CURRENTCATEGORYID = @CATEGORYCODEID
set @CATEGORYCHANGED = 0
else
set @CATEGORYCHANGED = 1
if exists (select 1 from dbo.ADJUSTMENT where REVENUEID = @REVENUEID and POSTSTATUSCODE = 1)
set @ALREADYADJUSTED = 1
else
set @ALREADYADJUSTED = 0
declare @ADJUST bit
if (@DESIGNATIONCHANGED = 1 or @CATEGORYCHANGED = 1 or @ALREADYADJUSTED = 1) and
exists (select 1 from dbo.REVENUEPOSTED where REVENUEPOSTED.ID = @REVENUEID)
begin
set @ADJUST = 1
-- Verify the adjustment dates are set
if @ADJUSTMENTPOSTDATE is null
begin
raiserror('ADJUSTMENTPOSTDATEREQUIRED', 13, 1)
return 1
end
if @ADJUSTMENTDATE is null
begin
raiserror('ADJUSTMENTDATEREQUIRED', 13, 1)
return 1
end
end
else
set @ADJUST = 0
begin try
declare @ADJUSTMENTID uniqueidentifier;
declare @STOCKSALEADJUSTMENTIDS xml;
declare @PROPERTYDETAILADJUSTMENTID uniqueidentifier;
declare @PROPERTYDETAILCOUNT int;
declare @PAYMENTMETHODCODE int;
declare @REVENUEPAYMENTMETHODID uniqueidentifier;
declare @OLDGIFTAIDQUALIFICATIONSTATUS nvarchar(30);
set @OLDGIFTAIDQUALIFICATIONSTATUS = dbo.UFN_GIFTAIDREVENUESPLIT_GETQUALIFICATIONSTATUS(@ID);
set @PROPERTYDETAILCOUNT = 0;
if @ADJUST = 1
begin
select
@PAYMENTMETHODCODE = PAYMENTMETHODCODE,
@REVENUEPAYMENTMETHODID = ID
from
dbo.REVENUEPAYMENTMETHOD
where
REVENUEID = @REVENUEID;
-- Save the revenue adjustment
exec dbo.USP_SAVE_ADJUSTMENT @REVENUEID, @ADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON
select @PROPERTYDETAILCOUNT = count(PROPERTYDETAIL.ID)
from dbo.PROPERTYDETAIL
where PROPERTYDETAIL.ID = @REVENUEPAYMENTMETHODID and SALEPOSTSTATUSCODE = 0;
/* If sold stock has been posted, log stock detail adjustment */
if @PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID)
begin
exec dbo.USP_SAVE_STOCKDETAILADJUSTMENT @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @STOCKSALEADJUSTMENTIDS output;
end
/* If sold property has been posted, log property detail adjustment */
else if (@PAYMENTMETHODCODE = 5) and (@PROPERTYDETAILCOUNT > 0)
begin
exec dbo.USP_SAVE_PROPERTYDETAILADJUSTMENT @REVENUEPAYMENTMETHODID, @PROPERTYDETAILADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON;
end
end
if @DESIGNATIONCHANGED = 1
begin
exec dbo.USP_REVENUE_UPDATERERECEIPTS @REVENUEID, @CHANGEAGENTID, @CURRENTDATE
exec dbo.USP_REVENUELETTER_SETREACKNOWLEDGEMENTS @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
end
/* CMC
update dbo.REVENUESPLIT_EXT set
DESIGNATIONID = @DESIGNATIONID
where
ID = @ID
update dbo.FINANCIALTRANSACTION set
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID
*/
update dbo.REVENUESPLIT set
DESIGNATIONID = @DESIGNATIONID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID
if @CATEGORYCODEID is null
exec dbo.USP_REVENUECATEGORY_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID
else
begin
update dbo.REVENUECATEGORY set
GLREVENUECATEGORYMAPPINGID = @CATEGORYCODEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID
if @@ROWCOUNT = 0
insert into dbo.REVENUECATEGORY (ID, GLREVENUECATEGORYMAPPINGID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (@ID, @CATEGORYCODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
end
if @OTHERTYPECODEID is null
exec dbo.USP_REVENUESPLITOTHER_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID
else
begin
update dbo.REVENUESPLITOTHER set
OTHERTYPECODEID = @OTHERTYPECODEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID
if @@ROWCOUNT = 0
insert into dbo.REVENUESPLITOTHER (ID, OTHERTYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (@ID, @OTHERTYPECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
end
if @OPPORTUNITYID is null
exec dbo.USP_REVENUEOPPORTUNITY_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID
else
begin
update dbo.REVENUEOPPORTUNITY set
OPPORTUNITYID = @OPPORTUNITYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID
if @@ROWCOUNT = 0
insert into dbo.REVENUEOPPORTUNITY (ID, OPPORTUNITYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (@ID, @OPPORTUNITYID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
declare @REVENUEDATE datetime
select @REVENUEDATE = DATE from dbo.REVENUE where ID = @REVENUEID
exec dbo.USP_OPPORTUNITY_UPDATEACCEPTEDSTATUS @OPPORTUNITYID, @REVENUEDATE, @CHANGEAGENTID, @CURRENTDATE
end
exec dbo.USP_REVENUESPLIT_UPDATEDECLINESGIFTAID @REVENUEID = @REVENUEID,
@APPLICATIONCODE = @APPLICATIONCODE,
@REVENUESPLITID = @ID,
@DESIGNATIONID = @DESIGNATIONID,
@DECLINESGIFTAID = @DECLINESGIFTAID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTDATE = @CURRENTDATE
-- Clear the user-defined gl distributions if the designation or revenue category has changed
-- Do this regardless of whether gift is posted or not
if @DESIGNATIONCHANGED = 1 or @CATEGORYCHANGED = 1
begin
declare @contextCache varbinary(128);
--cache current context information
set @contextCache = CONTEXT_INFO();
-- Clear GL
delete from dbo.REVENUEGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0;
delete from dbo.STOCKSALEGLDISTRIBUTION where STOCKSALEGLDISTRIBUTION.REVENUEID = @REVENUEID and OUTDATED = 0;
delete from dbo.PROPERTYDETAILGLDISTRIBUTION where PROPERTYDETAILGLDISTRIBUTION.REVENUEID = @REVENUEID and OUTDATED = 0;
set CONTEXT_INFO @CHANGEAGENTID;
--reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
-- Add new GL distributions
declare @DONOTPOST bit
select @DONOTPOST = DONOTPOST from dbo.REVENUE where ID = @REVENUEID
if @DONOTPOST = 0
begin
-- Add new GL distributions
exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
-- Add new stock detail GL distributions
exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
-- Add new property detail GL distributions
exec dbo.USP_SAVE_PROPERTYDETAILGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
end
--Replace gift aid GL distributions
if (@OLDGIFTAIDQUALIFICATIONSTATUS = dbo.UFN_GIFTAIDREVENUESPLIT_GETQUALIFICATIONSTATUS(@ID))
and (exists(select 1 from dbo.GIFTAIDGLDISTRIBUTION where REVENUESPLITGIFTAIDID = @ID))
begin
exec dbo.USP_REVENUESPLITGIFTAID_UPDATEGLDISTRIBUTIONS @ID, @CHANGEAGENTID, @CURRENTDATE, @DONOTPOST;
end
end
/* add adjustment history information */
if @ADJUST = 1
begin
if @ADJUSTMENTID is not null
exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY @REVENUEID, @CHANGEAGENTID, null, @ADJUSTMENTID;
if @STOCKSALEADJUSTMENTIDS is not null
exec dbo.USP_ADJUSTMENTHISTORY_STOCK_SAVEHISTORY @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, null, @STOCKSALEADJUSTMENTIDS;
if @PROPERTYDETAILADJUSTMENTID is not null
exec dbo.USP_ADJUSTMENTHISTORY_PROPERTY_SAVEHISTORY @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, null, @PROPERTYDETAILADJUSTMENTID;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;