USP_DATAFORMTEMPLATE_ADD_SALESDEPOSITPROCESS2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SALESDEPOSITPROCESS2
(
    @ID uniqueidentifier = null output
    ,@CHANGEAGENTID uniqueidentifier = null
    ,@NAME nvarchar(100)
    ,@DESCRIPTION nvarchar(255) = null
    ,@DEPOSITDATEOPTIONCODE tinyint = 0
    ,@DEPOSITDATE date = null
  ,@PAYMENTDATEOPTIONCODE tinyint = 1
  ,@PAYMENTDATE date = null
  ,@PDACCOUNTSYSTEMID uniqueidentifier = null
  ,@TEMPLATES 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
  if @DEPOSITDATEOPTIONCODE != 3
            set @DEPOSITDATE = null;

  declare @GROUPBYDATE bit = 0
  if @DEPOSITDATEOPTIONCODE = 0
    set @GROUPBYDATE = 1;

  if exists (select 1 from dbo.SALESDEPOSITPROCESS where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
    raiserror('BBERR_SALESDEPOSITPROCESS_PDACCOUNTSYSTEMUNIQUE', 13, 1)

    insert into dbo.SALESDEPOSITPROCESS
        (ID, NAME, DESCRIPTION, DEPOSITDATEOPTIONCODE, DEPOSITDATE, 
      PAYMENTDATEOPTIONCODE, PAYMENTDATE, GROUPBYDATE, ISRECONCILEDPROCESS, PDACCOUNTSYSTEMID,
      ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    values
        (@ID, @NAME, @DESCRIPTION, @DEPOSITDATEOPTIONCODE, @DEPOSITDATE, 
      @PAYMENTDATEOPTIONCODE, @PAYMENTDATE, @GROUPBYDATE, 1, @PDACCOUNTSYSTEMID,
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

  insert into dbo.SALESDEPOSITTEMPLATE
  (ID, NAME, SALESDEPOSITPROCESSID, SALESCHANNELOPTIONCODE, PAYMENTMETHODOPTIONCODE, INCLUDEINPROCESS, BANKACCOUNTID, CURRENCYOPTIONCODE,
    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  select
    isnull(SDT.ID, newID()), SDT.NAME, @ID, SDT.SALESCHANNELOPTIONCODE, SDT.PAYMENTMETHODOPTIONCODE, SDT.INCLUDEINPROCESS, SDT.BANKACCOUNTID, SDT.CURRENCYOPTIONCODE,
    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
  from dbo.UFN_SALESDEPOSITPROCESS_TEMPLATE_FROMITEMLISTXML(@TEMPLATES) SDT

  insert into dbo.SALESDEPOSITTEMPLATEPAYMENTMETHOD
  (ID, SALESDEPOSITTEMPLATEID, PAYMENTMETHODCODE, CREDITTYPECODEID, OTHERPAYMENTMETHODCODEID,
    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  select
    isnull(SDTPM.ID, newid()), SDTPM.SALESDEPOSITTEMPLATEID, SDTPM.PAYMENTMETHODCODE, SDTPM.CREDITTYPECODEID, SDTPM.OTHERPAYMENTMETHODCODEID,
    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
  from dbo.UFN_SALESDEPOSITPROCESS_TEMPLATEPAYMENTMETHOD_FROMITEMLISTXML(@TEMPLATES) SDTPM
  inner join dbo.SALESDEPOSITTEMPLATE SDT on SDT.ID = SDTPM.SALESDEPOSITTEMPLATEID
  where SDTPM.INCLUDE = 1 and SDT.PAYMENTMETHODOPTIONCODE = 1

  insert into dbo.SALESDEPOSITTEMPLATESALESCHANNEL
  (ID, SALESDEPOSITTEMPLATEID, SALESMETHODTYPECODE,
    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  select
    isnull(SDTSC.ID, newid()), SDTSC.SALESDEPOSITTEMPLATEID, SDTSC.SALESMETHODTYPECODE,
    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
  from dbo.UFN_SALESDEPOSITPROCESS_TEMPLATESALESCHANNEL_FROMITEMLISTXML(@TEMPLATES) SDTSC
  inner join dbo.SALESDEPOSITTEMPLATE SDT on SDT.ID = SDTSC.SALESDEPOSITTEMPLATEID
  where SDTSC.INCLUDE = 1 and SDT.SALESCHANNELOPTIONCODE = 1

  insert into dbo.SALESDEPOSITTEMPLATECURRENCY
  (ID, SALESDEPOSITTEMPLATEID, CURRENCYID,
    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  select
    isnull(SDTC.ID, newid()), SDTC.SALESDEPOSITTEMPLATEID, SDTC.CURRENCYID,
    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
  from dbo.UFN_SALESDEPOSITPROCESS_TEMPLATECURRENCY_FROMITEMLISTXML(@TEMPLATES) SDTC
  inner join dbo.SALESDEPOSITTEMPLATE SDT on SDT.ID = SDTC.SALESDEPOSITTEMPLATEID
  where SDTC.INCLUDE = 1 and SDT.CURRENCYOPTIONCODE = 1

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0