UFN_SALESDEPOSITTEMPLATE_UNLINKEDRECONCILEDPAYMENTS_GETCASHDIFFERENCE
Returns the difference between the actual and expected amounts of cash to be deposited based on a sales deposit template.
Return
| Return Type |
|---|
| money |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SALESDEPOSITTEMPLATEID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_SALESDEPOSITTEMPLATE_UNLINKEDRECONCILEDPAYMENTS_GETCASHDIFFERENCE
(
@SALESDEPOSITTEMPLATEID uniqueidentifier
)
returns money
as begin
declare @PAYMENTCASHTOTAL money = 0
declare @CREDITCASHTOTAL money = 0
declare @ACTUALCASHTOTAL money = 0
if dbo.UFN_SALESDEPOSITTEMPLATE_HASPAYMENTMETHOD(@SALESDEPOSITTEMPLATEID, 0) = 1
begin
select @PAYMENTCASHTOTAL = coalesce(sum([REVENUE].[AMOUNT]),0)
from dbo.[REVENUE]
inner join (select ID, SALESORDERID, PAYMENTID, RECONCILIATIONID, AMOUNT from dbo.SALESORDERPAYMENT with (nolock) where [DONOTRECONCILE] = 0
union all
select ID, RESERVATIONID as SALESORDERID, PAYMENTID, RECONCILIATIONID, AMOUNT from dbo.RESERVATIONSECURITYDEPOSITPAYMENT with (nolock)
) as SALESORDERPAYMENT
on [REVENUE].[ID] = [SALESORDERPAYMENT].[PAYMENTID]
inner join dbo.[REVENUEPAYMENTMETHOD]
on [REVENUE].[ID] = [REVENUEPAYMENTMETHOD].[REVENUEID]
inner join dbo.[RECONCILIATION]
on [SALESORDERPAYMENT].[RECONCILIATIONID] = [RECONCILIATION].[ID]
where
[RECONCILIATION].[STATUSCODE] = 2 and
[REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 0
select @CREDITCASHTOTAL = coalesce(sum([CREDITPAYMENT].[AMOUNT]),0)
from dbo.[CREDITPAYMENT]
inner join dbo.[RECONCILIATION]
on [CREDITPAYMENT].[RECONCILIATIONID] = [RECONCILIATION].[ID]
where
[RECONCILIATION].[STATUSCODE] = 2 and
[CREDITPAYMENT].[PAYMENTMETHODCODE] = 0
select @ACTUALCASHTOTAL = coalesce(sum([ACTUALCASH]),0)
from dbo.[RECONCILIATION]
where [STATUSCODE] = 2
end
return @ACTUALCASHTOTAL - (@PAYMENTCASHTOTAL - @CREDITCASHTOTAL)
end