USP_DATAFORMTEMPLATE_EDIT_SALESDEPOSITTEMPLATE_PRELOAD
The load procedure used by the edit dataform template "Sales Deposit Template 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. |
| @PAYMENTTYPE | xml | INOUT | Payment types |
| @DEPOSITDATE | nvarchar(10) | INOUT | Deposit date |
| @STATUS | nvarchar(10) | INOUT | Status |
| @NAME | nvarchar(100) | INOUT | Name |
| @BANKACCOUNTID | uniqueidentifier | INOUT | Bank account |
| @POSTSTATUSCODE | tinyint | INOUT | Post status |
| @REFERENCECODE | tinyint | INOUT | Reference |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SALESDEPOSITTEMPLATE_PRELOAD
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@TSLONG bigint = 0 output,
@PAYMENTTYPE xml = null output,
@DEPOSITDATE nvarchar(10) = null output,
@STATUS nvarchar(10) = null output,
@NAME nvarchar(100) = null output,
@BANKACCOUNTID uniqueidentifier = null output,
@POSTSTATUSCODE tinyint = null output,
@REFERENCECODE tinyint = null output
)
as
set nocount on;
set @DEPOSITDATE = 'Today'
set @STATUS = 'Locked'
declare @SALESDEPOSITPROCESSID uniqueidentifier
select
@DATALOADED = 1,
@SALESDEPOSITPROCESSID = [SALESDEPOSITPROCESSID],
@NAME = [NAME],
@BANKACCOUNTID = [BANKACCOUNTID],
@REFERENCECODE = [REFERENCECODE],
@TSLONG = [TSLONG]
from dbo.[SALESDEPOSITTEMPLATE]
where [ID] = @ID;
--The payment types currently in a sales deposit template (other than this one) in this process
declare @CURRENTSALESDEPOSITPAYMENTTYPES table (
[SALESDEPOSITTEMPLATENAME] nvarchar(100),
[PAYMENTMETHODCODE] tinyint,
[CREDITTYPECODEID] uniqueidentifier,
[OTHERPAYMENTMETHODCODEID] uniqueidentifier
)
insert into @CURRENTSALESDEPOSITPAYMENTTYPES
select
[SALESDEPOSITTEMPLATE].[NAME],
[SALESDEPOSITTEMPLATEPAYMENTMETHOD].[PAYMENTMETHODCODE],
[SALESDEPOSITTEMPLATEPAYMENTMETHOD].[CREDITTYPECODEID],
[SALESDEPOSITTEMPLATEPAYMENTMETHOD].[OTHERPAYMENTMETHODCODEID]
from dbo.[SALESDEPOSITTEMPLATE]
inner join dbo.[SALESDEPOSITTEMPLATEPAYMENTMETHOD]
on [SALESDEPOSITTEMPLATE].[ID] = [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[SALESDEPOSITTEMPLATEID]
where
[SALESDEPOSITTEMPLATE].[SALESDEPOSITPROCESSID] = @SALESDEPOSITPROCESSID and
[SALESDEPOSITTEMPLATE].[ID] <> @ID;
--The payment types temp table selectable in form
declare @PAYMENTTYPES table (
[ISAVAILABLE] bit,
[PAYMENTMETHOD] nvarchar(100),
[PAYMENTMETHODCODE] tinyint,
[CREDITTYPECODEID] uniqueidentifier,
[HASOTHERDEPOSIT] bit,
[SALESDEPOSITTEMPLATENAME] nvarchar(100),
[OTHERPAYMENTMETHODCODEID] uniqueidentifier
)
insert into @PAYMENTTYPES
select
case when 0 in (
select [PAYMENTMETHODCODE]
from dbo.[SALESDEPOSITTEMPLATEPAYMENTMETHOD]
where [SALESDEPOSITTEMPLATEID] = @ID
) then 1
else 0
end as [ISAVAILABLE],
dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION(0) as [PAYMENTMETHOD],
0 as [PAYMENTMETHODCODE],
null as [CREDITTYPECODEID],
case when exists (
select [CSDPT].[SALESDEPOSITTEMPLATENAME]
from @CURRENTSALESDEPOSITPAYMENTTYPES as [CSDPT]
where [CSDPT].[PAYMENTMETHODCODE] = 0
) then 1
else 0
end as [HASOTHERDEPOSIT],
(
select top 1 [CSDPT].[SALESDEPOSITTEMPLATENAME]
from @CURRENTSALESDEPOSITPAYMENTTYPES as [CSDPT]
where [CSDPT].[PAYMENTMETHODCODE] = 0
) as [SALESDEPOSITTEMPLATENAME],
null as [OTHERPAYMENTMETHODCODEID]
union all
select
case when 1 in (
select [PAYMENTMETHODCODE]
from dbo.[SALESDEPOSITTEMPLATEPAYMENTMETHOD]
where [SALESDEPOSITTEMPLATEID] = @ID
) then 1
else 0
end as [ISAVAILABLE],
dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION(1) as [PAYMENTMETHOD],
1 as [PAYMENTMETHODCODE],
null as [CREDITTYPECODEID],
case when exists (
select [CSDPT].[SALESDEPOSITTEMPLATENAME]
from @CURRENTSALESDEPOSITPAYMENTTYPES as [CSDPT]
where [CSDPT].[PAYMENTMETHODCODE] = 1
) then 1
else 0
end as [HASOTHERDEPOSIT],
(
select top 1 [CSDPT].[SALESDEPOSITTEMPLATENAME]
from @CURRENTSALESDEPOSITPAYMENTTYPES as [CSDPT]
where [CSDPT].[PAYMENTMETHODCODE] = 1
) as [SALESDEPOSITTEMPLATENAME],
null as [OTHERPAYMENTMETHODCODEID]
union all
select
case when [CREDITTYPECODE].[ID] in (
select [CREDITTYPECODEID]
from dbo.[SALESDEPOSITTEMPLATEPAYMENTMETHOD]
where
[SALESDEPOSITTEMPLATEID] = @ID and
[PAYMENTMETHODCODE] = 2
) then 1
else 0
end as [ISAVAILABLE],
[CREDITTYPECODE].[DESCRIPTION] as [PAYMENTMETHOD],
2 as [PAYMENTMETHODCODE],
[CREDITTYPECODE].[ID] as [CREDITTYPECODEID],
case when [CREDITTYPECODE].[ID] in (
select [CSDPT].[CREDITTYPECODEID]
from @CURRENTSALESDEPOSITPAYMENTTYPES as [CSDPT]
where [CSDPT].[PAYMENTMETHODCODE] = 2
) then 1
else 0
end as [HASOTHERDEPOSIT],
(
select top 1 [CSDPT].[SALESDEPOSITTEMPLATENAME]
from @CURRENTSALESDEPOSITPAYMENTTYPES as [CSDPT]
where
[CSDPT].[PAYMENTMETHODCODE] = 2 and
[CSDPT].[CREDITTYPECODEID] = [CREDITTYPECODE].[ID]
) as [SALESDEPOSITTEMPLATENAME],
null as [OTHERPAYMENTMETHODCODEID]
from dbo.[CREDITTYPECODE]
where [ACTIVE] = 1
union all
select
case when [OTHERPAYMENTMETHODCODE].[ID] in (
select [OTHERPAYMENTMETHODCODEID]
from dbo.[SALESDEPOSITTEMPLATEPAYMENTMETHOD]
where
[SALESDEPOSITTEMPLATEID] = @ID and
[PAYMENTMETHODCODE] = 10
) then 1
else 0
end as [ISAVAILABLE],
[OTHERPAYMENTMETHODCODE].[DESCRIPTION] as [PAYMENTMETHOD],
10 as [PAYMENTMETHODCODE],
null as [CREDITTYPECODEID],
case when [OTHERPAYMENTMETHODCODE].[ID] in (
select [CSDPT].[OTHERPAYMENTMETHODCODEID]
from @CURRENTSALESDEPOSITPAYMENTTYPES as [CSDPT]
where [CSDPT].[PAYMENTMETHODCODE] = 10
) then 1
else 0
end as [HASOTHERDEPOSIT],
(
select top 1 [CSDPT].[SALESDEPOSITTEMPLATENAME]
from @CURRENTSALESDEPOSITPAYMENTTYPES as [CSDPT]
where
[CSDPT].[PAYMENTMETHODCODE] = 10 and
[CSDPT].[OTHERPAYMENTMETHODCODEID] = [OTHERPAYMENTMETHODCODE].[ID]
) as [SALESDEPOSITTEMPLATENAME],
[OTHERPAYMENTMETHODCODE].[ID] as [OTHERPAYMENTMETHODCODEID]
from dbo.[OTHERPAYMENTMETHODCODE]
where [ACTIVE] = 1
and [ID] not in (select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD);
set @PAYMENTTYPE =
(
select
[PAYMENTTYPES].[ISAVAILABLE],
[PAYMENTTYPES].[PAYMENTMETHOD],
[PAYMENTTYPES].[PAYMENTMETHODCODE],
[PAYMENTTYPES].[CREDITTYPECODEID],
[PAYMENTTYPES].[HASOTHERDEPOSIT],
[PAYMENTTYPES].[SALESDEPOSITTEMPLATENAME],
[PAYMENTTYPES].[OTHERPAYMENTMETHODCODEID]
from @PAYMENTTYPES as [PAYMENTTYPES]
for xml raw ('ITEM'), type, elements, root('PAYMENTTYPE'), BINARY BASE64
)
return 0;