客户对账单-本月欠款

create   or   replace   function  byamountfn
(
    customernum  varchar2 ,
    startdate   date,
    enddate     date
)  return   char   is
    v_amount           number ;
    v_receipt_amount   number ;
    refundamount       number ;
    l_receive_amount1  number ;
    l_receive_amount2  number ;
    lastdebt           number ;
    thisamount         number ;
    thismoeny          number ;
    upextendedamount   number ;
begin
     -- -------------------------**********得到上月欠款**************-------------------------------
     select   sum (nvl(rct.extended_amount_tax,  0 )) extended_amount  -- 当前参数日期之前的所有货款
       into  v_amount
       from  ( select  ctl.customer_trx_line_id,
                   ct.customer_trx_id,
                   ct.initial_customer_trx_id,
                   ct.trx_number,
                   nvl(cux_ar_utl_pkg.get_trx_gl_date(ct.customer_trx_id),
                       ct.trx_date) trx_date,
                   ct.bill_to_customer_id,
                   nvl(ctl.gross_extended_amount, ctl.extended_amount) extended_amount_tax
               from  ra_customer_trx_all       ct,
                   ra_customer_trx_lines_all ctl,
                   hz_parties                hpt,
                   hz_cust_accounts          hca,
                   ra_cust_trx_types_all     ctt,
                   mtl_units_of_measure      uom
              where  ct.customer_trx_id  =  ctl.customer_trx_id
                and  ct.org_id  =  ctl.org_id
                and  ct.bill_to_customer_id  =  hca.cust_account_id
                and  hca.party_id  =  hpt.party_id
                and  ctt.cust_trx_type_id  =  ct.cust_trx_type_id
                and  ctl.line_type  !=   ' TAX '
                and  ctt.type  in  ( ' INV ' ,  ' CM ' )
                and  ctt.cust_trx_type_id  !=
                   cux_ar_utl_pkg.get_return_commitment_type
                   --   AND ctt.NAME != 'SH-内销开帐'
                and  ctl.uom_code  =  uom.uom_code( + )) rct
      where   1   =   1
        and  rct.bill_to_customer_id  =
           ( select  a.customer_id
               from  ar_customers a
              where  a.customer_number  =  customernum)
        and  rct.trx_date  <=  startdate  -   1 ;

     select   sum (nvl(acr.functional_amount,  0 ))  -- 当前参数日期之前的所有应付款
       into  v_receipt_amount
       from  ar_cash_receipts_v acr
      where  acr.gl_date  <=  startdate  -   1
        and  acr.state  !=   ' REVERSED '
        and  acr.customer_id  =
           ( select  a.customer_id
               from  ar_customers a
              where  a.customer_number  =  customernum);

     select  nvl( sum (araa.amount_applied),  0 )  -- 当前参数日期之前的所有退款
       into  refundamount
       from  ar_cash_receipt_history_all    acrh,
           ar_cash_receipts_all           acr,
           ar_receivable_applications_all araa
      where  acrh.gl_date  <=  startdate  -   1
        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  =  customernum);
    lastdebt : =  (v_amount  -  v_receipt_amount  +  refundamount);  -- 小于当前日期之前的所有货款 减去 小于当前日期之前的所有应付帐款 加小于当前日期之前的所有退款=上月欠款
     -- -------------------------***********本月货款**********------------------------------- 
     select  nvl( sum (extended_amount),  0 )
       into  thisamount
       from  ( select  ct.trx_number,
                   ct.trx_date,
                   decode(ctl.gross_extended_amount,
                           null ,
                          ctl.extended_amount,
                          ctl.gross_extended_amount)  as  extended_amount
               from  ra_customer_trx_all          ct,
                   ra_customer_trx_lines_all    ctl,
                   hz_parties                   hpt,
                   hz_cust_accounts             hca,
                   ra_cust_trx_types_all        ctt,
                   mtl_units_of_measure         uom,
                   ra_cust_trx_line_gl_dist_all rctl
              where  ct.customer_trx_id  =  ctl.customer_trx_id
                and  rctl.customer_trx_id  =  ct.customer_trx_id
                and   ' REC '   =  rctl.account_class
                and   ' Y '   =  rctl.latest_rec_flag
                and  ct.org_id  =  ctl.org_id
                and  ct.bill_to_customer_id  =  hca.cust_account_id
                and  hca.party_id  =  hpt.party_id
                and  ctt.cust_trx_type_id  =  ct.cust_trx_type_id
                and  ctl.line_type  !=   ' TAX '
                and  ctt.type  in  ( ' INV ' ,  ' CM ' )
                and  ctt.cust_trx_type_id  !=
                   cux_ar_utl_pkg.get_return_commitment_type
                   --  AND ctt.NAME != 'SH-内销开帐'
                and  ctl.uom_code  =  uom.uom_code( + )
                and  to_char(rctl.gl_date,  ' yyyy-mm-dd ' )  between
                   to_char(startdate,  ' YYYY-MM-DD ' )  and
                   to_char(enddate,  ' YYYY-MM-DD ' )
                and  hca.account_number  =  customernum);
     -- -------------------------**********本月回款************------------------------------- 
     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  =  customernum);
     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  =  customernum);
    thismoeny : =  (l_receive_amount1  -  l_receive_amount2);  -- 本月回款 = 本月应付帐款-本月退款
     -- -------------------------**********本月欠款************-------------------------------              
     -- 本月欠款= 上月欠款+本月货款-本月回款          
    upextendedamount : =  (lastdebt  +  thisamount  -  thismoeny);
     return  upextendedamount;
end ;

cux_ar_utl_pkg.get_return_commitment_type

function  get_return_commitment_type  return   number   is
cursor  csr_type  is
select  cust_trx_type_id  from  ra_cust_trx_types_all  where  name  =   ' SH-保证金退回 ' ; retval  number ;
begin
open  csr_type;  fetch  csr_type  into  retval;  close  csr_type;  return  nvl(retval,  - 1 );
end ;