USP_SALESDEPOSITPROCESS_GETSALESDEPOSITTEMPLATEPARAMETERS_2

            CREATE procedure dbo.USP_SALESDEPOSITPROCESS_GETSALESDEPOSITTEMPLATEPARAMETERS_2
            (
                @ID uniqueidentifier = null
            )    
            as begin

        declare @ISRECONCILEDPROCESS bit = null
        declare @ISGROUPEDBYDATE bit = null
        declare @PROCESSNAME nvarchar(100) = null
        declare @PAYMENTDATE date = null
        select
            @ISRECONCILEDPROCESS = [ISRECONCILEDPROCESS],
            @ISGROUPEDBYDATE = [GROUPBYDATE],
            @PROCESSNAME = NAME,
            @PAYMENTDATE = dbo.UFN_SALESDEPOSITPROCESS_COMPUTEPAYMENTDATE(PAYMENTDATEOPTIONCODE, PAYMENTDATE)
        from dbo.[SALESDEPOSITPROCESS]
        where [ID] = @ID

        declare @ALLOWGLDISTRIBUTIONS bit;
        select @ALLOWGLDISTRIBUTIONS = ALLOWGLDISTRIBUTIONS from dbo.UFN_PDACCOUNTSYSTEM_DEFAULTORSYSTEM();

        select
            [SALESDEPOSITTEMPLATE].[ID],
            [SALESDEPOSITTEMPLATE].[NAME],
            [SALESDEPOSITTEMPLATE].[BANKACCOUNTID],
            cast([PAYMENTS].[POSTSTATUSCODE] as tinyint) [POSTSTATUSCODE],
            case [SALESDEPOSITTEMPLATE].[REFERENCECODE]
                when 0 then [SALESDEPOSITTEMPLATE].[NAME]
                when 1 then @PROCESSNAME
                when 2 then @PROCESSNAME + ' ' + [SALESDEPOSITTEMPLATE].[NAME]
            end as [REFERENCE],
            dbo.UFN_SALESDEPOSITTEMPLATE_CALCULATEDEFAULTALLOWEDPAYMENTMETHODS([SALESDEPOSITTEMPLATE].[ID]) as [DEFAULTALLOWEDPAYMENTMETHODS],
            [PAYMENTS].[PAYMENTTOTAL] as [PAYMENTTOTAL],
            [PAYMENTS].[CREDITPAYMENTTOTAL] as [CREDITPAYMENTTOTAL],
            case --If there is a cash difference, put it only in a "not posted" deposit, unless GL is turned off

                when @ALLOWGLDISTRIBUTIONS = 0 or PAYMENTS.[POSTSTATUSCODE] = 1 then
                  dbo.UFN_SALESDEPOSITTEMPLATE_UNLINKEDRECONCILEDPAYMENTS_GETCASHDIFFERENCE_3([SALESDEPOSITTEMPLATE].[ID], [PAYMENTS].[TRANSACTIONDATE], @PAYMENTDATE)
                else 
                  0
                end as [CASHDIFFERENCE],
            [PAYMENTS].[PAYMENTCOUNT] as [NUMBEROFPAYMENTS],
            case when [PAYMENTS].[CREDITPAYMENTTOTAL] > 0 then cast(1 as bit) else cast(0 as bit) end as [HASCREDITPAYMENTS],
            @ISRECONCILEDPROCESS as [ISRECONCILEDPROCESS],
            [PAYMENTS].[TRANSACTIONDATE],
            case --If there is a check difference, put it only in a "not posted" deposit, unless GL is turned off

                when @ALLOWGLDISTRIBUTIONS = 0 or PAYMENTS.[POSTSTATUSCODE] = 1 then
                  dbo.UFN_SALESDEPOSITTEMPLATE_UNLINKEDRECONCILEDPAYMENTS_GETCHECKDIFFERENCE_3([SALESDEPOSITTEMPLATE].[ID], [PAYMENTS].[TRANSACTIONDATE], @PAYMENTDATE)
                else
                  0
                end as [CHECKDIFFERENCE],
            [PAYMENTS].TRANSACTIONCURRENCYID,
      BANKACCOUNT.TRANSACTIONCURRENCYID as BANKACCOUNTCURRENCYID,
      CS.BASECURRENCYID
  from dbo.[SALESDEPOSITTEMPLATE]
  inner join dbo.BANKACCOUNT on BANKACCOUNT.ID = SALESDEPOSITTEMPLATE.BANKACCOUNTID
  inner join dbo.PDACCOUNTSYSTEM PDAS on BANKACCOUNT.PDACCOUNTSYSTEMID = PDAS.ID
  inner join dbo.CURRENCYSET CS on CS.ID = PDAS.CURRENCYSETID
        outer apply (

            select
                coalesce(sum([PAYMENTAMOUNT]),0) as [PAYMENTTOTAL],
                coalesce(sum([CREDITPAYMENTAMOUNT]),0) as [CREDITPAYMENTTOTAL],
                coalesce(sum([ISPAYMENT]),0) as [PAYMENTCOUNT],
                [POSTSTATUSCODE],
                cast([TRANSACTIONDATE] as datetime) as [TRANSACTIONDATE],
                [TRANSACTIONCURRENCYID]
                from
                    (
                        --Not yet posted payments

                        select
                            [AMOUNT] as [PAYMENTAMOUNT],
                            0 as [CREDITPAYMENTAMOUNT],
                            1 as [POSTSTATUSCODE],
                            case @ISGROUPEDBYDATE
                                when 1 then cast([TRANSACTIONDATE] as date)
                                else null
                            end as [TRANSACTIONDATE],
                            cast(1 as int) as [ISPAYMENT],
                            [TRANSACTIONCURRENCYID]
                            from dbo.UFN_TREASURYDEPOSITTEMPLATE_GETUNLINKEDPAYMENTS(SALESDEPOSITTEMPLATE.ID, 1, null, @PAYMENTDATE)

                        --Do not post payments

                        union all
                        select
                            [AMOUNT] as [PAYMENTAMOUNT],
                            0 as [CREDITPAYMENTAMOUNT],
                            3 as [POSTSTATUSCODE],
                            case @ISGROUPEDBYDATE
                                when 1 then cast([TRANSACTIONDATE] as date)
                                else null
                            end as [TRANSACTIONDATE],
                            cast(1 as int) as [ISPAYMENT],
                            [TRANSACTIONCURRENCYID]
                            from dbo.UFN_TREASURYDEPOSITTEMPLATE_GETUNLINKEDPAYMENTS(SALESDEPOSITTEMPLATE.ID, 3, null, @PAYMENTDATE)

                        --Credit payments

                        union all
                        select
                            0 as [PAYMENTAMOUNT],
                            [AMOUNT] as [CREDITPAYMENTAMOUNT],
                            1 as [POSTSTATUSCODE],
                            case @ISGROUPEDBYDATE
                                when 1 then cast([TRANSACTIONDATE] as date)
                                else null
                            end as [TRANSACTIONDATE],
                            cast(0 as int) as [ISPAYMENT],
                            [TRANSACTIONCURRENCYID]
                            from dbo.UFN_TREASURYDEPOSITTEMPLATE_GETUNLINKEDCREDITPAYMENTS(SALESDEPOSITTEMPLATE.ID, 1, null, null, @PAYMENTDATE)

        --Credit payments (DNP)

                        union all
                        select
                            0 as [PAYMENTAMOUNT],
                            [AMOUNT] as [CREDITPAYMENTAMOUNT],
                            3 as [POSTSTATUSCODE],
                            case @ISGROUPEDBYDATE
                                when 1 then cast([TRANSACTIONDATE] as date)
                                else null
                            end as [TRANSACTIONDATE],
                            cast(0 as int) as [ISPAYMENT],
                            [TRANSACTIONCURRENCYID]
                            from dbo.UFN_TREASURYDEPOSITTEMPLATE_GETUNLINKEDCREDITPAYMENTS(SALESDEPOSITTEMPLATE.ID, 3, null, null, @PAYMENTDATE)

                        --Adding reconciliation submission dates for reconciliations without an order or credit payment

                        --These reconciliations may have an over/short amount to account for on those dates

                        union all 
                        select
                            0 as [PAYMENTAMOUNT],
                            0 as [CREDITPAYMENTAMOUNT],
                            case when @ALLOWGLDISTRIBUTIONS = 1 then 1 else 3 end as [POSTSTATUSCODE],
                            case @ISGROUPEDBYDATE
                                when 1 then cast([ORIGINALSUBMISSIONDATE] as date)
                                else null
                            end as [TRANSACTIONDATE],
                            cast(0 as int) as [ISPAYMENT],
                            (select top 1 ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1) as TRANSACTIONCURRENCYID
                        from dbo.[RECONCILIATION]
                        where 
                            [STATUSCODE] > 1 and CASHOVERSHORTDEPOSITED = 0 and
                            not exists(select 1 
                                from (
                                    select RECONCILIATIONID, PAYMENTID, DONOTRECONCILE from dbo.SALESORDERPAYMENT
                                    union all
                                    select RECONCILIATIONID, PAYMENTID, 0 from dbo.RESERVATIONSECURITYDEPOSITPAYMENT) SALESORDERPAYMENT
                                inner join dbo.REVENUEPAYMENTMETHOD on SALESORDERPAYMENT.PAYMENTID = REVENUEPAYMENTMETHOD.REVENUEID
                                where RECONCILIATIONID = RECONCILIATION.ID and DONOTRECONCILE = 0 and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 0)
                            and not exists(select 1 from dbo.CREDITPAYMENT where RECONCILIATIONID = RECONCILIATION.ID and CREDITPAYMENT.PAYMENTMETHODCODE = 0)

                        union all
                        select
                            0 as [PAYMENTAMOUNT],
                            0 as [CREDITPAYMENTAMOUNT],
                            case when @ALLOWGLDISTRIBUTIONS = 1 then 1 else 3 end as [POSTSTATUSCODE],
                            case @ISGROUPEDBYDATE
                                when 1 then cast([ORIGINALSUBMISSIONDATE] as date)
                                else null
                            end as [TRANSACTIONDATE],
                            cast(0 as int) as [ISPAYMENT],
                            (select top 1 ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1) as TRANSACTIONCURRENCYID
                        from dbo.[RECONCILIATION]
                        where 
                            [STATUSCODE] > 1 and CHECKOVERSHORTDEPOSITED = 0 and
                            not exists(select 1 
                                from (
                                    select RECONCILIATIONID, PAYMENTID, DONOTRECONCILE from dbo.SALESORDERPAYMENT
                                    union all
                                    select RECONCILIATIONID, PAYMENTID, 0 from dbo.RESERVATIONSECURITYDEPOSITPAYMENT) SALESORDERPAYMENT
                                inner join dbo.REVENUEPAYMENTMETHOD on SALESORDERPAYMENT.PAYMENTID = REVENUEPAYMENTMETHOD.REVENUEID
                                where RECONCILIATIONID = RECONCILIATION.ID and DONOTRECONCILE = 0 and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 1)
                            and not exists(select 1 from dbo.CREDITPAYMENT where RECONCILIATIONID = RECONCILIATION.ID and CREDITPAYMENT.PAYMENTMETHODCODE = 1)
                    ) [TRANSACTIONS]
                group by [TRANSACTIONDATE], [POSTSTATUSCODE], [TRANSACTIONCURRENCYID]


        ) [PAYMENTS]
        where SALESDEPOSITTEMPLATE.INCLUDEINPROCESS = 1
and SALESDEPOSITTEMPLATE.SALESDEPOSITPROCESSID = @ID
            end