USP_RECORDOPERATION_RESERVATIONKEEPOVERAGE
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_RECORDOPERATION_RESERVATIONKEEPOVERAGE
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier
)
as
set nocount on;
declare @CURRENTDATE datetime = getdate();
declare @SALESORDERSTATUSCODE tinyint;
declare @SALESORDERTRANSACTIONID uniqueidentifier;
declare @SALESORDERCONSTITUENTID uniqueidentifier;
declare @SALESORDERTRANSACTIONDATE datetime;
declare @SALESORDERPOSTSTATUSCODE tinyint;
declare @RESERVATIONNAME nvarchar(100);
if @CHANGEAGENTID is null begin
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
end
select
@SALESORDERSTATUSCODE = SALESORDER.STATUSCODE,
@SALESORDERTRANSACTIONID = SALESORDER.REVENUEID,
@SALESORDERCONSTITUENTID = SALESORDER.CONSTITUENTID,
@SALESORDERTRANSACTIONDATE = SALESORDER.TRANSACTIONDATE,
@SALESORDERPOSTSTATUSCODE = coalesce(FINANCIALTRANSACTION.POSTSTATUSCODE, 1),
@RESERVATIONNAME = RESERVATION.NAME
from
dbo.SALESORDER
inner join
dbo.RESERVATION on RESERVATION.ID = SALESORDER.ID
left join
dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = SALESORDER.REVENUEID
where
SALESORDER.ID = @ID;
declare @LATESTADJUSTMENTRECORDID uniqueidentifier;
select top 1
@LATESTADJUSTMENTRECORDID = LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
from
dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join
dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT as LIADJUSTMENT on LIADJUSTMENT.ID = LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
where
LI.FINANCIALTRANSACTIONID = @SALESORDERTRANSACTIONID
and LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID is not null
and LI.DELETEDON is null
and LI.TYPECODE = 0 -- Standard
order by
LIADJUSTMENT.DATE desc;
begin try
if @SALESORDERSTATUSCODE is null begin
raiserror('BBERR_RESERVATIONKEEPOVERAGE_RESERVATIONNOTFOUND', 13, 1);
end
if @SALESORDERSTATUSCODE <> 1 begin -- Complete
raiserror('BBERR_RESERVATIONKEEPOVERAGE_RESERVATIONNOTCOMPLETED', 13, 1);
end
declare @OVERAGEPAYMENTS table (
ID uniqueidentifier,
OVERAGEAMOUNT money,
NEWOVERAGEKEPTLINEITEMID uniqueidentifier,
NEWJOURNALENTRYID uniqueidentifier
);
insert into @OVERAGEPAYMENTS (
ID,
OVERAGEAMOUNT,
NEWOVERAGEKEPTLINEITEMID,
NEWJOURNALENTRYID
)
select
SALESORDERPAYMENT.PAYMENTID,
AMOUNTS.OVERAGE,
newid(),
newid()
from
dbo.SALESORDERPAYMENT
outer apply
dbo.UFN_PAYMENT_AMOUNTS(SALESORDERPAYMENT.PAYMENTID) as AMOUNTS
where
SALESORDERPAYMENT.SALESORDERID = @ID
and AMOUNTS.OVERAGE > 0;
if @@rowcount = 0 begin
raiserror('BBERR_RESERVATIONKEEPOVERAGE_NOOVERAGEPAYMENTS', 13, 1);
end
declare @ALLOWGLDISTRIBUTIONS bit;
select @ALLOWGLDISTRIBUTIONS = ALLOWGLDISTRIBUTIONS from dbo.UFN_PDACCOUNTSYSTEM_DEFAULTORSYSTEM();
declare @POSTDATE date;
declare @POSTSTATUSCODE tinyint = 3; -- Do not post
set @POSTDATE = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate());
if @ALLOWGLDISTRIBUTIONS = 1
begin
set @POSTSTATUSCODE = 1; -- Not posted
end
declare @ORDEROVERAGELINEITEMID uniqueidentifier = newid();
declare @TOTALOVERAGEAMOUNT money;
select @TOTALOVERAGEAMOUNT = sum(OVERAGEAMOUNT)
from @OVERAGEPAYMENTS;
-- Financial Transactions aren't created for $0 orders so we will need to create one if one doesn't exist
if @SALESORDERTRANSACTIONID is null begin
set @SALESORDERTRANSACTIONID = newid();
exec dbo.USP_SALESORDER_ADDREVENUE @ID, @SALESORDERTRANSACTIONID, @SALESORDERTRANSACTIONDATE, @SALESORDERCONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;
end
-- Add a single overage line item to the order transaction
insert into dbo.FINANCIALTRANSACTIONLINEITEM (
ID,
FINANCIALTRANSACTIONID,
FINANCIALTRANSACTIONLINEITEMADJUSTMENTID,
TRANSACTIONAMOUNT,
BASEAMOUNT,
ORGAMOUNT,
DESCRIPTION,
POSTDATE,
POSTSTATUSCODE,
UNITVALUE,
DATEADDED,
DATECHANGED,
ADDEDBYID,
CHANGEDBYID
)
values (
@ORDEROVERAGELINEITEMID,
@SALESORDERTRANSACTIONID,
@LATESTADJUSTMENTRECORDID,
@TOTALOVERAGEAMOUNT,
@TOTALOVERAGEAMOUNT,
@TOTALOVERAGEAMOUNT,
@RESERVATIONNAME + ' - Overage',
@POSTDATE,
@POSTSTATUSCODE,
@TOTALOVERAGEAMOUNT,
@CURRENTDATE,
@CURRENTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID
);
insert into dbo.REVENUESPLIT_EXT (
ID,
TYPECODE,
APPLICATIONCODE,
DATEADDED,
DATECHANGED,
ADDEDBYID,
CHANGEDBYID
)
values (
@ORDEROVERAGELINEITEMID,
20, -- Overage
11, -- Miscellaneous
@CURRENTDATE,
@CURRENTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID
)
-- Add a "Do not post" line item to each payment containing an overage
-- with a source of the order overage line item
insert into dbo.FINANCIALTRANSACTIONLINEITEM (
ID,
FINANCIALTRANSACTIONID,
SOURCELINEITEMID,
TRANSACTIONAMOUNT,
BASEAMOUNT,
ORGAMOUNT,
DESCRIPTION,
POSTDATE,
POSTSTATUSCODE,
UNITVALUE,
VISIBLE,
DATEADDED,
DATECHANGED,
ADDEDBYID,
CHANGEDBYID
)
select
NEWOVERAGEKEPTLINEITEMID,
ID,
@ORDEROVERAGELINEITEMID,
OVERAGEAMOUNT,
OVERAGEAMOUNT,
OVERAGEAMOUNT,
@RESERVATIONNAME + ' - Overage',
@POSTDATE, --So we know when the overage was kept
3, -- Do not post
OVERAGEAMOUNT,
0,
@CURRENTDATE,
@CURRENTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID
from
@OVERAGEPAYMENTS;
insert into dbo.REVENUESPLIT_EXT (
ID,
TYPECODE,
APPLICATIONCODE,
DATEADDED,
DATECHANGED,
ADDEDBYID,
CHANGEDBYID
)
select
NEWOVERAGEKEPTLINEITEMID,
20, -- Overage
10, -- Order
@CURRENTDATE,
@CURRENTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID
from
@OVERAGEPAYMENTS;
if @ALLOWGLDISTRIBUTIONS = 1 begin
declare @JOURNAL nvarchar(50) = 'Blackbaud Enterprise';
declare @DISTRIBUTIONS table (
ACCOUNTID uniqueidentifier,
TRANSACTIONTYPECODE tinyint,
TRANSACTIONCURRENCYID uniqueidentifier,
REFERENCE nvarchar(255),
MAPPEDVALUES xml,
PROJECT nvarchar(100),
ACCOUNT nvarchar(100),
BASEEXCHANGERATEID uniqueidentifier,
ORGANIZATIONEXCHANGERATEID uniqueidentifier,
GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
ERRORMESSAGE nvarchar(max)
);
insert into @DISTRIBUTIONS (
ACCOUNTID,
TRANSACTIONTYPECODE,
TRANSACTIONCURRENCYID,
REFERENCE,
MAPPEDVALUES,
PROJECT,
ACCOUNT,
BASEEXCHANGERATEID,
ORGANIZATIONEXCHANGERATEID,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
ERRORMESSAGE
)
select
ACCOUNTID,
TRANSACTIONTYPECODE,
TRANSACTIONCURRENCYID,
REFERENCE,
MAPPEDVALUES,
PROJECT,
ACCOUNTSTRING,
BASEEXCHANGERATEID,
ORGANIZATIONEXCHANGERATEID,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
nullif(ERRORMESSAGE, '')
from
dbo.UFN_REVENUE_GENERATEGLDISTRIBUTION_FORSINGLEREVENUE(@SALESORDERTRANSACTIONID)
where
REVENUESPLITID = @ORDEROVERAGELINEITEMID;
declare @ERRORMESSAGE nvarchar(max);
select top 1 @ERRORMESSAGE = ERRORMESSAGE from @DISTRIBUTIONS where ERRORMESSAGE is not null and ACCOUNTID is null;
if @ERRORMESSAGE is not null begin
raiserror('%s', 13, 1, @ERRORMESSAGE);
end
else begin
insert into dbo.GLACCOUNTMAPPINGERROR (
TRANSACTIONID,
TRANSACTIONTYPECODE,
ERRORMESSAGE,
MAPPEDVALUES,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select distinct
@ID,
5, -- Order
ERRORMESSAGE,
convert(nvarchar(max), MAPPEDVALUES),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@DISTRIBUTIONS
where
ERRORMESSAGE is not null;
end
-- Insert overage credit journal entry
declare @OVERAGECREDITID uniqueidentifier = newid();
insert into dbo.JOURNALENTRY (
ID,
FINANCIALTRANSACTIONLINEITEMID,
TRANSACTIONTYPECODE,
SUBLEDGERTYPECODE,
TRANSACTIONAMOUNT,
BASEAMOUNT,
ORGAMOUNT,
COMMENT,
POSTDATE,
GLACCOUNTID,
TRANSACTIONCURRENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@OVERAGECREDITID,
@ORDEROVERAGELINEITEMID,
1, -- Credit
1, -- Credit
@TOTALOVERAGEAMOUNT,
@TOTALOVERAGEAMOUNT,
@TOTALOVERAGEAMOUNT,
REFERENCE,
@POSTDATE,
ACCOUNTID,
TRANSACTIONCURRENCYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@DISTRIBUTIONS
where
TRANSACTIONTYPECODE = 1; -- Credit
insert into dbo.JOURNALENTRY_EXT (
ID,
DISTRIBUTIONTABLEID,
PROJECT,
JOURNAL,
PRECALCORGANIZATIONEXCHANGERATEID,
PRECALCBASEEXCHANGERATEID,
ACCOUNT,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@OVERAGECREDITID,
@OVERAGECREDITID,
PROJECT,
@JOURNAL,
ORGANIZATIONEXCHANGERATEID,
BASEEXCHANGERATEID,
ACCOUNT,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@DISTRIBUTIONS
where
TRANSACTIONTYPECODE = 1; -- Credit
-- Insert overage debit journal entries
insert into dbo.JOURNALENTRY (
ID,
FINANCIALTRANSACTIONLINEITEMID,
TRANSACTIONTYPECODE,
SUBLEDGERTYPECODE,
TRANSACTIONAMOUNT,
BASEAMOUNT,
ORGAMOUNT,
COMMENT,
POSTDATE,
GLACCOUNTID,
TRANSACTIONCURRENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
OVERAGEPAYMENTS.NEWJOURNALENTRYID,
@ORDEROVERAGELINEITEMID,
0, -- Debit
0, -- Debit
OVERAGEPAYMENTS.OVERAGEAMOUNT,
OVERAGEPAYMENTS.OVERAGEAMOUNT,
OVERAGEPAYMENTS.OVERAGEAMOUNT,
REFERENCE,
@POSTDATE,
JOURNALENTRY.GLACCOUNTID,
JOURNALENTRY.TRANSACTIONCURRENCYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@OVERAGEPAYMENTS as OVERAGEPAYMENTS
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as OVERAGEPAYMENTLINEITEMS on OVERAGEPAYMENTLINEITEMS.FINANCIALTRANSACTIONID = OVERAGEPAYMENTS.ID
inner join
dbo.REVENUESPLIT_EXT as OVERAGEPAYMENTLINEITEMSEXTENSION on OVERAGEPAYMENTLINEITEMSEXTENSION.ID = OVERAGEPAYMENTLINEITEMS.ID
inner join
dbo.JOURNALENTRY on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = OVERAGEPAYMENTLINEITEMS.ID
outer apply
@DISTRIBUTIONS as DISTRIBUTIONS
where
OVERAGEPAYMENTLINEITEMSEXTENSION.TYPECODE = 19 -- Unearned revenue
and JOURNALENTRY.TRANSACTIONTYPECODE = 1 -- Credit
and DISTRIBUTIONS.TRANSACTIONTYPECODE = 0 -- Debit
and OVERAGEPAYMENTLINEITEMS.DELETEDON is null
and OVERAGEPAYMENTLINEITEMS.TYPECODE = 0 -- Standard
insert into dbo.JOURNALENTRY_EXT (
ID,
PROJECT,
JOURNAL,
TABLENAMECODE,
DISTRIBUTIONTABLEID,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
ACCOUNT,
PRECALCPOSTSTATUSCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
OVERAGEPAYMENTS.NEWJOURNALENTRYID,
DISTRIBUTIONS.PROJECT,
@JOURNAL,
1, -- REVENUEGLDISTRIBUTION
OVERAGEPAYMENTS.NEWJOURNALENTRYID,
JOURNALENTRY_EXT.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
GLACCOUNT.ACCOUNTNUMBER,
1, -- Not posted
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@OVERAGEPAYMENTS as OVERAGEPAYMENTS
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as OVERAGEPAYMENTLINEITEMS on OVERAGEPAYMENTLINEITEMS.FINANCIALTRANSACTIONID = OVERAGEPAYMENTS.ID
inner join
dbo.REVENUESPLIT_EXT as OVERAGEPAYMENTLINEITEMSEXTENSION on OVERAGEPAYMENTLINEITEMSEXTENSION.ID = OVERAGEPAYMENTLINEITEMS.ID
inner join
dbo.JOURNALENTRY on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = OVERAGEPAYMENTLINEITEMS.ID
inner join
dbo.JOURNALENTRY_EXT on JOURNALENTRY_EXT.ID = JOURNALENTRY.ID
inner join
dbo.GLACCOUNT on GLACCOUNT.ID = JOURNALENTRY.GLACCOUNTID
outer apply
@DISTRIBUTIONS as DISTRIBUTIONS
where
OVERAGEPAYMENTLINEITEMSEXTENSION.TYPECODE = 19 -- Unearned revenue
and JOURNALENTRY.TRANSACTIONTYPECODE = 1 -- Credit
and DISTRIBUTIONS.TRANSACTIONTYPECODE = 0 -- Debit
and OVERAGEPAYMENTLINEITEMS.DELETEDON is null
and OVERAGEPAYMENTLINEITEMS.TYPECODE = 0 -- Standard
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;