USP_DATAFORMTEMPLATE_EDIT_SALESDEPOSITTEMPLATE
The save 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 indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @NAME | nvarchar(100) | IN | Name |
| @BANKACCOUNTID | uniqueidentifier | IN | Bank account |
| @POSTSTATUSCODE | tinyint | IN | Post status |
| @REFERENCECODE | tinyint | IN | Reference |
| @PAYMENTTYPE | xml | IN | Payment types |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SALESDEPOSITTEMPLATE
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@NAME nvarchar(100),
@BANKACCOUNTID uniqueidentifier,
@POSTSTATUSCODE tinyint,
@REFERENCECODE tinyint,
@PAYMENTTYPE xml
)
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
set @CURRENTDATE = getdate()
begin try
update dbo.[SALESDEPOSITTEMPLATE]
set
[NAME] = @NAME,
[BANKACCOUNTID] = @BANKACCOUNTID,
[REFERENCECODE] = @REFERENCECODE,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where [ID] = @ID
--remove payment methods that have been unselected
declare paymenttypes_cursor cursor LOCAL FAST_FORWARD for
select [ID]
from dbo.[SALESDEPOSITTEMPLATEPAYMENTMETHOD]
where
[SALESDEPOSITTEMPLATEID] = @ID and
not exists (
select T.paymenttype.value('(PAYMENTMETHODCODE)[1]', 'tinyint')
from @PAYMENTTYPE.nodes('/PAYMENTTYPE/ITEM') T(paymenttype)
where
T.paymenttype.value('(ISAVAILABLE)[1]', 'bit') = 1 and
T.paymenttype.value('(PAYMENTMETHODCODE)[1]', 'tinyint') = [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[PAYMENTMETHODCODE] and
(T.paymenttype.value('(PAYMENTMETHODCODE)[1]', 'tinyint') in (0,1) or
(T.paymenttype.value('(PAYMENTMETHODCODE)[1]', 'tinyint') = 2 and T.paymenttype.value('(CREDITTYPECODEID)[1]', 'uniqueidentifier') = [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[CREDITTYPECODEID]) or
(T.paymenttype.value('(PAYMENTMETHODCODE)[1]', 'tinyint') = 10 and T.paymenttype.value('(OTHERPAYMENTMETHODCODEID)[1]', 'uniqueidentifier') = [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[OTHERPAYMENTMETHODCODEID]) )
)
open paymenttypes_cursor
declare @SALESDEPOSITTEMPLATEPAYMENTMETHODID uniqueidentifier = null
fetch next from paymenttypes_cursor
into @SALESDEPOSITTEMPLATEPAYMENTMETHODID
while @@FETCH_STATUS = 0
begin
exec dbo.USP_SALESDEPOSITTEMPLATEPAYMENTMETHOD_DELETEBYID_WITHCHANGEAGENTID @SALESDEPOSITTEMPLATEPAYMENTMETHODID, @CHANGEAGENTID
fetch next from paymenttypes_cursor
into @SALESDEPOSITTEMPLATEPAYMENTMETHODID
end
close paymenttypes_cursor;
deallocate paymenttypes_cursor;
--populate the sales deposit templates with newly selected payment types
if @PAYMENTTYPE is not null
begin
insert into dbo.[SALESDEPOSITTEMPLATEPAYMENTMETHOD]
(
[ID],
[SALESDEPOSITTEMPLATEID],
[PAYMENTMETHODCODE],
[CREDITTYPECODEID],
[OTHERPAYMENTMETHODCODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
@ID,
T.paymenttype.value('(PAYMENTMETHODCODE)[1]', 'tinyint'),
case when (T.paymenttype.value('(PAYMENTMETHODCODE)[1]', 'tinyint') = 2) then
T.paymenttype.value('(CREDITTYPECODEID)[1]', 'uniqueidentifier')
else null end,
case when (T.paymenttype.value('(PAYMENTMETHODCODE)[1]', 'tinyint') = 10) then
T.paymenttype.value('(OTHERPAYMENTMETHODCODEID)[1]', 'uniqueidentifier')
else null end,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @PAYMENTTYPE.nodes('/PAYMENTTYPE/ITEM') T(paymenttype)
where
T.paymenttype.value('(ISAVAILABLE)[1]', 'bit') = 1 and
not exists (
select [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[ID]
from dbo.[SALESDEPOSITTEMPLATEPAYMENTMETHOD]
where
[SALESDEPOSITTEMPLATEPAYMENTMETHOD].[SALESDEPOSITTEMPLATEID] = @ID and
T.paymenttype.value('(PAYMENTMETHODCODE)[1]', 'tinyint') = [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[PAYMENTMETHODCODE] and
(T.paymenttype.value('(PAYMENTMETHODCODE)[1]', 'tinyint') in (0,1) or
(T.paymenttype.value('(PAYMENTMETHODCODE)[1]', 'tinyint') = 2 and T.paymenttype.value('(CREDITTYPECODEID)[1]', 'uniqueidentifier') = [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[CREDITTYPECODEID]) or
(T.paymenttype.value('(PAYMENTMETHODCODE)[1]', 'tinyint') = 10 and T.paymenttype.value('(OTHERPAYMENTMETHODCODEID)[1]', 'uniqueidentifier') = [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[OTHERPAYMENTMETHODCODEID])
)
)
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;