USP_DATAFORMTEMPLATE_ADD_SALESDEPOSITTEMPLATE_3

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SALESDEPOSITTEMPLATE_3
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
  @SALESDEPOSITPROCESSID uniqueidentifier,
    @NAME nvarchar(100) = '',
    @BANKACCOUNTID uniqueidentifier = null,
    @REFERENCECODE tinyint = 0,
    @SALESCHANNELOPTIONCODE tinyint = 0,
    @PAYMENTMETHODOPTIONCODE tinyint = 0,
    @PAYMENTTYPE xml = null,
    @SALESCHANNEL xml = null,
  @CURRENCYOPTIONCODE tinyint = 0,
  @CURRENCY xml = null
)
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
    insert into dbo.[SALESDEPOSITTEMPLATE]
    (
        [ID],
        [NAME],
    [SALESDEPOSITPROCESSID],
        [BANKACCOUNTID],
        [REFERENCECODE],
        [SALESCHANNELOPTIONCODE],
        [PAYMENTMETHODOPTIONCODE],
    [CURRENCYOPTIONCODE],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED]
    )
    values
    (
        @ID, 
        @NAME,
    @SALESDEPOSITPROCESSID,
        @BANKACCOUNTID,
        @REFERENCECODE,
        @SALESCHANNELOPTIONCODE,
        @PAYMENTMETHODOPTIONCODE,
    @CURRENCYOPTIONCODE,
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CURRENTDATE, 
        @CURRENTDATE
    )

    --populate the sales channels with selected sales channels
    if @SALESCHANNEL is not null and @SALESCHANNELOPTIONCODE = 1
    begin
        insert into dbo.[SALESDEPOSITTEMPLATESALESCHANNEL]
        (
            [ID], [SALESDEPOSITTEMPLATEID], [SALESMETHODTYPECODE],
            [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED]
        )
        select
            isnull(SC.ID, newid()), @ID, SALESMETHODTYPECODE,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from dbo.UFN_SALESDEPOSITTEMPLATE_SALESCHANNELS_FROMITEMLISTXML(@SALESCHANNEL) SC
        where INCLUDE = 1 and @SALESCHANNELOPTIONCODE = 1
    end

    --populate the payment types with selected payment types
    if @PAYMENTTYPE is not null and @PAYMENTMETHODOPTIONCODE = 1
    begin
        insert into dbo.[SALESDEPOSITTEMPLATEPAYMENTMETHOD]
        (
            [ID], [SALESDEPOSITTEMPLATEID], [PAYMENTMETHODCODE], [CREDITTYPECODEID], [OTHERPAYMENTMETHODCODEID],
            [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED]
        )
        select
            isnull(PM.ID, newid()), @ID, PAYMENTMETHODCODE, CREDITTYPECODEID, OTHERPAYMENTMETHODCODEID,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from dbo.UFN_SALESDEPOSITTEMPLATE_PAYMENTMETHODS_FROMITEMLISTXML(@PAYMENTTYPE) PM
        where INCLUDE = 1 and @PAYMENTMETHODOPTIONCODE = 1
    end

  --populate the currencies with selected currencies
  if @CURRENCY is not null and @CURRENCYOPTIONCODE = 1
  begin
    insert into dbo.[SALESDEPOSITTEMPLATECURRENCY]
    (
            [ID], [SALESDEPOSITTEMPLATEID], [CURRENCYID],
            [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED]
        )
    select
            isnull(C.ID, newid()), @ID, CURRENCYID,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from UFN_SALESDEPOSITTEMPLATE_CURRENCY_FROMITEMLISTXML(@CURRENCY) C
    where INCLUDE = 1 and @CURRENCYOPTIONCODE = 1
  end

    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;