应收帐款汇总

select  customer_name,
       customer_number,
       extended_amount,
       description,
       price,
       quantity,
       salename,
       startdate,
       enddate
   from  ( select  hpt.party_name customer_name,
               hca.account_number customer_number,
               decode(ctl.gross_extended_amount,
                       null ,
                      ctl.extended_amount,
                      ctl.gross_extended_amount)  as  extended_amount,
               ctl.description,
               decode(ctl.gross_unit_selling_price,
                       null ,
                      ctl.unit_selling_price,
                      ctl.gross_unit_selling_price)  as  price,
               arpt_sql_func_util.get_salesrep_name_number(ct.primary_salesrep_id,
                                                            ' NAME ' ,
                                                           ct.org_id)  as  salename,
               nvl(ctl.quantity_credited, ctl.quantity_invoiced)  as  quantity,
               to_char(:startdate,  ' YYYY-MM-DD ' )  as  startdate,
               to_char(:enddate,  ' YYYY-MM-DD ' )  as  enddate
           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( + )
            and  to_char(ct.trx_date,  ' yyyy-mm-dd ' )  between
               to_char(:startdate,  ' YYYY-MM-DD ' )  and
               to_char(:enddate,  ' YYYY-MM-DD ' )
            and  hca.account_number  =  nvl(:customernum, hca.account_number))