UFN_SALESDEPOSITTEMPLATE_UNLINKEDRECONCILEDPAYMENTS_GETCHECKDIFFERENCE_3

CREATE function dbo.UFN_SALESDEPOSITTEMPLATE_UNLINKEDRECONCILEDPAYMENTS_GETCHECKDIFFERENCE_3
(
    @SALESDEPOSITTEMPLATEID uniqueidentifier,
    @TRANSACTIONDATE date,
    @PAYMENTDATE date
) 
returns money
as begin

    declare @CHECKDIFFERENCE money = 0
    declare @SALESCHANNELOPTIONCODE tinyint

    select @SALESCHANNELOPTIONCODE = SALESCHANNELOPTIONCODE
    from dbo.SALESDEPOSITTEMPLATE
    where ID = @SALESDEPOSITTEMPLATEID;

    if dbo.UFN_SALESDEPOSITTEMPLATE_HASPAYMENTMETHOD(@SALESDEPOSITTEMPLATEID, 1) = 1
    begin            
        if @TRANSACTIONDATE is not null
        begin
            declare @PAYMENTCHECKTABLE table (
                RECONCILIATIONID uniqueidentifier,
                AMOUNT money,
                GROUPPERIOD tinyint --0, Past; 1, Current; 2, Future

            )                    
            insert into @PAYMENTCHECKTABLE
            select 
                [RECONCILIATION].[ID],
                coalesce(sum([REVENUE].[AMOUNT]), 0),
                [GROUP].[PERIOD]
            from dbo.[RECONCILIATION]
            cross apply (
                select PAYMENTID, RECONCILIATIONID, AMOUNT, DEPOSITED
                from dbo.SALESORDERPAYMENT with (nolock)
                where 
                    SALESORDERPAYMENT.RECONCILIATIONID = RECONCILIATION.ID and
                    SALESORDERPAYMENT.DONOTRECONCILE = 0

                union all
                select PAYMENTID, RECONCILIATIONID, AMOUNT, DEPOSITED
                from dbo.RESERVATIONSECURITYDEPOSITPAYMENT with (nolock)
                where RESERVATIONSECURITYDEPOSITPAYMENT.RECONCILIATIONID = RECONCILIATION.ID
            ) as [SALESORDERPAYMENT]
            inner join dbo.[REVENUE] 
                on [SALESORDERPAYMENT].[PAYMENTID] = [REVENUE].[ID]
            inner join dbo.[REVENUEPAYMENTMETHOD] 
                on [REVENUE].[ID] = [REVENUEPAYMENTMETHOD].[REVENUEID]
            cross apply (select datediff(day,@TRANSACTIONDATE,cast([REVENUE].[DATE] as date)) as [COMPARISON]) as [REVENUETOTRANDATE]
            cross apply (
                select case
                    when [REVENUETOTRANDATE].[COMPARISON] < 0 then 0
                    when [REVENUETOTRANDATE].[COMPARISON] = 0 then 1
                    when [REVENUETOTRANDATE].[COMPARISON] > 0 then 2
                end as [PERIOD]
            ) as [GROUP]
            where
                [RECONCILIATION].[STATUSCODE] > 1 and RECONCILIATION.CHECKOVERSHORTDEPOSITED = 0 and
                [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 1
                and (@SALESCHANNELOPTIONCODE = 0 
                    or RECONCILIATION.CHECKOVERSHORTSALESMETHODCODE in (select SALESMETHODTYPECODE from dbo.SALESDEPOSITTEMPLATESALESCHANNEL where SALESDEPOSITTEMPLATEID = @SALESDEPOSITTEMPLATEID))
        and ((@PAYMENTDATE is null) or (CAST (RECONCILIATION.RECONCILIATIONDATE as DATE) <= @PAYMENTDATE))
            group by RECONCILIATION.ID, [GROUP].[PERIOD]

            declare @RECONCILIATIONS table (
                ID uniqueidentifier,
                TOTAL money,
                GROUPPERIOD tinyint --0, Past; 1, Current; 2, Future

            )
            insert into @RECONCILIATIONS
            select
                [RECONCILIATION].[ID],
                coalesce(PAYMENTCHECK.AMOUNT, 0),
                [GROUP].[PERIOD]
            from dbo.[RECONCILIATION]
            cross apply (
                select 0 as [PERIOD]
                union
                select 1
                union 
                select 2
            ) as [GROUP]
            left join @PAYMENTCHECKTABLE as [PAYMENTCHECK] on
                [RECONCILIATION].[ID] = [PAYMENTCHECK].[RECONCILIATIONID] and 
                [GROUP].[PERIOD] = [PAYMENTCHECK].[GROUPPERIOD]
            where 
                [RECONCILIATION].[STATUSCODE] > 1 and RECONCILIATION.CHECKOVERSHORTDEPOSITED = 0 and
                [PAYMENTCHECK].[AMOUNT] is not null
                and (@SALESCHANNELOPTIONCODE = 0 
                    or RECONCILIATION.CHECKOVERSHORTSALESMETHODCODE in (select SALESMETHODTYPECODE from dbo.SALESDEPOSITTEMPLATESALESCHANNEL where SALESDEPOSITTEMPLATEID = @SALESDEPOSITTEMPLATEID))
        and ((@PAYMENTDATE is null) or (CAST (RECONCILIATION.RECONCILIATIONDATE as DATE) <= @PAYMENTDATE))

            select @CHECKDIFFERENCE = coalesce(sum(
                case 
                    --Translation: When there are no future payments and the @TRANSACTIONDATE is the final day of this reconciliation, get that reconciliation's difference

                    when RECONCILIATIONDETAIL.ID is not null and [FUTUREPERIOD].[TOTAL] is null and ([CURRENTPERIOD].[TOTAL] is not null or ([SUMTOCURRENT].[TOTAL] = 0 and cast([RECONCILIATION].[ORIGINALSUBMISSIONDATE] as date) = @TRANSACTIONDATE))
                        then [RECONCILIATIONDETAIL].[AMOUNT] - [SUMTOCURRENT].[TOTAL]
                    else 0
                end)
            ,0)
            from dbo.[RECONCILIATION]
            left join dbo.RECONCILIATIONDETAIL on RECONCILIATIONDETAIL.RECONCILIATIONID = RECONCILIATION.ID and RECONCILIATIONDETAIL.PAYMENTMETHODCODE = 1
            outer apply(select TOTAL as [TOTAL] from @RECONCILIATIONS where GROUPPERIOD = 0 and [ID] = [RECONCILIATION].[ID]) as PRIORPERIOD
            outer apply(select TOTAL as [TOTAL] from @RECONCILIATIONS where GROUPPERIOD = 1 and [ID] = [RECONCILIATION].[ID]) as CURRENTPERIOD
            outer apply(select TOTAL as [TOTAL] from @RECONCILIATIONS where GROUPPERIOD = 2 and [ID] = [RECONCILIATION].[ID]) as FUTUREPERIOD
            outer apply(select coalesce([PRIORPERIOD].[TOTAL],0) + coalesce([CURRENTPERIOD].[TOTAL],0) as [TOTAL]) as [SUMTOCURRENT]
            where [RECONCILIATION].[STATUSCODE] > 1 and RECONCILIATION.CHECKOVERSHORTDEPOSITED = 0
                and (@SALESCHANNELOPTIONCODE = 0 
                    or RECONCILIATION.CHECKOVERSHORTSALESMETHODCODE in (select SALESMETHODTYPECODE from dbo.SALESDEPOSITTEMPLATESALESCHANNEL where SALESDEPOSITTEMPLATEID = @SALESDEPOSITTEMPLATEID))
        and ((@PAYMENTDATE is null) or (CAST (RECONCILIATION.RECONCILIATIONDATE as DATE) <= @PAYMENTDATE))
        end
        else
        begin
            declare @PAYMENTCHECKTOTAL money = 0
            declare @ACTUALCHECKTOTAL money = 0

            select @PAYMENTCHECKTOTAL =     coalesce(sum([REVENUE].[AMOUNT]), 0)
            from dbo.[RECONCILIATION]
            cross apply (
                select ID, SALESORDERID, PAYMENTID, RECONCILIATIONID, AMOUNT, DEPOSITED
                from dbo.SALESORDERPAYMENT with (nolock)
                where 
                    SALESORDERPAYMENT.RECONCILIATIONID = RECONCILIATION.ID and
                    SALESORDERPAYMENT.DONOTRECONCILE = 0

                union all
                select ID, RESERVATIONID as SALESORDERID, PAYMENTID, RECONCILIATIONID, AMOUNT, DEPOSITED
                from dbo.RESERVATIONSECURITYDEPOSITPAYMENT with (nolock)
                where RESERVATIONSECURITYDEPOSITPAYMENT.RECONCILIATIONID = RECONCILIATION.ID
            ) as SALESORDERPAYMENT
            inner join dbo.[REVENUE] 
                on [SALESORDERPAYMENT].[PAYMENTID] = [REVENUE].[ID]
            inner join dbo.[REVENUEPAYMENTMETHOD] 
                on [REVENUE].[ID] = [REVENUEPAYMENTMETHOD].[REVENUEID]
            where
                [RECONCILIATION].[STATUSCODE] > 1 and RECONCILIATION.CHECKOVERSHORTDEPOSITED = 0 and
                [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 1 
                and (@SALESCHANNELOPTIONCODE = 0 
                    or RECONCILIATION.CHECKOVERSHORTSALESMETHODCODE in (select SALESMETHODTYPECODE from dbo.SALESDEPOSITTEMPLATESALESCHANNEL where SALESDEPOSITTEMPLATEID = @SALESDEPOSITTEMPLATEID))
                and (
                    (@PAYMENTDATE is null) 
                    or (
                        CAST(
                            (
                                SELECT 
                                    max(FT2.CALCULATEDDATE) 
                                from 
                                    dbo.FINANCIALTRANSACTION FT2
                                    left join dbo.SALESORDERPAYMENT SOP2 on FT2.ID = SOP2.PAYMENTID
                                    left join dbo.RESERVATIONSECURITYDEPOSITPAYMENT RSDP2 on RSDP2.PAYMENTID = FT2.ID
                                    inner join RECONCILIATION R2 on SOP2.RECONCILIATIONID = R2.ID or RSDP2.RECONCILIATIONID = R2.ID
                                where 
                                    R2.ID = RECONCILIATION.ID
                            ) as DATE
                        ) <= @PAYMENTDATE
                    )
                )

            select @ACTUALCHECKTOTAL = coalesce(sum([RECONCILIATIONDETAIL].[AMOUNT]),0)
            from dbo.[RECONCILIATION]
            left join dbo.RECONCILIATIONDETAIL on RECONCILIATIONDETAIL.RECONCILIATIONID = RECONCILIATION.ID and RECONCILIATIONDETAIL.PAYMENTMETHODCODE = 1
            where [STATUSCODE] > 1 and RECONCILIATION.CHECKOVERSHORTDEPOSITED = 0
            and (@SALESCHANNELOPTIONCODE = 0 
                    or RECONCILIATION.CHECKOVERSHORTSALESMETHODCODE in (select SALESMETHODTYPECODE from dbo.SALESDEPOSITTEMPLATESALESCHANNEL where SALESDEPOSITTEMPLATEID = @SALESDEPOSITTEMPLATEID))
      and ((@PAYMENTDATE is null) or (CAST (RECONCILIATION.ORIGINALSUBMISSIONDATE as DATE) <= @PAYMENTDATE))

            set @CHECKDIFFERENCE = @ACTUALCHECKTOTAL - @PAYMENTCHECKTOTAL
        end
    end

    return  @CHECKDIFFERENCE
end