USP_DATAFORMTEMPLATE_EDITLOAD_CLOSEDDRAWERCASHOVERSHORTALLOCATION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_CLOSEDDRAWERCASHOVERSHORTALLOCATION(
    @ID uniqueidentifier
    ,@DATALOADED bit = 0 output
    ,@TSLONG bigint = 0 output
    ,@USERNAME nvarchar(128) = null output
    ,@OVERSHORTAMOUNT money = null output
    ,@OVERSHORTSALESMETHODCODE tinyint = null output
    ,@RECONCILIATIONID uniqueidentifier = null output
    ,@PAYMENTMETHODCODE tinyint = null output
)
as
    set nocount on;

    set @DATALOADED = 0
    set @TSLONG = 0

    select
        @DATALOADED = 1
        ,@TSLONG = R.TSLONG
        ,@USERNAME = dbo.UFN_APPUSER_GETNAME(R.APPUSERID)
        ,@OVERSHORTSALESMETHODCODE = R.CASHOVERSHORTSALESMETHODCODE
        ,@OVERSHORTAMOUNT = R.ACTUALCASH - 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)
            + isnull((select sum(CP.AMOUNT)
            from dbo.CREDITPAYMENT CP
            where CP.PAYMENTMETHODCODE = 0 and CP.RECONCILIATIONID = @ID), 0)
        ,@RECONCILIATIONID = @ID
        ,@PAYMENTMETHODCODE = 0
    from dbo.RECONCILIATION R
    where R.ID = @ID

    return 0;