USP_DATAFORMTEMPLATE_EDITLOAD_CLOSEDDRAWERCHECKOVERSHORTALLOCATION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_CLOSEDDRAWERCHECKOVERSHORTALLOCATION(
    @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.CHECKOVERSHORTSALESMETHODCODE
        ,@OVERSHORTAMOUNT = isnull(D.AMOUNT, 0) - 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)
        ,@RECONCILIATIONID = @ID
        ,@PAYMENTMETHODCODE = 1
    from dbo.RECONCILIATION R
    left join dbo.RECONCILIATIONDETAIL D on D.RECONCILIATIONID = @ID and D.PAYMENTMETHODCODE = 1
    where R.ID = @ID

    return 0;