USP_DATAFORMTEMPLATE_VIEW_SALESDEPOSITTEMPLATE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SALESDEPOSITTEMPLATE
(
    @ID uniqueidentifier
    ,@DATALOADED bit = 0 output
    ,@NAME nvarchar(100) = null output
    ,@SALESCHANNELOPTIONCODE tinyint = null output
    ,@PAYMENTMETHODOPTIONCODE tinyint = null output
    ,@SALESCHANNELS xml = null output
    ,@PAYMENTMETHODS xml = null output
    ,@CONFLICTS xml = null output
  ,@BANKACCOUNTNAME nvarchar(100) = null output
  ,@CURRENCYOPTIONCODE tinyint = null output
  ,@CURRENCIES xml = null output
)
as
    set nocount on;

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

    -- populate the output parameters, which correspond to fields on the form.  Note that
    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
    -- will display a "no data loaded" message.
    select @DATALOADED = 1
        ,@NAME = T.NAME
        ,@SALESCHANNELOPTIONCODE = T.SALESCHANNELOPTIONCODE
        ,@PAYMENTMETHODOPTIONCODE = T.PAYMENTMETHODOPTIONCODE
    ,@CURRENCYOPTIONCODE = T.CURRENCYOPTIONCODE
    ,@BANKACCOUNTNAME = BA.ACCOUNTNAME
        ,@SALESCHANNELS = (select C.SALESMETHODTYPECODE, C.SALESMETHODTYPE
            from dbo.SALESDEPOSITTEMPLATESALESCHANNEL C 
            where C.SALESDEPOSITTEMPLATEID = @ID
            for xml raw ('ITEM'), type, elements, root('SALESCHANNELS'), binary base64)
    ,@CURRENCIES = (select SDTC.CURRENCYID, dbo.UFN_CURRENCY_GETDESCRIPTION(C.ID) as CURRENCY
      from dbo.SALESDEPOSITTEMPLATECURRENCY SDTC
        inner join dbo.CURRENCY C on SDTC.CURRENCYID = C.ID
      where SDTC.SALESDEPOSITTEMPLATEID = @ID
      order by C.NAME
      for xml raw ('ITEM'), type, elements, root('CURRENCIES'), binary base64)
        ,@PAYMENTMETHODS = (select PM.PAYMENTMETHODCODE
                ,COALESCE(OC.DESCRIPTION, CC.DESCRIPTION, PM.PAYMENTMETHOD) PAYMENTMETHOD
                ,PM.CREDITTYPECODEID
                ,PM.OTHERPAYMENTMETHODCODEID
            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 = @ID
            for xml raw ('ITEM'), type, elements, root('PAYMENTMETHODS'), binary base64)
        ,@CONFLICTS = (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, root('CONFLICTS'), binary base64)
    from dbo.SALESDEPOSITTEMPLATE T
  inner join dbo.BANKACCOUNT BA on BA.ID = T.BANKACCOUNTID
    where T.ID = @ID

    return 0;