AR應收賬款匯總

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))