USP_DATAFORMTEMPLATE_VIEW_DEPOSITSUMMARY

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DEPOSITSUMMARY
(
    @DATALOADED bit = 0 output,
    @DEPOSITSUMMARY xml = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    declare @APPROVEDRECONCILIATIONS table (
        ID uniqueidentifier not null,
        ACTUALCASH money not null
    )

    -- This will produce a scan.  We may need
    -- a separate table containing only a few records for faster querying.
    insert into @APPROVEDRECONCILIATIONS
    select
        ID,
        ACTUALCASH
    from
        dbo.RECONCILIATION
    where
        STATUSCODE = 2;  -- Approved

    select
        @DATALOADED = 1,
        @DEPOSITSUMMARY = (
            select
                sum(PAYMENTCOUNT) as PAYMENTCOUNT, 
                sum(AMOUNT) as AMOUNT, 
                PAYMENTMETHODCODE, 
                PAYMENTMETHOD, 
                PAYMENTTYPE,
                ISREFUND
            from (
                select
                    count(SALESORDERPAYMENT.ID) as PAYMENTCOUNT,
                    case REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
                        when 0 then
                            (
                                select sum(ACTUALCASH)
                                from @APPROVEDRECONCILIATIONS
                                where ID in (select RECONCILIATIONID from SALESORDERPAYMENT)
                            )
                        else
                            sum(SALESORDERPAYMENT.AMOUNT)
                    end as AMOUNT, 
                    REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                    case REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
                        when 0 then dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION(0)
                        when 1 then dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION(1)
                    end as PAYMENTMETHOD,
                    case REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
                        when 2 then dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCARDPAYMENTMETHODDETAIL.[CREDITTYPECODEID]) 
                        when 10 then dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODDETAIL.[OTHERPAYMENTMETHODCODEID])
                    end as PAYMENTTYPE,
                    cast(0 as bit) as [ISREFUND]
                from @APPROVEDRECONCILIATIONS as APPROVEDRECONCILIATIONS
                inner join dbo.SALESORDERPAYMENT on APPROVEDRECONCILIATIONS.ID = SALESORDERPAYMENT.RECONCILIATIONID
                inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = SALESORDERPAYMENT.PAYMENTID
                left join dbo.CREDITCARDPAYMENTMETHODDETAIL on CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID 
                left join dbo.OTHERPAYMENTMETHODDETAIL on OTHERPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
                where 
                    SALESORDERPAYMENT.DONOTRECONCILE = 0
                group by
                    REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                    CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID,
                    OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID

                union all

                -- LeeCh, 06/26/09, Bug #42655
                -- If there is cash over but no expected cash payment,
                -- we should still take the cash over amount into the deposit summary
                select
                    count(ID) as PAYMENTCOUNT,
                    sum(ACTUALCASH) as AMOUNT, 
                    0 as PAYMENTMETHODCODE,
                    dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION(0) as PAYMENTMETHOD,
                    null as PAYMENTTYPE,
                    cast(0 as bit) as [ISREFUND]
                from
                    @APPROVEDRECONCILIATIONS
                where
                    ACTUALCASH > 0 and 
                    ID not in (
                        select SALESORDERPAYMENT.RECONCILIATIONID
                        from dbo.SALESORDERPAYMENT
                        inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = SALESORDERPAYMENT.PAYMENTID
                        where REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 0  -- Cash
                    )
                having
                    count(ID) > 0

                union all  --Refunds of payment types not used for reconciliation sales
                select 
                    0 as PAYMENTCOUNT, --Refunds of a "payment type" don't contribute to the count (just the number of refunds -- unioned below)
                    -coalesce(sum(REFUNDSPAYMENTS.REFUNDAMOUNT),0),
                    REFUNDSPAYMENTS.PAYMENTMETHODCODE,
                    case when REFUNDSPAYMENTS.PAYMENTMETHODCODE in (0,1) then dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION(REFUNDSPAYMENTS.PAYMENTMETHODCODE) end as PAYMENTMETHOD,
                    case REFUNDSPAYMENTS.PAYMENTMETHODCODE
                        when 2 then dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(REFUNDSPAYMENTS.CREDITTYPECODEID) 
                        when 10 then dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(REFUNDSPAYMENTS.OTHERPAYMENTMETHODCODEID)
                        else ''
                    end as PAYMENTTYPE,
                    cast(0 as bit) as ISREFUND
                from (
                    select
                        coalesce(CREDITPAYMENT.AMOUNT,0) as REFUNDAMOUNT,
                        CREDITPAYMENT.PAYMENTMETHODCODE,
                        (
                            select top 1 CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID
                            from dbo.CREDITCARDPAYMENTMETHODDETAIL
                            inner join dbo.REVENUEPAYMENTMETHOD on CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
                            inner join dbo.CREDITORDERPAYMENTMAP on CREDITORDERPAYMENTMAP.ORDERPAYMENTID = REVENUEPAYMENTMETHOD.REVENUEID
                            where CREDITORDERPAYMENTMAP.CREDITPAYMENTID = CREDITPAYMENT.ID
                        ) as CREDITTYPECODEID,
                        OTHERPAYMENTMETHODCODEID
                    from dbo.CREDITPAYMENT
                    inner join @APPROVEDRECONCILIATIONS as APPROVEDRECONCILIATIONS on CREDITPAYMENT.RECONCILIATIONID = APPROVEDRECONCILIATIONS.ID
                    where 
                        CREDITPAYMENT.PAYMENTMETHODCODE <> 0  -- Cash
                ) as REFUNDSPAYMENTS
                group by 
                    REFUNDSPAYMENTS.PAYMENTMETHODCODE, 
                    REFUNDSPAYMENTS.CREDITTYPECODEID,
                    REFUNDSPAYMENTS.OTHERPAYMENTMETHODCODEID

                union all
                select
                    count(distinct CREDITPAYMENT.CREDITID) as PAYMENTCOUNT,
                    0 as AMOUNT,
                    null as PAYMENTMETHODCODE,
                    null as PAYMENTMETHOD,
                    null as PAYMENTTYPE,
                    cast(1 as bit) as ISREFUND
                from dbo.CREDITPAYMENT
                inner join @APPROVEDRECONCILIATIONS as APPROVEDRECONCILIATIONS on CREDITPAYMENT.RECONCILIATIONID = APPROVEDRECONCILIATIONS.ID
                having count(distinct CREDITPAYMENT.CREDITID) > 0
            ) as DEPOSITEDRECONCILIATIONS
            group by ISREFUND, PAYMENTMETHODCODE, PAYMENTMETHOD, PAYMENTTYPE
            order by ISREFUND asc, PAYMENTMETHODCODE
            for xml raw ('ITEM'), type, elements, root('DEPOSITSUMMARY'), BINARY BASE64
        );

  return 0;