USP_DATAFORMTEMPLATE_VIEW_RECONCILIATIONDETAILS_2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RECONCILIATIONDETAILS_2
(
    @ID uniqueidentifier
    ,@DATALOADED bit = 0 output
    ,@USERID nvarchar(50) = null output
    ,@RECONCILIATIONDATE datetime = null output
    ,@ACTUALCASHPAYMENTS money = null output
    ,@STARTINGCASHBALANCE money = null output
    ,@EXPECTEDCASHPAYMENTS money = null output
    ,@CASHREFUNDS money = null output
    ,@NUMBERCHECKSCOUNTED int = null output
    ,@EXPECTEDCHECKSCOUNT int = null output
    ,@AMOUNTCHECKSCOUNTED money = null output
    ,@EXPECTEDCHECKPAYMENTS money = null output
    ,@OTHERPAYMENTS xml = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;

    set @DATALOADED = 0;

    select 
        @DATALOADED = 1
        ,@USERID = dbo.UFN_APPUSER_GETNAME(RECONCILIATION.APPUSERID)
        ,@RECONCILIATIONDATE = RECONCILIATION.RECONCILIATIONDATE
        ,@ACTUALCASHPAYMENTS = RECONCILIATION.ACTUALCASH
        ,@STARTINGCASHBALANCE = RECONCILIATION.STARTINGCASH
        ,@EXPECTEDCASHPAYMENTS = isnull((select SUM(P.AMOUNT)
            from dbo.SALESORDERPAYMENT P
            inner join dbo.REVENUEPAYMENTMETHOD PM on P.PAYMENTID = PM.REVENUEID
            where PM.PAYMENTMETHODCODE = 0 and P.RECONCILIATIONID = @ID), 0)
            + isnull((select sum(P.AMOUNT)
            from dbo.RESERVATIONSECURITYDEPOSITPAYMENT P
            inner join dbo.REVENUEPAYMENTMETHOD PM on P.PAYMENTID = PM.REVENUEID
            where PM.PAYMENTMETHODCODE = 0 and P.RECONCILIATIONID = @ID), 0)
        ,@CASHREFUNDS = isnull((select sum(CP.AMOUNT)
            from dbo.CREDITPAYMENT CP
            where CP.PAYMENTMETHODCODE = 0 and CP.RECONCILIATIONID = @ID), 0)
        ,@NUMBERCHECKSCOUNTED = isnull(D.QUANTITY, 0)
        ,@EXPECTEDCHECKSCOUNT = isnull((select COUNT(PM.ID)
            from dbo.SALESORDERPAYMENT P
            inner join dbo.REVENUEPAYMENTMETHOD PM on P.PAYMENTID = PM.REVENUEID
            where PM.PAYMENTMETHODCODE = 1 and P.RECONCILIATIONID = @ID), 0)
            + isnull((select COUNT(PM.ID)
            from dbo.RESERVATIONSECURITYDEPOSITPAYMENT P
            inner join dbo.REVENUEPAYMENTMETHOD PM on P.PAYMENTID = PM.REVENUEID
            where PM.PAYMENTMETHODCODE = 1 and P.RECONCILIATIONID = @ID), 0)
        ,@AMOUNTCHECKSCOUNTED = isnull(D.AMOUNT, 0)
        ,@EXPECTEDCHECKPAYMENTS = isnull((select SUM(P.AMOUNT)
            from dbo.SALESORDERPAYMENT P
            inner join dbo.REVENUEPAYMENTMETHOD PM on P.PAYMENTID = PM.REVENUEID
            where PM.PAYMENTMETHODCODE = 1 and P.RECONCILIATIONID = @ID), 0)
            + isnull((select sum(P.AMOUNT)
            from dbo.RESERVATIONSECURITYDEPOSITPAYMENT P
            inner join dbo.REVENUEPAYMENTMETHOD PM on P.PAYMENTID = PM.REVENUEID
            where PM.PAYMENTMETHODCODE = 1 and P.RECONCILIATIONID = @ID), 0)
        ,@OTHERPAYMENTS = (
            select T.PAYMENTMETHOD
                ,T.TOTALAMOUNT [AMOUNT]
                ,T.EXPECTED [NUMBER]
                ,T.PAYMENTMETHODCODE
                ,T.ISREFUND
                ,T.DEPOSITABLE
            from dbo.UFN_RECONCILIATION_GETOTHERRECEIPTS_WITH_REFUNDDETAIL(@ID, null, null) T
            where T.PAYMENTMETHODCODE != 1 or T.ISREFUND = 1
            order by T.ISREFUND, T.PAYMENTMETHODCODE, T.PAYMENTMETHOD
            for xml raw('ITEM'),type,elements,root('OTHERPAYMENTS'),BINARY BASE64)
    from dbo.RECONCILIATION
    left join dbo.RECONCILIATIONDETAIL D on D.RECONCILIATIONID = @ID and D.PAYMENTMETHODCODE = 1
    where RECONCILIATION.ID = @ID;

    return 0;