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