USP_SALESDEPOSITPROCESS_GETSALESDEPOSITTEMPLATEPARAMETERS

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

                declare @ISRECONCILEDPROCESS bit = null
                declare @ISGROUPEDBYDATE bit = null
                declare @PROCESSNAME nvarchar(100) = null
                select
                    @ISRECONCILEDPROCESS = [ISRECONCILEDPROCESS],
                    @ISGROUPEDBYDATE = [GROUPBYDATE],
                    @PROCESSNAME = NAME
                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],
                    dbo.UFN_SALESDEPOSITTEMPLATE_UNLINKEDRECONCILEDPAYMENTS_GETCASHDIFFERENCE_2([SALESDEPOSITTEMPLATE].[ID], [PAYMENTS].[TRANSACTIONDATE]) 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],
                    dbo.UFN_SALESDEPOSITTEMPLATE_UNLINKEDRECONCILEDPAYMENTS_GETCHECKDIFFERENCE([SALESDEPOSITTEMPLATE].[ID], [PAYMENTS].[TRANSACTIONDATE]) as [CHECKDIFFERENCE]
                from dbo.[SALESDEPOSITTEMPLATE]
                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]
                        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]
                                    from dbo.UFN_SALESDEPOSITTEMPLATE_GETUNLINKEDPAYMENTS_4(SALESDEPOSITTEMPLATE.ID, 1, null)

                                --Do not post payments

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

                                --Credit payments

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

                                --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 2 end as [POSTSTATUSCODE],
                                    case @ISGROUPEDBYDATE
                                        when 1 then cast([ORIGINALSUBMISSIONDATE] as date)
                                        else null
                                    end as [TRANSACTIONDATE],
                                    cast(0 as int) as [ISPAYMENT]
                                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 2 end as [POSTSTATUSCODE],
                                    case @ISGROUPEDBYDATE
                                        when 1 then cast([ORIGINALSUBMISSIONDATE] as date)
                                        else null
                                    end as [TRANSACTIONDATE],
                                    cast(0 as int) as [ISPAYMENT]
                                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]


                ) [PAYMENTS]
                where SALESDEPOSITTEMPLATE.INCLUDEINPROCESS = 1
            end