USP_DATAFORMTEMPLATE_EDIT_SALESDEPOSITTEMPLATE_3

create procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SALESDEPOSITTEMPLATE_3
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @NAME nvarchar(100),
    @BANKACCOUNTID uniqueidentifier,
    @REFERENCECODE tinyint,
    @SALESCHANNELOPTIONCODE tinyint,
    @PAYMENTMETHODOPTIONCODE tinyint,
    @PAYMENTTYPE xml,
    @SALESCHANNEL xml,
  @CURRENCYOPTIONCODE tinyint,
  @CURRENCY 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
        [ID] = @ID, 
        [NAME] = @NAME,
        [BANKACCOUNTID] = @BANKACCOUNTID,
        [REFERENCECODE] = @REFERENCECODE,
        [SALESCHANNELOPTIONCODE] = @SALESCHANNELOPTIONCODE,
        [PAYMENTMETHODOPTIONCODE] = @PAYMENTMETHODOPTIONCODE,
    [CURRENCYOPTIONCODE] = @CURRENCYOPTIONCODE,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
    where ID = @ID;

    merge dbo.[SALESDEPOSITTEMPLATESALESCHANNEL] as T
        using dbo.UFN_SALESDEPOSITTEMPLATE_SALESCHANNELS_FROMITEMLISTXML(@SALESCHANNEL) as S
        on (T.SALESMETHODTYPECODE = S.SALESMETHODTYPECODE and T.SALESDEPOSITTEMPLATEID = @ID)
        when not matched by target and S.INCLUDE = 1 and @SALESCHANNELOPTIONCODE = 1
            then insert([ID], [SALESDEPOSITTEMPLATEID], [SALESMETHODTYPECODE], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED])
                values(newid(), @ID, S.SALESMETHODTYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
        when matched and (S.INCLUDE = 0 or @SALESCHANNELOPTIONCODE = 0)
            then delete;

    merge dbo.[SALESDEPOSITTEMPLATEPAYMENTMETHOD] as T
        using dbo.UFN_SALESDEPOSITTEMPLATE_PAYMENTMETHODS_FROMITEMLISTXML(@PAYMENTTYPE) as S
        on (
          T.SALESDEPOSITTEMPLATEID = @ID
            and T.PAYMENTMETHODCODE = S.PAYMENTMETHODCODE 
            and (T.CREDITTYPECODEID = S.CREDITTYPECODEID or (T.CREDITTYPECODEID is null and S.CREDITTYPECODEID is null))
            and (T.OTHERPAYMENTMETHODCODEID = S.OTHERPAYMENTMETHODCODEID or (T.OTHERPAYMENTMETHODCODEID is null and S.OTHERPAYMENTMETHODCODEID is null))
        )
        when not matched by target and S.INCLUDE = 1 and @PAYMENTMETHODOPTIONCODE = 1
            then insert([ID], [SALESDEPOSITTEMPLATEID], [PAYMENTMETHODCODE], [CREDITTYPECODEID], [OTHERPAYMENTMETHODCODEID],
                    [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED])
                values(newid(), @ID, S.PAYMENTMETHODCODE, S.CREDITTYPECODEID, S.OTHERPAYMENTMETHODCODEID,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
        when matched and (S.INCLUDE = 0 or @PAYMENTMETHODOPTIONCODE = 0)
            then delete;

  merge dbo.[SALESDEPOSITTEMPLATECURRENCY] as T
    using dbo.UFN_SALESDEPOSITTEMPLATE_CURRENCY_FROMITEMLISTXML(@CURRENCY) as S
    on (T.CURRENCYID = S.CURRENCYID and T.SALESDEPOSITTEMPLATEID = @ID)
    when not matched by target and S.INCLUDE = 1 and @CURRENCYOPTIONCODE = 1
      then insert([ID], [SALESDEPOSITTEMPLATEID], [CURRENCYID], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED])
        values(newid(), @ID, S.CURRENCYID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
    when matched and (S.INCLUDE = 0 or @CURRENCYOPTIONCODE = 0)
      then delete;

    if exists(select 1
        from dbo.SALESDEPOSITTEMPLATE T1
        left join dbo.SALESDEPOSITTEMPLATESALESCHANNEL C1 on T1.ID = C1.SALESDEPOSITTEMPLATEID
        left join dbo.SALESDEPOSITTEMPLATEPAYMENTMETHOD PM1 on T1.ID = PM1.SALESDEPOSITTEMPLATEID
      left join dbo.SALESDEPOSITTEMPLATECURRENCY TC1 on T1.ID = TC1.SALESDEPOSITTEMPLATEID
        inner join dbo.SALESDEPOSITTEMPLATE T2 on T1.ID != T2.ID
        left join dbo.SALESDEPOSITTEMPLATESALESCHANNEL C2 on T2.ID = C2.SALESDEPOSITTEMPLATEID
        left join dbo.SALESDEPOSITTEMPLATEPAYMENTMETHOD PM2 on T2.ID = PM2.SALESDEPOSITTEMPLATEID
      left join dbo.SALESDEPOSITTEMPLATECURRENCY TC2 on T2.ID = TC2.SALESDEPOSITTEMPLATEID
    left join dbo.SALESDEPOSITPROCESS SDP1 on SDP1.ID = T1.SALESDEPOSITPROCESSID
    left join dbo.SALESDEPOSITPROCESS SDP2 on SDP2.ID = T2.SALESDEPOSITPROCESSID
        where T1.ID = @ID and T1.INCLUDEINPROCESS = 1 and T2.INCLUDEINPROCESS = 1
      and (SDP1.ID is null or SDP2.ID = SDP1.ID)
            and ((T1.PAYMENTMETHODOPTIONCODE = 0 and (T2.PAYMENTMETHODOPTIONCODE = 0 or PM2.ID is not null))
                or (T2.PAYMENTMETHODOPTIONCODE = 0 and (T1.PAYMENTMETHODOPTIONCODE = 0 or PM1.ID is not null))
                or (PM1.PAYMENTMETHODCODE = PM2.PAYMENTMETHODCODE
                     and ((PM1.CREDITTYPECODEID is null and PM2.CREDITTYPECODEID is null) 
                        or (PM1.CREDITTYPECODEID = PM2.CREDITTYPECODEID))
                     and ((PM2.OTHERPAYMENTMETHODCODEID is null and PM2.OTHERPAYMENTMETHODCODEID is null) 
                        or (PM1.OTHERPAYMENTMETHODCODEID = PM2.OTHERPAYMENTMETHODCODEID))))
            and ((T1.SALESCHANNELOPTIONCODE = 0 and (T2.SALESCHANNELOPTIONCODE = 0 or C2.ID is not null))
                or (T2.SALESCHANNELOPTIONCODE = 0 and (T1.SALESCHANNELOPTIONCODE = 0 or C1.ID is not null))
                or (C1.SALESMETHODTYPECODE = C2.SALESMETHODTYPECODE))
      and ((T1.CURRENCYOPTIONCODE = 0 and (T2.CURRENCYOPTIONCODE = 0 or TC2.ID is not null))
        or (T2.CURRENCYOPTIONCODE = 0 and (T1.CURRENCYOPTIONCODE = 0 or TC1.ID is not null))
        or (TC1.CURRENCYID = TC2.CURRENCYID)))
        raiserror('BBERR_SALESDEPOSITTEMPLATE_CONFLICT', 16, 1)

    declare @isChannel bit = 0
    if (@SALESCHANNELOPTIONCODE = 0) or exists(select ID from dbo.[SALESDEPOSITTEMPLATESALESCHANNEL] where SALESDEPOSITTEMPLATEID = @ID)
        set @isChannel = 1
    declare @isMethod bit = 0
    if (@PAYMENTMETHODOPTIONCODE = 0) or exists(select ID from dbo.[SALESDEPOSITTEMPLATEPAYMENTMETHOD] where SALESDEPOSITTEMPLATEID = @ID)
        set @isMethod = 1
  declare @isCurrency bit = 0
  if (@CURRENCYOPTIONCODE = 0) or exists(select ID from dbo.[SALESDEPOSITTEMPLATECURRENCY] where SALESDEPOSITTEMPLATEID = @ID)
    set @isCurrency = 1
    if (@isChannel = 0 and @isMethod = 0)
        begin
            raiserror('BBERR_MISSING_ALL', 16, 1)
            return 1
        end
    else if (@isChannel = 0)
        begin
            raiserror('BBERR_MISSING_CHANNEL', 16, 1)
            return 1
        end
    else if (@isMethod = 0)
        begin
            raiserror('BBERR_MISSING_METHOD', 16, 1)
            return 1
        end
  if (@isCurrency = 0)
    begin
      raiserror('BBERR_MISSING_CURRENCY', 16, 1)
      return 1
    end
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0;