USP_DATAFORMTEMPLATE_EDITLOAD_SALESDEPOSITPROCESSPREPROCESS2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_SALESDEPOSITPROCESSPREPROCESS2
(
    @ID uniqueidentifier
    ,@DATALOADED bit = 0 output
    ,@TSLONG bigint = 0 output
    ,@NAME nvarchar(100) = null output
    ,@UNDEFINEDPAYMENTTEMPLATES xml = null output
    ,@DEPOSITDATEOPTIONCODE tinyint = null output
    ,@DEPOSITDATE date = null output
    ,@TEMPLATES xml = null output
    ,@TRANSACTIONDAYCOUNT integer = null output
  ,@PAYMENTDATEOPTIONCODE tinyint = null output
  ,@PAYMENTDATE date = null output
  ,@CONTEXTID uniqueidentifier = null output
  ,@TRANSACTIONCURRENCYCOUNT integer = null output
)
as

    set nocount on;

    set @DATALOADED = 0
    set @TSLONG = 0

    exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_SALESDEPOSITPROCESS2 @ID=@ID, @TEMPLATES=@TEMPLATES output;

    create table #AllTransactions(
    PAYMENTMETHODCODE tinyint,
    PAYMENTMETHOD nvarchar(100) COLLATE database_default,
    SALESMETHODCODE tinyint,
    CREDITTYPECODEID uniqueidentifier,
    OTHERPAYMENTMETHODCODEID uniqueidentifier,
    TRANSACTIONCURRENCYID uniqueidentifier,
    TRANSACTIONDATE date)

  insert into #AllTransactions
        select
            PAYMENTMETHODCODE 
            ,PAYMENTMETHOD
            ,SALESMETHODCODE
            ,CREDITTYPECODEID
            ,OTHERPAYMENTMETHODCODEID
            ,TRANSACTIONCURRENCYID
            ,cast(TRANSACTIONDATE as date)
        from dbo.UFN_SALESDEPOSITPROCESS_GETACCOUNTSYSTEMTRANSACTIONS(@ID)

    union all

    select
          PAYMENTMETHODCODE,
          PAYMENTMETHOD,
          SALESMETHODCODE,
          null as CREDITTYPECODEID,
          null as OTHERPAYMENTMETHODCODEID,
          (select top 1 ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1) as TRANSACTIONCURRENCYID
          ,cast (TRANSACTIONDATE as date)
      from dbo.UFN_SALESDEPOSITPROCESS_TEMPLATES_GETOVERSHORTS(@ID)


    select
        @DATALOADED = 1
        ,@TSLONG = P.TSLONG
        ,@NAME = P.NAME
        ,@DEPOSITDATEOPTIONCODE = P.DEPOSITDATEOPTIONCODE
        ,@DEPOSITDATE = P.DEPOSITDATE
        ,@PAYMENTDATEOPTIONCODE = P.PAYMENTDATEOPTIONCODE
        ,@PAYMENTDATE = P.PAYMENTDATE
        ,@CONTEXTID = P.ID
        ,@UNDEFINEDPAYMENTTEMPLATES = (
            select 
                TRANSACTIONS.PAYMENTMETHODCODE 
                ,TRANSACTIONS.PAYMENTMETHOD
                ,TRANSACTIONS.SALESMETHODCODE
                ,TRANSACTIONS.CREDITTYPECODEID
                ,TRANSACTIONS.OTHERPAYMENTMETHODCODEID
                ,TRANSACTIONS.TRANSACTIONCURRENCYID
                ,dbo.UFN_CURRENCY_GETDESCRIPTION(TRANSACTIONS.TRANSACTIONCURRENCYID) as TRANSACTIONCURRENCY
            from (
                select distinct
                PAYMENTS.PAYMENTMETHODCODE 
                ,PAYMENTS.PAYMENTMETHOD
                ,PAYMENTS.SALESMETHODCODE
                ,PAYMENTS.CREDITTYPECODEID
                ,PAYMENTS.OTHERPAYMENTMETHODCODEID
                ,PAYMENTS.TRANSACTIONCURRENCYID
                from (
                    select distinct
                        T.PAYMENTMETHODCODE
                        ,T.PAYMENTMETHOD
                        ,T.SALESMETHODCODE
                        ,T.CREDITTYPECODEID
                        ,T.OTHERPAYMENTMETHODCODEID
                        ,T.TRANSACTIONCURRENCYID
                    from #AllTransactions T
                ) PAYMENTS
                where not exists(select 1
                    from dbo.SALESDEPOSITTEMPLATE T
                    left join dbo.SALESDEPOSITTEMPLATEPAYMENTMETHOD PM on T.ID = PM.SALESDEPOSITTEMPLATEID
                    left join dbo.SALESDEPOSITTEMPLATESALESCHANNEL C on T.ID = C.SALESDEPOSITTEMPLATEID
                    left join dbo.SALESDEPOSITTEMPLATECURRENCY CU on T.ID = cu.SALESDEPOSITTEMPLATEID
                    where T.SALESDEPOSITPROCESSID = @ID
                        and (T.SALESCHANNELOPTIONCODE = 0 or C.SALESMETHODTYPECODE = PAYMENTS.SALESMETHODCODE)
                        and (T.CURRENCYOPTIONCODE = 0 or CU.CURRENCYID = PAYMENTS.TRANSACTIONCURRENCYID)
                        and (T.PAYMENTMETHODOPTIONCODE = 0 or 
                            (PM.PAYMENTMETHODCODE = PAYMENTS.PAYMENTMETHODCODE
                                and ((PM.CREDITTYPECODEID is null and PAYMENTS.CREDITTYPECODEID is null)
                                    or (PM.CREDITTYPECODEID = PAYMENTS.CREDITTYPECODEID))
                                and ((PM.OTHERPAYMENTMETHODCODEID = PAYMENTS.OTHERPAYMENTMETHODCODEID)
                  or (PM.OTHERPAYMENTMETHODCODEID is null and PAYMENTS.OTHERPAYMENTMETHODCODEID is null)))))) TRANSACTIONS
                        for xml raw ('ITEM'), type, elements, root('UNDEFINEDPAYMENTTEMPLATES'), binary base64)
        ,@TRANSACTIONDAYCOUNT = (select COUNT(TRANS.[DATE])
            from (
                select distinct T.TRANSACTIONDATE [DATE], T.TRANSACTIONCURRENCYID
                from #AllTransactions T) TRANS)
    ,@TRANSACTIONCURRENCYCOUNT = (select COUNT(TRANS.TRANSACTIONCURRENCYID)
            from (
                select distinct T.TRANSACTIONCURRENCYID
                from #AllTransactions T) TRANS)
    from dbo.SALESDEPOSITPROCESS P
    where P.ID = @ID

  drop table #AllTransactions

    return 0;