select d.party_number,
d.party_name,
d.party_site_name,
d.document_type,
d.trx_number doc_number,
d.trx_date doc_date,
e.attribute1 delivery_num,
e.date1 delivery_date,
d.tax_invoice_number,
d.tax_invoice_date,
e.numeric1 exempted_amt,
d.exemption_type,
d.exemption_num||decode(nvl(to_char(d.exemption_date,'DD-Mon-YYYY'),'X'),'X','','/')||to_char(d.exemption_date,'DD-Mon-YYYY') exemption_ref,
d.proof_of_export_num||decode(nvl(to_char(d.proof_received_date,'DD-Mon-YYYY'),'X'),'X','','/')||to_char(d.proof_received_date,'DD-Mon-YYYY') proof_ref
from jai_rep_context_t c, jai_rep_trx_detail_t d, jai_rep_trx_jx_ext_t e
where c.rep_context_id = d.rep_context_id
and d.detail_tax_line_id = e.detail_tax_line_id
and c.extract_summary_code = 'L'
and d.first_party_primary_reg_num = :tax_registration_num1
and c.rep_organization_id = :organization_id1
and c.rep_location_id = :location_id1
and c.request_id = :p_conc_request_id
--edit by hongjun.wu for bug#19477875 begin--
--order by d.tax_type_name
order by d.tax_type_name, d.party_number, d.trx_number, d.trx_date, e.attribute1, e.date1
--edit by hongjun.wu for bug#19477875 end-- |