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,
export 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-open account for internal sales '
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 ))
