USP_DATAFORMTEMPLATE_ADD_RESERVATIONCOPY
The save procedure used by the add dataform template "Reservation Copy Add Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @CONTEXTRESERVATIONID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @CONSTITUENTID | uniqueidentifier | IN | Patron |
| @CONTACTRELATIONSHIPID | uniqueidentifier | IN | Contact |
| @NAME | nvarchar(100) | IN | Reservation name |
| @ARRIVALDATE | date | IN | Visit date |
| @EXCEEDCAPACITY | bit | IN | Exceed capacity available |
| @IGNOREITINERARYRESOURCECONFLICTS | bit | IN | Ignore itinerary resource conflicts |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_RESERVATIONCOPY
(
@ID uniqueidentifier = null output,
@CONTEXTRESERVATIONID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier,
@CONSTITUENTID uniqueidentifier = null,
@CONTACTRELATIONSHIPID uniqueidentifier = null,
@NAME nvarchar(100) = null,
@ARRIVALDATE date = null,
@EXCEEDCAPACITY bit = 0,
@IGNOREITINERARYRESOURCECONFLICTS bit = 0
)
as
set nocount on;
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
declare @TODAY date;
set @CURRENTDATE = getdate();
set @TODAY = @CURRENTDATE;
declare @PRICINGCODE tinyint;
declare @CONTACTCONSTITUENTID uniqueidentifier;
declare @CONTACTADDRESSID uniqueidentifier;
declare @CONTACTEMAILADDRESSID uniqueidentifier;
declare @CONTACTPHONEID uniqueidentifier;
declare @DEPOSITREQUIRED bit;
declare @DEPOSITTIMEVALUE smallint;
declare @DEPOSITTIMECODE tinyint;
declare @DEPOSITTYPECODE tinyint;
declare @DEPOSITAMOUNT money;
declare @SECURITYDEPOSITREQUIRED bit;
declare @SECURITYDEPOSITTIMEVALUE smallint;
declare @SECURITYDEPOSITTIMECODE tinyint;
declare @SECURITYDEPOSITTYPECODE tinyint;
declare @SECURITYDEPOSITAMOUNT money;
declare @CONTRACTREQUIRED bit;
declare @CONTRACTTIMEVALUE smallint;
declare @CONTRACTTIMECODE tinyint;
declare @FINALCOUNTREQUIRED bit;
declare @FINALCOUNTTIMEVALUE smallint;
declare @FINALCOUNTTIMECODE tinyint;
declare @FINALTIMEVALUE smallint;
declare @FINALTIMECODE tinyint;
declare @DEPOSITDUEDATE date;
declare @SECURITYDEPOSITDUEDATE date;
declare @FINALDUEDATE date;
declare @CONTRACTDUEDATE date;
declare @FINALCOUNTDUEDATE date;
select @PRICINGCODE = PRICINGCODE from dbo.RESERVATION where ID = @CONTEXTRESERVATIONID;
select top 1
@DEPOSITREQUIRED = DEPOSITREQUIRED,
@DEPOSITTIMEVALUE = DEPOSITTIMEVALUE,
@DEPOSITTIMECODE = DEPOSITTIMECODE,
@DEPOSITTYPECODE = DEPOSITTYPECODE,
@DEPOSITAMOUNT = DEPOSITAMOUNT,
@SECURITYDEPOSITREQUIRED = SECURITYDEPOSITREQUIRED,
@SECURITYDEPOSITTIMEVALUE = SECURITYDEPOSITTIMEVALUE,
@SECURITYDEPOSITTIMECODE = SECURITYDEPOSITTIMECODE,
@SECURITYDEPOSITTYPECODE = SECURITYDEPOSITTYPECODE,
@SECURITYDEPOSITAMOUNT = SECURITYDEPOSITAMOUNT,
@CONTRACTREQUIRED = CONTRACTREQUIRED,
@CONTRACTTIMEVALUE = CONTRACTTIMEVALUE,
@CONTRACTTIMECODE = CONTRACTTIMECODE,
@FINALCOUNTREQUIRED = FINALCOUNTREQUIRED,
@FINALCOUNTTIMEVALUE = FINALCOUNTTIMEVALUE,
@FINALCOUNTTIMECODE = FINALCOUNTTIMECODE,
@FINALTIMEVALUE = FINALTIMEVALUE,
@FINALTIMECODE = FINALTIMECODE
from
dbo.GROUPSALESDEFAULT;
if @DEPOSITREQUIRED = 1
begin
if @DEPOSITTIMECODE = 0
begin
set @DEPOSITDUEDATE = dateadd(day, -@DEPOSITTIMEVALUE, @ARRIVALDATE);
if @DEPOSITDUEDATE < @TODAY and @ARRIVALDATE >= @TODAY
set @DEPOSITDUEDATE = @TODAY;
end
else if @DEPOSITTIMECODE = 1
set @DEPOSITDUEDATE = dateadd(day, @DEPOSITTIMEVALUE, @TODAY);
if @DEPOSITTYPECODE = 1
set @DEPOSITAMOUNT = 0;
end
if @SECURITYDEPOSITREQUIRED = 1
begin
if @SECURITYDEPOSITTIMECODE = 0
begin
set @SECURITYDEPOSITDUEDATE = dateadd(day, -@SECURITYDEPOSITTIMEVALUE, @ARRIVALDATE);
if @SECURITYDEPOSITDUEDATE < @TODAY and @ARRIVALDATE >= @TODAY
set @SECURITYDEPOSITDUEDATE = @TODAY;
end
else if @SECURITYDEPOSITTIMECODE = 1
set @SECURITYDEPOSITDUEDATE = dateadd(day, @SECURITYDEPOSITTIMEVALUE, @TODAY);
if @SECURITYDEPOSITTYPECODE = 1
set @SECURITYDEPOSITAMOUNT = 0;
end
if @FINALTIMECODE = 0
set @FINALDUEDATE = dateadd(day, -@FINALTIMEVALUE, @ARRIVALDATE);
else if @FINALTIMECODE = 1
set @FINALDUEDATE = dateadd(day, @FINALTIMEVALUE, @TODAY);
else if @FINALTIMECODE = 2
set @FINALDUEDATE = dateadd(day, @FINALTIMEVALUE, @DEPOSITDUEDATE);
if @FINALDUEDATE > @ARRIVALDATE
set @FINALDUEDATE = @ARRIVALDATE;
else if @FINALDUEDATE < @TODAY and @ARRIVALDATE >= @TODAY
set @FINALDUEDATE = @TODAY;
if @DEPOSITDUEDATE > @FINALDUEDATE
set @DEPOSITDUEDATE = @FINALDUEDATE;
if @SECURITYDEPOSITDUEDATE > @FINALDUEDATE
set @SECURITYDEPOSITDUEDATE = @FINALDUEDATE;
if @CONTRACTREQUIRED = 1
begin
if @CONTRACTTIMECODE = 0
set @CONTRACTDUEDATE = dateadd(day, -@CONTRACTTIMEVALUE, @ARRIVALDATE);
else if @CONTRACTTIMECODE = 1
set @CONTRACTDUEDATE = dateadd(day, @CONTRACTTIMEVALUE, @TODAY);
if @CONTRACTDUEDATE > @FINALDUEDATE
set @CONTRACTDUEDATE = @FINALDUEDATE;
else if @CONTRACTDUEDATE < @TODAY and @ARRIVALDATE >= @TODAY
set @CONTRACTDUEDATE = @TODAY;
end
if @FINALCOUNTREQUIRED = 1
begin
if @FINALCOUNTTIMECODE = 0
set @FINALCOUNTDUEDATE = dateadd(day, -@FINALCOUNTTIMEVALUE, @ARRIVALDATE);
else if @FINALCOUNTTIMECODE = 1
set @FINALCOUNTDUEDATE = dateadd(day, @FINALCOUNTTIMEVALUE, @TODAY);
if @FINALCOUNTDUEDATE > @FINALDUEDATE
set @FINALCOUNTDUEDATE = @FINALDUEDATE;
else if @FINALCOUNTDUEDATE < @TODAY and @ARRIVALDATE >= @TODAY
set @FINALCOUNTDUEDATE = @TODAY;
end
begin try
if @EXCEEDCAPACITY = 0
begin
declare @CHECKCAPACITY bit;
select top 1 @CHECKCAPACITY = case when MAXIMUMCAPACITY > 0 then 1 else 0 end from dbo.GROUPSALESDEFAULT;
if @CHECKCAPACITY = 1
begin
declare @NUMBEROFDAYS int;
declare @CAPACITYNEEDED int;
declare @I int = 0;
select @NUMBEROFDAYS = datediff(day, STARTDATETIME, ENDDATETIME) + 1
from dbo.RESERVATION
where RESERVATION.ID = @CONTEXTRESERVATIONID;
set @CAPACITYNEEDED = dbo.UFN_RESERVATION_TOTALVISITORCOUNT(@CONTEXTRESERVATIONID);
while @I < @NUMBEROFDAYS
begin
if @CAPACITYNEEDED > dbo.UFN_GROUPSALESCAPACITY_CAPACITYREMAINING(dateadd(day, @I, @ARRIVALDATE), null)
raiserror('ERR_EXCEEDSCAPACITY', 13, 1);
set @I = @I + 1;
end
end
end
exec dbo.USP_RESERVATION_GETCONTACTINFO
@CONSTITUENTID,
@CONTACTRELATIONSHIPID,
@CONTACTCONSTITUENTID output,
@CONTACTADDRESSID output,
@CONTACTEMAILADDRESSID output,
@CONTACTPHONEID output;
-- Create new reservation
insert into dbo.SALESORDER
(
ID,
APPUSERID,
SALESMETHODTYPECODE,
CONSTITUENTID,
CONTACTRELATIONSHIPID,
RECIPIENTID,
ADDRESSID,
EMAILADDRESSID,
PHONEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
@CURRENTAPPUSERID,
3, -- Group Sales
@CONSTITUENTID,
@CONTACTRELATIONSHIPID,
@CONTACTCONSTITUENTID,
@CONTACTADDRESSID,
@CONTACTEMAILADDRESSID,
@CONTACTPHONEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.RESERVATION
(
ID,
NAME,
ARRIVALDATE,
ARRIVALTIME,
NUMBEROFBUSES,
ARRIVALAREACODEID,
ARRIVALNOTES,
DRIVERNAME,
DRIVERPHONENUMBER,
DEPOSITREQUIRED,
DEPOSITAMOUNT,
DEPOSITDUEDATE,
SECURITYDEPOSITREQUIRED,
SECURITYDEPOSITAMOUNT,
SECURITYDEPOSITDUEDATE,
FINALDUEDATE,
FINALCOUNTREQUIRED,
FINALCOUNTDUEDATE,
CONTRACTREQUIRED,
CONTRACTDUEDATE,
PRICINGCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@ID,
@NAME,
@ARRIVALDATE,
ARRIVALTIME,
NUMBEROFBUSES,
ARRIVALAREACODEID,
ARRIVALNOTES,
DRIVERNAME,
DRIVERPHONENUMBER,
@DEPOSITREQUIRED,
@DEPOSITAMOUNT,
@DEPOSITDUEDATE,
@SECURITYDEPOSITREQUIRED,
@SECURITYDEPOSITAMOUNT,
@SECURITYDEPOSITDUEDATE,
@FINALDUEDATE,
@FINALCOUNTREQUIRED,
@FINALCOUNTDUEDATE,
@CONTRACTREQUIRED,
@CONTRACTDUEDATE,
@PRICINGCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.RESERVATION
where
ID = @CONTEXTRESERVATIONID;
exec dbo.USP_RESERVATIONSTATUSHISTORY_ADD @ID, @CHANGEAGENTID, 0;
-- Handle rate scale
if @PRICINGCODE = 1
begin
insert into dbo.RESERVATIONRATESCALE
(
ID,
RATESCALEID,
INCLUDEALLPROGRAMS,
INCLUDEALLFEES,
INCLUDEALLRESOURCES,
INCLUDEALLSTAFFRESOURCES,
USEPERTICKETAFTERMAX,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@ID,
RATESCALEID,
INCLUDEALLPROGRAMS,
INCLUDEALLFEES,
INCLUDEALLRESOURCES,
INCLUDEALLSTAFFRESOURCES,
USEPERTICKETAFTERMAX,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.RESERVATIONRATESCALE
where
ID = @CONTEXTRESERVATIONID;
insert into dbo.RESERVATIONRATESCALEAPPLICATION
(
ID,
RESERVATIONRATESCALEID,
TYPECODE,
AMOUNT,
PROGRAMID,
FEEID,
RESOURCEID,
VOLUNTEERTYPEID,
ISADDEDMANUALLY,
ISADDEDAUTOMATICALLY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
TYPECODE,
AMOUNT,
PROGRAMID,
FEEID,
RESOURCEID,
VOLUNTEERTYPEID,
ISADDEDMANUALLY,
ISADDEDAUTOMATICALLY,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.RESERVATIONRATESCALEAPPLICATION
where
RESERVATIONRATESCALEID = @CONTEXTRESERVATIONID;
insert into dbo.RESERVATIONRATESCALEFEE
(
ID,
RESERVATIONRATESCALEID,
FEEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
FEEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.RESERVATIONRATESCALEFEE
where
RESERVATIONRATESCALEID = @CONTEXTRESERVATIONID;
insert into dbo.RESERVATIONRATESCALEPRICE
(
ID,
RESERVATIONRATESCALEID,
AMOUNT,
GROUPMINIMUM,
GROUPMAXIMUM,
INUSE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
AMOUNT,
GROUPMINIMUM,
GROUPMAXIMUM,
INUSE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.RESERVATIONRATESCALEPRICE
where
RESERVATIONRATESCALEID = @CONTEXTRESERVATIONID;
insert into dbo.RESERVATIONRATESCALEPROGRAM
(
ID,
RESERVATIONRATESCALEID,
PROGRAMID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
PROGRAMID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.RESERVATIONRATESCALEPROGRAM
where
RESERVATIONRATESCALEID = @CONTEXTRESERVATIONID;
insert into dbo.RESERVATIONRATESCALERESOURCE
(
ID,
RESERVATIONRATESCALEID,
RESOURCEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
RESOURCEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.RESERVATIONRATESCALERESOURCE
where
RESERVATIONRATESCALEID = @CONTEXTRESERVATIONID;
insert into dbo.RESERVATIONRATESCALESTAFFRESOURCE
(
ID,
RESERVATIONRATESCALEID,
VOLUNTEERTYPEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
VOLUNTEERTYPEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.RESERVATIONRATESCALESTAFFRESOURCE
where
RESERVATIONRATESCALEID = @CONTEXTRESERVATIONID;
end
-- Copy itineraries
declare @SOURCEITINERARYID uniqueidentifier;
declare @DESTINATIONITINERARYID uniqueidentifier;
declare @START datetime;
declare @END datetime;
declare ITINERARIES_CURSOR cursor local fast_forward for
select ID from dbo.ITINERARY where RESERVATIONID = @CONTEXTRESERVATIONID;
open ITINERARIES_CURSOR;
fetch next from ITINERARIES_CURSOR
into @SOURCEITINERARYID;
while @@FETCH_STATUS = 0
begin
set @DESTINATIONITINERARYID = newid();
insert into dbo.ITINERARY
(
ID,
RESERVATIONID,
NAME,
GROUPSALESGROUPTYPECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@DESTINATIONITINERARYID,
@ID,
NAME,
GROUPSALESGROUPTYPECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.ITINERARY
where
ID = @SOURCEITINERARYID;
insert into dbo.ITINERARYATTENDEE
(
ID,
ITINERARYID,
PRICETYPECODEID,
QUANTITY,
SEQUENCE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@DESTINATIONITINERARYID,
PRICETYPECODEID,
QUANTITY,
SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.ITINERARYATTENDEE
where
ITINERARYID = @SOURCEITINERARYID;
exec dbo.USP_ITINERARY_COPYFROMEXISTING @DESTINATIONITINERARYID, @SOURCEITINERARYID, @CHANGEAGENTID, @IGNOREITINERARYRESOURCECONFLICTS;
if @IGNOREITINERARYRESOURCECONFLICTS = 0 and
exists (select 1 from dbo.ITINERARYITEM where ITINERARYITEM.ITINERARYID = @DESTINATIONITINERARYID)
begin
select
@START = ITINERARY.STARTDATETIME,
@END = ITINERARY.ENDDATETIME
from dbo.ITINERARY
where ID = @DESTINATIONITINERARYID
if dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
(
@START, @END,
null,
dbo.UFN_ITINERARYRESOURCE_GETRESOURCES_TOITEMLISTXML(@DESTINATIONITINERARYID),
dbo.UFN_ITINERARY_GETSTAFFRESOURCES_TOITEMLISTXML(@DESTINATIONITINERARYID),
null, @DESTINATIONITINERARYID, null,
0,
1, -- Ignore itinerary resources
0,
0
) = 1
begin
raiserror('BBERR_CONFLICTSEXIST', 13, 1);
return 1;
end
end
fetch next from ITINERARIES_CURSOR
into @SOURCEITINERARYID;
end
close ITINERARIES_CURSOR;
deallocate ITINERARIES_CURSOR;
if @PRICINGCODE = 0
begin
-- WI #165816 - Copying over all discounts
-- Copy Adjustable first...
insert into dbo.SALESORDERADJUSTABLEDISCOUNT
(
ID,
SALESORDERID,
CALCULATIONTYPECODE,
[PERCENT],
AMOUNT,
DISCOUNTREASONCODEID,
DISCOUNTNAME,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
CALCULATIONTYPECODE,
[PERCENT],
AMOUNT,
DISCOUNTREASONCODEID,
DISCOUNTNAME,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.SALESORDERADJUSTABLEDISCOUNT
where SALESORDERID = @CONTEXTRESERVATIONID
-- holds all possible discounts for the order
declare @MANUALDISCOUNTS table
(
SALESORDERID uniqueidentifier,
DISCOUNTID uniqueidentifier,
PROMOTIONALCODE nvarchar(50),
ISPROMOTIONALCODE bit,
ISLIMITED bit,
NUMBEROFDISCOUNTSPERORDER int,
ISADJUSTABLEDISCOUNT bit
)
-- Order discounts first
insert into @MANUALDISCOUNTS
select
@ID,
SALESORDERITEMORDERDISCOUNT.DISCOUNTID,
isnull(SALESORDERMANUALDISCOUNT.PROMOTIONALCODE, ''),
case when (SALESORDERMANUALDISCOUNT.PROMOTIONALCODE is not null and SALESORDERMANUALDISCOUNT.PROMOTIONALCODE <> '')
then 1
else 0
end,
case when SALESORDERDISCOUNTLIMITOVERRIDE.NUMBEROFDISCOUNTSPERORDER is not null
then 1
else 0
end,
SALESORDERDISCOUNTLIMITOVERRIDE.NUMBEROFDISCOUNTSPERORDER,
SALESORDERITEMORDERDISCOUNT.ISADJUSTABLEDISCOUNT
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMORDERDISCOUNT
on SALESORDERITEM.ID = SALESORDERITEMORDERDISCOUNT.ID
left join dbo.SALESORDERMANUALDISCOUNT
on (SALESORDERITEMORDERDISCOUNT.DISCOUNTID = SALESORDERMANUALDISCOUNT.DISCOUNTID and SALESORDERITEM.SALESORDERID = SALESORDERMANUALDISCOUNT.SALESORDERID)
left join dbo.SALESORDERDISCOUNTLIMITOVERRIDE
on (SALESORDERITEMORDERDISCOUNT.DISCOUNTID = SALESORDERDISCOUNTLIMITOVERRIDE.DISCOUNTID and SALESORDERITEM.SALESORDERID = SALESORDERDISCOUNTLIMITOVERRIDE.SALESORDERID)
where SALESORDERITEM.SALESORDERID = @CONTEXTRESERVATIONID
union all
select
@ID,
SALESORDERITEMITEMDISCOUNT.DISCOUNTID,
isnull(SALESORDERMANUALDISCOUNT.PROMOTIONALCODE, ''),
case when (SALESORDERMANUALDISCOUNT.PROMOTIONALCODE is not null and SALESORDERMANUALDISCOUNT.PROMOTIONALCODE <> '')
then 1
else 0
end,
case when SALESORDERDISCOUNTLIMITOVERRIDE.NUMBEROFDISCOUNTSPERORDER is not null
then 1
else 0
end,
SALESORDERDISCOUNTLIMITOVERRIDE.NUMBEROFDISCOUNTSPERORDER,
0
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMITEMDISCOUNT
on SALESORDERITEM.ID = SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID
left join dbo.SALESORDERMANUALDISCOUNT
on (SALESORDERITEMITEMDISCOUNT.DISCOUNTID = SALESORDERMANUALDISCOUNT.DISCOUNTID and SALESORDERITEM.SALESORDERID = SALESORDERMANUALDISCOUNT.SALESORDERID)
left join dbo.SALESORDERDISCOUNTLIMITOVERRIDE
on (SALESORDERITEMITEMDISCOUNT.DISCOUNTID = SALESORDERDISCOUNTLIMITOVERRIDE.DISCOUNTID and SALESORDERITEM.SALESORDERID = SALESORDERDISCOUNTLIMITOVERRIDE.SALESORDERID)
where SALESORDERITEM.SALESORDERID = @CONTEXTRESERVATIONID
-- get rid of all discounts already on the order
delete from @MANUALDISCOUNTS
where DISCOUNTID in
(
select
SALESORDERITEMORDERDISCOUNT.DISCOUNTID
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMORDERDISCOUNT
on SALESORDERITEM.ID = SALESORDERITEMORDERDISCOUNT.ID
where SALESORDERITEM.SALESORDERID = @ID
and SALESORDERITEMORDERDISCOUNT.DISCOUNTID is not null
union all
select
SALESORDERITEMITEMDISCOUNT.DISCOUNTID
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMITEMDISCOUNT
on SALESORDERITEM.ID = SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID
where SALESORDERITEM.SALESORDERID = @ID
)
declare @DISCOUNTID uniqueidentifier
declare @PROMOTIONALCODE nvarchar(50)
declare @ISPROMOTIONALCODE bit
declare @ISLIMITED bit
declare @NUMBEROFDISCOUNTSPERORDER int
declare @ISADJUSTABLEDISCOUNT bit
declare DISCOUNTS_CURSOR cursor local fast_forward for
select DISCOUNTID, PROMOTIONALCODE, ISPROMOTIONALCODE, ISLIMITED, NUMBEROFDISCOUNTSPERORDER, ISADJUSTABLEDISCOUNT
from @MANUALDISCOUNTS
open DISCOUNTS_CURSOR;
fetch next from DISCOUNTS_CURSOR
into @DISCOUNTID, @PROMOTIONALCODE, @ISPROMOTIONALCODE, @ISLIMITED, @NUMBEROFDISCOUNTSPERORDER, @ISADJUSTABLEDISCOUNT;
-- apply discounts
while @@FETCH_STATUS = 0
begin
exec dbo.USP_DATAFORMTEMPLATE_ADD_DAILYSALESORDER_MANUALDISCOUNT null, @ID, @CHANGEAGENTID, @DISCOUNTID, @PROMOTIONALCODE, @ISPROMOTIONALCODE, @ISLIMITED, @NUMBEROFDISCOUNTSPERORDER, 0, @ISADJUSTABLEDISCOUNT
fetch next from DISCOUNTS_CURSOR
into @DISCOUNTID, @PROMOTIONALCODE, @ISPROMOTIONALCODE, @ISLIMITED, @NUMBEROFDISCOUNTSPERORDER, @ISADJUSTABLEDISCOUNT;
end
close DISCOUNTS_CURSOR;
deallocate DISCOUNTS_CURSOR;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;