USP_DATAFORMTEMPLATE_EDIT_SALESDEPOSITPROCESSPREPROCESS_PRELOAD
The load procedure used by the edit dataform template "Sales Deposit Preprocess Edit Form"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter used to load the fields defined on the form. |
| @DATALOADED | bit | INOUT | Output parameter indicating whether or not data was actually loaded. |
| @TSLONG | bigint | INOUT | Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record. |
| @UNDEFINEDPAYMENTTYPE | xml | INOUT | You must add the following payment methods to an existing or new deposit template before you can run the process. |
| @SALESDEPOSITTEMPLATE | xml | INOUT | |
| @NAME | nvarchar(100) | INOUT | Process name |
| @NUMBEROFDEPOSITS | int | INOUT | Number of deposits |
| @SALESDEPOSITPROCESSID | uniqueidentifier | INOUT |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SALESDEPOSITPROCESSPREPROCESS_PRELOAD
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@TSLONG bigint = 0 output,
@UNDEFINEDPAYMENTTYPE xml = null output,
@SALESDEPOSITTEMPLATE xml = null output,
@NAME nvarchar(100) = null output,
@NUMBEROFDEPOSITS integer = null output,
@SALESDEPOSITPROCESSID uniqueidentifier = null output
)
as
set nocount on;
declare @ISRECONCILEDPROCESS bit = null
select
@DATALOADED = 1,
@NAME = [NAME],
@ISRECONCILEDPROCESS = [ISRECONCILEDPROCESS],
@TSLONG = [TSLONG]
from dbo.[SALESDEPOSITPROCESS]
where [ID] = @ID
set @SALESDEPOSITPROCESSID = @ID
declare @SALESORDERPAYMENTPAYMENTTYPES table (
[PAYMENTMETHODCODE] tinyint,
[CREDITTYPECODEID] uniqueidentifier,
[OTHERPAYMENTMETHODCODEID] uniqueidentifier,
[PAYMENTMETHOD] nvarchar(100)
)
insert into @SALESORDERPAYMENTPAYMENTTYPES
select distinct
[REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE],
[CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID],
[OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID],
case [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE]
when 2 then dbo.UFN_CREDITTYPECODE_GETDESCRIPTION([CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID])
when 10 then dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID])
else dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION([REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE])
end
from dbo.[SALESORDERPAYMENT]
inner join dbo.[REVENUEPAYMENTMETHOD]
on [SALESORDERPAYMENT].[PAYMENTID] = [REVENUEPAYMENTMETHOD].[REVENUEID]
inner join dbo.[SALESORDER]
on [SALESORDER].[ID] = [SALESORDERPAYMENT].[SALESORDERID]
left join dbo.[CREDITCARDPAYMENTMETHODDETAIL]
on [REVENUEPAYMENTMETHOD].[ID] = [CREDITCARDPAYMENTMETHODDETAIL].[ID]
left join dbo.[OTHERPAYMENTMETHODDETAIL]
on [REVENUEPAYMENTMETHOD].[ID] = [OTHERPAYMENTMETHODDETAIL].[ID]
left join dbo.[RECONCILIATION]
on [SALESORDERPAYMENT].[RECONCILIATIONID] = [RECONCILIATION].[ID]
where
(
(@ISRECONCILEDPROCESS = 1 and [SALESORDERPAYMENT].[DONOTRECONCILE] = 0 and [RECONCILIATION].[STATUSCODE] = 2) or
(@ISRECONCILEDPROCESS = 0 and [SALESORDERPAYMENT].[DONOTRECONCILE] = 1 and [SALESORDER].[STATUSCODE] = 1)
) and
(
[OTHERPAYMENTMETHODDETAIL].[ID] is null or
[OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID] not in
(select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD)
) and
--Unlinked
[REVENUEPAYMENTMETHOD].[REVENUEID] not in (
select [BANKACCOUNTDEPOSITPAYMENT].[ID]
from dbo.[BANKACCOUNTDEPOSITPAYMENT] with (nolock)
)
-- Reservation security deposit payments
insert into @SALESORDERPAYMENTPAYMENTTYPES
select distinct
[REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE],
[CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID],
[OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID],
case [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE]
when 2 then dbo.UFN_CREDITTYPECODE_GETDESCRIPTION([CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID])
when 10 then dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID])
else dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION([REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE])
end
from dbo.[RESERVATIONSECURITYDEPOSITPAYMENT]
inner join dbo.[REVENUEPAYMENTMETHOD]
on [RESERVATIONSECURITYDEPOSITPAYMENT].[PAYMENTID] = [REVENUEPAYMENTMETHOD].[REVENUEID]
inner join dbo.[SALESORDER]
on [SALESORDER].[ID] = [RESERVATIONSECURITYDEPOSITPAYMENT].[RESERVATIONID]
left join dbo.[CREDITCARDPAYMENTMETHODDETAIL]
on [REVENUEPAYMENTMETHOD].[ID] = [CREDITCARDPAYMENTMETHODDETAIL].[ID]
left join dbo.[OTHERPAYMENTMETHODDETAIL]
on [REVENUEPAYMENTMETHOD].[ID] = [OTHERPAYMENTMETHODDETAIL].[ID]
left join dbo.[RECONCILIATION]
on [RESERVATIONSECURITYDEPOSITPAYMENT].[RECONCILIATIONID] = [RECONCILIATION].[ID]
where
(
(@ISRECONCILEDPROCESS = 1 and [RECONCILIATION].[STATUSCODE] = 2) or
(@ISRECONCILEDPROCESS = 0 and [SALESORDER].[SALESMETHODTYPECODE] = 2)
) and
(
[OTHERPAYMENTMETHODDETAIL].[ID] is null or
[OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID] not in
(select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD)
) and
--Unlinked
[REVENUEPAYMENTMETHOD].[REVENUEID] not in (
select [BANKACCOUNTDEPOSITPAYMENT].[ID]
from dbo.[BANKACCOUNTDEPOSITPAYMENT] with (nolock)
)
--Refund payment types that might not be in sales
insert into @SALESORDERPAYMENTPAYMENTTYPES
select distinct
[CREDITPAYMENT].[PAYMENTMETHODCODE],
[CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID],
[CREDITPAYMENT].[OTHERPAYMENTMETHODCODEID],
case [CREDITPAYMENT].[PAYMENTMETHODCODE]
when 2 then dbo.UFN_CREDITTYPECODE_GETDESCRIPTION([CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID])
when 10 then dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([CREDITPAYMENT].[OTHERPAYMENTMETHODCODEID])
else dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION([CREDITPAYMENT].[PAYMENTMETHODCODE])
end
from dbo.[CREDITPAYMENT]
left join dbo.[REVENUESPLIT]
on [CREDITPAYMENT].[REVENUESPLITID] = [REVENUESPLIT].[ID]
inner join dbo.[REVENUE]
on
[CREDITPAYMENT].[REVENUEID] = [REVENUE].[ID] or
REVENUESPLIT.REVENUEID = REVENUE.ID
inner join dbo.[REVENUEPAYMENTMETHOD]
on [REVENUE].[ID] = [REVENUEPAYMENTMETHOD].[REVENUEID]
left join dbo.[CREDITCARDPAYMENTMETHODDETAIL]
on
[REVENUEPAYMENTMETHOD].[ID] = [CREDITCARDPAYMENTMETHODDETAIL].[ID] and
[CREDITPAYMENT].[PAYMENTMETHODCODE] = 2 --Only getting the creditcard type if the credit card was refunded as a credit card refund
left join dbo.[RECONCILIATION]
on [CREDITPAYMENT].[RECONCILIATIONID] = [RECONCILIATION].[ID]
where
(@ISRECONCILEDPROCESS = 1 and [RECONCILIATION].[STATUSCODE] = 2) and
(
[CREDITPAYMENT].[OTHERPAYMENTMETHODCODEID] is null or
[CREDITPAYMENT].[OTHERPAYMENTMETHODCODEID] not in
(select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD)
) and
not exists (
select [SALESPAYMENTTYPES].[PAYMENTMETHODCODE]
from @SALESORDERPAYMENTPAYMENTTYPES as [SALESPAYMENTTYPES]
where
[SALESPAYMENTTYPES].[PAYMENTMETHODCODE] = [CREDITPAYMENT].[PAYMENTMETHODCODE] and
(
[CREDITPAYMENT].[PAYMENTMETHODCODE] not in (2, 10) or
([CREDITPAYMENT].[PAYMENTMETHODCODE] = 2 and
[SALESPAYMENTTYPES].[CREDITTYPECODEID] = [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]) or
([CREDITPAYMENT].[PAYMENTMETHODCODE] = 10 and
[SALESPAYMENTTYPES].[OTHERPAYMENTMETHODCODEID] = [CREDITPAYMENT].[OTHERPAYMENTMETHODCODEID])
)
)
--If there are no cash payments, make sure that there is also no cash over/short
if not exists(
select [PAYMENTMETHODCODE]
from @SALESORDERPAYMENTPAYMENTTYPES as [PAYMENTPAYMENTTYPES]
where [PAYMENTMETHODCODE] = 0
)
begin
declare @CASHOVERSHORT money = 0
select @CASHOVERSHORT = coalesce(sum([ACTUALCASH]),0)
from dbo.[RECONCILIATION]
where
[STATUSCODE] = 2 and
@ISRECONCILEDPROCESS = 1
if @CASHOVERSHORT <> 0
begin
insert into @SALESORDERPAYMENTPAYMENTTYPES (
[PAYMENTMETHODCODE],
[PAYMENTMETHOD]
)
values (
0,
dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION(0)
)
end
end
--The payment types currently in a sales deposit template in this process
declare @CURRENTSALESDEPOSITPAYMENTTYPES table (
[ID] uniqueidentifier,
[SALESDEPOSITTEMPLATENAME] nvarchar(100),
[PAYMENTMETHODCODE] tinyint,
[CREDITTYPECODEID] uniqueidentifier,
[OTHERPAYMENTMETHODCODEID] uniqueidentifier,
[PAYMENTMETHOD] nvarchar(100)
)
insert into @CURRENTSALESDEPOSITPAYMENTTYPES
select
[SALESDEPOSITTEMPLATE].[ID],
[SALESDEPOSITTEMPLATE].[NAME],
[SALESDEPOSITTEMPLATEPAYMENTMETHOD].[PAYMENTMETHODCODE],
[SALESDEPOSITTEMPLATEPAYMENTMETHOD].[CREDITTYPECODEID],
[SALESDEPOSITTEMPLATEPAYMENTMETHOD].[OTHERPAYMENTMETHODCODEID],
case [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[PAYMENTMETHODCODE]
when 2 then dbo.UFN_CREDITTYPECODE_GETDESCRIPTION([SALESDEPOSITTEMPLATEPAYMENTMETHOD].[CREDITTYPECODEID])
when 10 then dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([SALESDEPOSITTEMPLATEPAYMENTMETHOD].[OTHERPAYMENTMETHODCODEID])
else dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION([SALESDEPOSITTEMPLATEPAYMENTMETHOD].[PAYMENTMETHODCODE])
end
from dbo.[SALESDEPOSITTEMPLATE]
inner join dbo.[SALESDEPOSITTEMPLATEPAYMENTMETHOD]
on [SALESDEPOSITTEMPLATE].[ID] = [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[SALESDEPOSITTEMPLATEID]
inner join @SALESORDERPAYMENTPAYMENTTYPES as [PAYMENTS]
on
[SALESDEPOSITTEMPLATEPAYMENTMETHOD].[PAYMENTMETHODCODE] = [PAYMENTS].[PAYMENTMETHODCODE] and
(
[SALESDEPOSITTEMPLATEPAYMENTMETHOD].[PAYMENTMETHODCODE] not in (2, 10) or
([SALESDEPOSITTEMPLATEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 2 and
[SALESDEPOSITTEMPLATEPAYMENTMETHOD].[CREDITTYPECODEID] = [PAYMENTS].[CREDITTYPECODEID]) or
([SALESDEPOSITTEMPLATEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 10 and
[SALESDEPOSITTEMPLATEPAYMENTMETHOD].[OTHERPAYMENTMETHODCODEID] = [PAYMENTS].[OTHERPAYMENTMETHODCODEID])
)
where [SALESDEPOSITTEMPLATE].[SALESDEPOSITPROCESSID] = @ID
set @UNDEFINEDPAYMENTTYPE = (
select
[PAYMENTS].[PAYMENTMETHODCODE],
[PAYMENTS].[CREDITTYPECODEID],
[PAYMENTS].[PAYMENTMETHOD],
null as [SALESDEPOSITTEMPLATEID],
[PAYMENTS].[OTHERPAYMENTMETHODCODEID]
from @SALESORDERPAYMENTPAYMENTTYPES as [PAYMENTS]
where not exists(
select [PAYMENTMETHODCODE]
from @CURRENTSALESDEPOSITPAYMENTTYPES as [DEPOSITPAYMENTTYPES]
where
[PAYMENTS].[PAYMENTMETHODCODE] = [DEPOSITPAYMENTTYPES].[PAYMENTMETHODCODE] and
(
[PAYMENTS].[PAYMENTMETHODCODE] not in (2, 10) or
([PAYMENTS].[PAYMENTMETHODCODE] = 2 and
[PAYMENTS].[CREDITTYPECODEID] = [DEPOSITPAYMENTTYPES].[CREDITTYPECODEID]) or
([PAYMENTS].[PAYMENTMETHODCODE] = 10 and
[PAYMENTS].[OTHERPAYMENTMETHODCODEID] = [DEPOSITPAYMENTTYPES].[OTHERPAYMENTMETHODCODEID])
)
)
for xml raw ('ITEM'), type, elements, root('UNDEFINEDPAYMENTTYPE'), BINARY BASE64
)
set @SALESDEPOSITTEMPLATE = (
select
distinct
[CSDPT].[ID] as [SALESDEPOSITTEMPLATEID],
(
select dbo.UDA_BUILDLIST(distinct [CURRENTSALESDEPOSITPAYMENTTYPES].[PAYMENTMETHOD])
from @CURRENTSALESDEPOSITPAYMENTTYPES as [CURRENTSALESDEPOSITPAYMENTTYPES]
where [CURRENTSALESDEPOSITPAYMENTTYPES].[ID] = [CSDPT].[ID]
) as [CURRENTPAYMENTMETHODS],
[CSDPT].[SALESDEPOSITTEMPLATENAME] as [SALESDEPOSITTEMPLATENAME]
from @CURRENTSALESDEPOSITPAYMENTTYPES as [CSDPT]
group by
[CSDPT].[SALESDEPOSITTEMPLATENAME],
[CSDPT].[ID]
for xml raw ('ITEM'), type, elements, root('SALESDEPOSITTEMPLATE'), BINARY BASE64
)
return 0;