UFN_SALESDEPOSITPROCESS_TEMPLATES_GETOVERSHORTS

create function dbo.UFN_SALESDEPOSITPROCESS_TEMPLATES_GETOVERSHORTS
(
    @SALESDEPOSITPROCESSID uniqueidentifier
)
RETURNS TABLE AS
RETURN (
        select 
            case when unpvt.PAYMENTMETHOD = 'CASHOVERSHORTSALESMETHODCODE' then 0 else 1 end PAYMENTMETHODCODE
            ,case when unpvt.PAYMENTMETHOD = 'CASHOVERSHORTSALESMETHODCODE' then 'Cash' else 'Check' end PAYMENTMETHOD
            ,unpvt.SALESMETHODTYPECODE as SALESMETHODCODE
      ,unpvt.RECONCILIATIONDATE as TRANSACTIONDATE
        from (
            select 
                isnull([0], 0) CASHDIFF
                ,RECONCILIATIONLIST.CHECKDIFFERENCE
                ,RECONCILIATION.CASHOVERSHORTSALESMETHODCODE
                ,RECONCILIATION.CHECKOVERSHORTSALESMETHODCODE
        ,RECONCILIATION.RECONCILIATIONDATE
            from
            (
                select DIFFERENCETABLE.ID
                    ,SUM(case when DIFFERENCETABLE.PAYMENTMETHODCODE = 0 then DIFFERENCETABLE.[DIFFERENCE] end) as [0]
                    ,SUM(case when DIFFERENCETABLE.PAYMENTMETHODCODE = 1 then DIFFERENCETABLE.[DIFFERENCE] end) as [CHECKDIFFERENCE]
                from (
                    select isnull(isnull(EXPECTED.ID, ACTUAL.ID),REFUND.ID) as ID
                        ,isnull(isnull(EXPECTED.PAYMENTMETHODCODE, ACTUAL.PAYMENTMETHODCODE),REFUND.PAYMENTMETHODCODE) as PAYMENTMETHODCODE
                        ,case isnull(isnull(EXPECTED.PAYMENTMETHODCODE, ACTUAL.PAYMENTMETHODCODE),REFUND.PAYMENTMETHODCODE)
                            when 0 then isnull(ACTUAL.ACTUALAMOUNT, 0)  - (isnull(EXPECTED.EXPECTEDAMOUNT, 0) - isnull(REFUND.REFUNDAMOUNT, 0))
                            when 1 then isnull(ACTUAL.ACTUALAMOUNT, 0)  - (isnull(EXPECTED.EXPECTEDAMOUNT, 0) - isnull(REFUND.REFUNDAMOUNT, 0))
                            else 0
                        end as [DIFFERENCE]
                    from (
                        select
                            R.ID
                            ,REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE as PAYMENTMETHODCODE
                            ,sum(SALESORDERPAYMENT.AMOUNT) as EXPECTEDAMOUNT
                        from dbo.RECONCILIATION as R
                        inner join (select ID, SALESORDERID, PAYMENTID, RECONCILIATIONID, AMOUNT 
                            from dbo.SALESORDERPAYMENT 
                            where [SALESORDERPAYMENT].[DONOTRECONCILE] = 0
                            union all 
                            select ID, RESERVATIONID as SALESORDERID, PAYMENTID, RECONCILIATIONID, AMOUNT 
                            from dbo.RESERVATIONSECURITYDEPOSITPAYMENT
                        ) as SALESORDERPAYMENT on R.ID = SALESORDERPAYMENT.RECONCILIATIONID
                        inner join dbo.FINANCIALTRANSACTION REVENUE on SALESORDERPAYMENT.PAYMENTID = REVENUE.ID 
                        inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                        where R.STATUSCODE > 1 and (R.CASHOVERSHORTDEPOSITED = 0 or R.CHECKOVERSHORTDEPOSITED = 0)
                            and (@SALESDEPOSITPROCESSID is null or REVENUE.PDACCOUNTSYSTEMID = (select PDACCOUNTSYSTEMID from dbo.SALESDEPOSITPROCESS where ID = @SALESDEPOSITPROCESSID))
                        group by R.ID, REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
                    ) as EXPECTED 

                    full outer join (
                        select 
                            R.ID
                            ,RD.PAYMENTMETHODCODE as PAYMENTMETHODCODE
                            ,sum(RD.AMOUNT) as ACTUALAMOUNT
                        from dbo.RECONCILIATION R 
                        inner join dbo.RECONCILIATIONDETAIL RD on R.ID = RD.RECONCILIATIONID 
                        where R.STATUSCODE > 1 and RD.PAYMENTMETHODCODE = 1 and R.CHECKOVERSHORTDEPOSITED = 0
                        group by R.ID, RD.PAYMENTMETHODCODE

                        union all

                        select 
                            R.ID
                            ,0 as PAYMENTMETHODCODE
                            ,coalesce(ACTUALCASH,0) as ACTUALAMOUNT
                        from dbo.RECONCILIATION R
                        where R.STATUSCODE > 1 and R.CASHOVERSHORTDEPOSITED = 0
                    ) as ACTUAL on EXPECTED.ID = ACTUAL.ID and EXPECTED.PAYMENTMETHODCODE = ACTUAL.PAYMENTMETHODCODE 

                    full outer join (
                        select
                            R.ID
                            ,CP.PAYMENTMETHODCODE
                            ,sum(CP.AMOUNT) as [REFUNDAMOUNT]
                        from dbo.CREDITPAYMENT CP
                        inner join dbo.FINANCIALTRANSACTION FT on FT.ID = CP.CREDITID
                        inner join dbo.RECONCILIATION R on CP.RECONCILIATIONID = R.ID
                        where R.STATUSCODE > 1 and CP.PAYMENTMETHODCODE = 0
                            and (@SALESDEPOSITPROCESSID is null or FT.PDACCOUNTSYSTEMID = (select PDACCOUNTSYSTEMID from dbo.SALESDEPOSITPROCESS where ID = @SALESDEPOSITPROCESSID))
                        group by CP.PAYMENTMETHODCODE, R.ID
                    ) as REFUND on EXPECTED.ID = REFUND.ID and EXPECTED.PAYMENTMETHODCODE = REFUND.PAYMENTMETHODCODE 
                    where ACTUAL.ID is not null
                ) as DIFFERENCETABLE 
                group by DIFFERENCETABLE.ID
            ) as RECONCILIATIONLIST
            inner join dbo.RECONCILIATION on RECONCILIATIONLIST.ID = RECONCILIATION.ID
            where RECONCILIATION.STATUSCODE > 1 and (RECONCILIATION.CASHOVERSHORTDEPOSITED = 0 or RECONCILIATION.CHECKOVERSHORTDEPOSITED = 0)
                and isnull([0], 0) != 0
                or RECONCILIATIONLIST.CHECKDIFFERENCE != 0) P
        unpivot
            (SALESMETHODTYPECODE for PAYMENTMETHOD in (CASHOVERSHORTSALESMETHODCODE, CHECKOVERSHORTSALESMETHODCODE)) as unpvt
        where (unpvt.PAYMENTMETHOD = 'CASHOVERSHORTSALESMETHODCODE' and unpvt.CASHDIFF != 0)
            or (unpvt.PAYMENTMETHOD = 'CHECKOVERSHORTSALESMETHODCODE' and unpvt.CHECKDIFFERENCE != 0)
)