USP_DATALIST_SALESDEPOSITPROCESS_UNDEPOSITEDTRANSASCTIONS

CREATE procedure dbo.USP_DATALIST_SALESDEPOSITPROCESS_UNDEPOSITEDTRANSASCTIONS
(
    @SALESDEPOSITPROCESSID uniqueidentifier = null
)
as
    set nocount on;

declare @CURRENCYID uniqueidentifier;
declare @CURRENCYDESC nvarchar(110)
select @CURRENCYID = ID, @CURRENCYDESC = dbo.UFN_CURRENCY_GETDESCRIPTION(ID) 
from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1;

select 
    PAYMENTS.PAYMENTMETHOD as PAYMENTMETHOD
    ,PAYMENTS.SALESMETHOD as SALESMETHOD
    ,COUNT(1) as [COUNT]
    ,SUM(PAYMENTS.AMOUNT) as TOTAL
    ,PAYMENTS.TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID
  ,PAYMENTS.CURRENCY
from (
    select
        T.PAYMENTMETHODCODE
        ,T.PAYMENTMETHOD
        ,T.SALESMETHODCODE
        ,T.SALESMETHOD
        ,case when T.ISREFUND = 0 then T.AMOUNT else -1 * T.AMOUNT end AMOUNT
        ,T.TRANSACTIONCURRENCYID
        ,CURRENCY.NAME + ' (' + CURRENCY.ISO4217 + ')' as CURRENCY
    from dbo.UFN_SALESDEPOSITPROCESS_GETACCOUNTSYSTEMTRANSACTIONS(@SALESDEPOSITPROCESSID) T
    inner join dbo.CURRENCY on CURRENCY.ID = T.TRANSACTIONCURRENCYID

    union all

    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
        ,case when unpvt.PAYMENTMETHOD = 'CASHOVERSHORTSALESMETHODCODE' then unpvt.CASHOVERSHORTSALESMETHOD else unpvt.CHECKOVERSHORTSALESMETHOD end
        ,case when unpvt.PAYMENTMETHOD = 'CASHOVERSHORTSALESMETHODCODE' then unpvt.CASHDIFF else unpvt.CHECKDIFFERENCE end
        ,@CURRENCYID
        ,@CURRENCYDESC
    from (
        select 
            isnull([0], 0) CASHDIFF
            ,RECONCILIATIONLIST.CHECKDIFFERENCE
            ,RECONCILIATION.CASHOVERSHORTSALESMETHODCODE
            ,RECONCILIATION.CASHOVERSHORTSALESMETHOD
            ,RECONCILIATION.CHECKOVERSHORTSALESMETHODCODE
            ,RECONCILIATION.CHECKOVERSHORTSALESMETHOD
        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)
    ) PAYMENTS
group by PAYMENTS.PAYMENTMETHODCODE, PAYMENTS.PAYMENTMETHOD, PAYMENTS.SALESMETHODCODE, PAYMENTS.SALESMETHOD, PAYMENTS.TRANSACTIONCURRENCYID, PAYMENTS.CURRENCY
order by PAYMENTS.PAYMENTMETHOD, PAYMENTS.SALESMETHOD