现在的位置: 首页 > 综合 > 正文

AR 客户本月回款

2012年12月28日 ⁄ 综合 ⁄ 共 1488字 ⁄ 字号 评论关闭
function returnmoneyformula return number is
    l_receive_amount1 
number;
    l_receive_amount2 
number;
    returnmoney       
number;
begin
    
select nvl(sum(acr.functional_amount), 0--付款
      into l_receive_amount1
      
from ar_cash_receipts_v acr
     
where to_char(acr.gl_date, 'YYYY-MM-DD'between
           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 a
             
where a.customer_number = :customer_number);
    
select nvl(sum(araa.amount_applied), 0--退款
      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'between
           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 = 'ACTIVITY'
       
and acr.pay_from_customer =
           (
select a.customer_id
              
from ar_customers a
             
where a.customer_number = :customer_number);
    returnmoney :
= (l_receive_amount1 - l_receive_amount2);
    
return nvl(returnmoney, 0);
end;

抱歉!评论已关闭.