UFN_SALESDEPOSITTEMPLATE_UNLINKEDRECONCILEDPAYMENTS_GETCASHDIFFERENCE_3

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

    declare @CASHDIFFERENCE money = 0
    declare @SALESCHANNELOPTIONCODE tinyint

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

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

            )                    
            insert into @PAYMENTCASHTABLE
            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.CASHOVERSHORTDEPOSITED = 0 and
                [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 0
                and (@SALESCHANNELOPTIONCODE = 0 
                    or RECONCILIATION.CASHOVERSHORTSALESMETHODCODE 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 @CREDITCASHTABLE table (
                RECONCILIATIONID uniqueidentifier,
                AMOUNT money,
                GROUPPERIOD tinyint --0, Past; 1, Current; 2, Future

            )
            insert into @CREDITCASHTABLE
            select 
                RECONCILIATION.ID, 
                coalesce(sum([CREDITPAYMENT].[AMOUNT]),0),
                [GROUP].[PERIOD]
            from dbo.[CREDITPAYMENT]
            inner join dbo.[RECONCILIATION]
                on [CREDITPAYMENT].[RECONCILIATIONID] = [RECONCILIATION].[ID]
            cross apply (select datediff(day,@TRANSACTIONDATE,cast([CREDITPAYMENT].[DATEADDED] as date)) as [COMPARISON]) as [CREDITTOTRANDATE]
            cross apply (
                select case
                    when [CREDITTOTRANDATE].[COMPARISON] < 0 then 0
                    when [CREDITTOTRANDATE].[COMPARISON] = 0 then 1
                    when [CREDITTOTRANDATE].[COMPARISON] > 0 then 2
                end as [PERIOD]
            ) as [GROUP]
            where 
                [RECONCILIATION].[STATUSCODE] > 1 and RECONCILIATION.CASHOVERSHORTDEPOSITED = 0 and
                [CREDITPAYMENT].[PAYMENTMETHODCODE] = 0
                and (@SALESCHANNELOPTIONCODE = 0 
                    or RECONCILIATION.CASHOVERSHORTSALESMETHODCODE 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(PAYMENTCASH.AMOUNT, 0) - coalesce(CREDITCASH.AMOUNT, 0),
                [GROUP].[PERIOD]
            from dbo.[RECONCILIATION]
            cross apply (
                select 0 as [PERIOD]
                union
                select 1
                union 
                select 2
            ) as [GROUP]
            left join @PAYMENTCASHTABLE as [PAYMENTCASH] on
                [RECONCILIATION].[ID] = [PAYMENTCASH].[RECONCILIATIONID] and 
                [GROUP].[PERIOD] = [PAYMENTCASH].[GROUPPERIOD]
            left join @CREDITCASHTABLE as [CREDITCASH] on 
                [RECONCILIATION].[ID] = [CREDITCASH].[RECONCILIATIONID] and
                [GROUP].[PERIOD] = [CREDITCASH].[GROUPPERIOD]
            where 
                [RECONCILIATION].[STATUSCODE] > 1 and RECONCILIATION.CASHOVERSHORTDEPOSITED = 0 and
                ([PAYMENTCASH].[AMOUNT] is not null or [CREDITCASH].[AMOUNT] is not null)
                and (@SALESCHANNELOPTIONCODE = 0 
                    or RECONCILIATION.CASHOVERSHORTSALESMETHODCODE in (select SALESMETHODTYPECODE from dbo.SALESDEPOSITTEMPLATESALESCHANNEL where SALESDEPOSITTEMPLATEID = @SALESDEPOSITTEMPLATEID))
        and ((@PAYMENTDATE is null) or (CAST (RECONCILIATION.RECONCILIATIONDATE as DATE) <= @PAYMENTDATE))

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

                    when [FUTUREPERIOD].[TOTAL] is null and ([CURRENTPERIOD].[TOTAL] is not null or ([SUMTOCURRENT].[TOTAL] = 0 and cast([RECONCILIATION].[ORIGINALSUBMISSIONDATE] as date) = @TRANSACTIONDATE))
                        then [RECONCILIATION].[ACTUALCASH] - [SUMTOCURRENT].[TOTAL]
                    else 0
                end)
            ,0)
            from dbo.[RECONCILIATION]
            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.CASHOVERSHORTDEPOSITED = 0
                and (@SALESCHANNELOPTIONCODE = 0 
                    or RECONCILIATION.CASHOVERSHORTSALESMETHODCODE in (select SALESMETHODTYPECODE from dbo.SALESDEPOSITTEMPLATESALESCHANNEL where SALESDEPOSITTEMPLATEID = @SALESDEPOSITTEMPLATEID))
        and ((CAST (RECONCILIATION.RECONCILIATIONDATE as DATE) <= @PAYMENTDATE))
        end
        else
        begin
            declare @PAYMENTCASHTOTAL money = 0
            declare @CREDITCASHTOTAL money = 0
            declare @ACTUALCASHTOTAL money = 0

            select @PAYMENTCASHTOTAL =     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.CASHOVERSHORTDEPOSITED = 0 and
                [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 0 
                and (
                        @SALESCHANNELOPTIONCODE = 0 
                        or RECONCILIATION.CASHOVERSHORTSALESMETHODCODE 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 @CREDITCASHTOTAL = coalesce(sum([CREDITPAYMENT].[AMOUNT]),0)
            from dbo.[RECONCILIATION]
            inner join dbo.[CREDITPAYMENT]
                on [CREDITPAYMENT].[RECONCILIATIONID] = [RECONCILIATION].[ID]
            where 
                [RECONCILIATION].[STATUSCODE] > 1 and RECONCILIATION.CASHOVERSHORTDEPOSITED = 0 and
                [CREDITPAYMENT].[PAYMENTMETHODCODE] = 0
                and (@SALESCHANNELOPTIONCODE = 0 
                    or RECONCILIATION.CASHOVERSHORTSALESMETHODCODE 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 @ACTUALCASHTOTAL = coalesce(sum([ACTUALCASH]),0)
            from dbo.[RECONCILIATION]
            where [STATUSCODE] > 1 and RECONCILIATION.CASHOVERSHORTDEPOSITED = 0
                and (@SALESCHANNELOPTIONCODE = 0 
                    or RECONCILIATION.CASHOVERSHORTSALESMETHODCODE in (select SALESMETHODTYPECODE from dbo.SALESDEPOSITTEMPLATESALESCHANNEL where SALESDEPOSITTEMPLATEID = @SALESDEPOSITTEMPLATEID))
        and ((@PAYMENTDATE is null) or (CAST (RECONCILIATION.ORIGINALSUBMISSIONDATE as DATE) <= @PAYMENTDATE))

            set @CASHDIFFERENCE = @ACTUALCASHTOTAL - (@PAYMENTCASHTOTAL - @CREDITCASHTOTAL)
        end
    end

    return  @CASHDIFFERENCE
end