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