USP_DATAFORMTEMPLATE_EDITLOAD_SALESDEPOSITPROCESS2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_SALESDEPOSITPROCESS2
(
    @ID uniqueidentifier
    ,@DATALOADED bit = 0 output
    ,@TSLONG bigint = 0 output
    ,@NAME nvarchar(100) = null output
    ,@DESCRIPTION nvarchar(255) = null output
    ,@DEPOSITDATEOPTIONCODE tinyint = null output
    ,@DEPOSITDATE date = null output
    ,@TEMPLATES xml = null output
  ,@PAYMENTDATEOPTIONCODE tinyint = null output
  ,@PAYMENTDATE date = null output
  ,@ACCOUNTSYSTEMNAME nvarchar(100) = null output
)
as

    set nocount on;

    -- be sure to set these, in case the select returns no rows
    set @DATALOADED = 0
    set @TSLONG = 0

    select 
        @DATALOADED = 1
        ,@NAME = P.NAME
        ,@DESCRIPTION = P.DESCRIPTION
        ,@TSLONG = P.TSLONG
        ,@DEPOSITDATEOPTIONCODE = P.DEPOSITDATEOPTIONCODE
        ,@DEPOSITDATE = P.DEPOSITDATE
    ,@PAYMENTDATEOPTIONCODE = P.PAYMENTDATEOPTIONCODE
    ,@PAYMENTDATE = P.PAYMENTDATE
    ,@ACCOUNTSYSTEMNAME = PDAS.NAME
        ,@TEMPLATES = (select T.ID
            ,T.NAME
            ,T.INCLUDEINPROCESS
      ,dbo.UFN_BANKACCOUNT_GETACCOUNTNAME(T.BANKACCOUNTID) as BANKACCOUNTNAME
            ,T.SALESCHANNELOPTIONCODE
            ,T.PAYMENTMETHODOPTIONCODE
            ,case when T.SALESCHANNELOPTIONCODE = 0 then T.SALESCHANNELOPTION
                else (select dbo.UDA_BUILDLIST(C.SALESMETHODTYPE)
                    from dbo.SALESDEPOSITTEMPLATESALESCHANNEL C 
                    where C.SALESDEPOSITTEMPLATEID = T.ID)
                end as SALESCHANNELS
            ,case when T.PAYMENTMETHODOPTIONCODE = 0 then T.PAYMENTMETHODOPTION
                else (select dbo.UDA_BUILDLIST(COALESCE(OC.DESCRIPTION, CC.DESCRIPTION, PM.PAYMENTMETHOD))
                    from dbo.SALESDEPOSITTEMPLATEPAYMENTMETHOD PM
                    left join dbo.CREDITTYPECODE CC on CC.ID = PM.CREDITTYPECODEID
                    left join dbo.OTHERPAYMENTMETHODCODE OC on OC.ID = PM.OTHERPAYMENTMETHODCODEID
                    where PM.SALESDEPOSITTEMPLATEID = T.ID)
                end as PAYMENTMETHODS
      ,case when T.CURRENCYOPTIONCODE = 0 then T.CURRENCYOPTION
        else (select dbo.UDA_BUILDLIST(dbo.UFN_CURRENCY_GETDESCRIPTION(C.ID))
          from dbo.SALESDEPOSITTEMPLATECURRENCY SDTC
            inner join dbo.CURRENCY C on C.ID = SDTC.CURRENCYID
          where SDTC.SALESDEPOSITTEMPLATEID = T.ID)
        end as CURRENCIES
            ,(select distinct T2.ID, T2.NAME
                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
                where T1.ID = T.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))
                for xml raw ('ITEM'), type, elements, binary base64) as CONFLICTS
        from dbo.SALESDEPOSITTEMPLATE T
    where (T.SALESDEPOSITPROCESSID = @ID or @ID is null)
        order by T.NAME asc
        for xml raw ('ITEM'), type, elements, root('TEMPLATES'), binary base64)
    from dbo.SALESDEPOSITPROCESS P
  left join dbo.PDACCOUNTSYSTEM PDAS on PDAS.ID = P.PDACCOUNTSYSTEMID
    where P.ID = @ID

    return 0;