Function returnmoneyformula return number is
Rochelle receive_amount1 number;
Rochelle receive_amount2 number;
Returnmoney number;
Begin
Select nvl (sum (acr. functional_amount), 0) -- Payment
Into l_receive_amount1
From ar_cash_receipts_v acr
Where to_char (acr. gl_date, 'yyyy-MM-DD ')
To_char (: startdate, 'yyyy-MM-DD ') and
To_char (: enddate, 'yyyy-MM-DD ')
And acr. state! = 'Reversed'
And acr. customer_id =
(Select a. customer_id
From ar_customers
Where a. customer_number =: customer_number );
Select nvl (sum (araa. amount_applied), 0) -- refund
Into l_receive_amount2
From ar_cash_receipt_history_all acrh,
Ar_cash_receipts_all acr,
Ar_receivable_applications_all araa
Where to_char (acrh. gl_date, 'yyyy-MM-DD ')
To_char (: startdate, 'yyyy-MM-DD ') and
To_char (: enddate, 'yyyy-MM-DD ')
And acrh. status! = 'Reversed'
And acr. cash_receipt_id = acrh. cash_receipt_id
And acrh. cash_receipt_history_id = araa. cash_receipt_history_id
And araa. applied_customer_trx_id is null
And acrh.org _ id = acr.org _ id
And araa. display = 'y'
And araa. status = 'active'
And acr. pay_from_customer =
(Select a. customer_id
From ar_customers
Where a. customer_number =: customer_number );
Returnmoney: = (l_receive_amount1-l_receive_amount2 );
Return nvl (returnmoney, 0 );
End;