SELECT hp.party_number p_num
,ooh.order_number ord
,ooh.flow_status_code o_stat
,ool.line_number || '.' || ool.shipment_number ||
decode(ool.option_number
,NULL
,NULL
,'.') || ool.option_number line_no
,ool.ordered_item item
,ctl.batch_id
,ctl.order_line_id
,ctl.processed_flag
,cte.error_text
FROM hz_parties hp
,hz_cust_accounts hca
,oe_order_headers_all ooh
,oe_order_lines_all ool
,csi_batch_txn_lines ctl
,csi_txn_errors cte
WHERE hp.party_id = hca.party_id
AND hca.cust_account_id = ooh.sold_to_org_id
AND ooh.header_id = ool.header_id
AND ool.line_id = ctl.order_line_id(+)
AND ctl.order_line_id = cte.source_id(+)
AND hp.party_number IN ('123')
ORDER BY hp.party_id DESC
,ooh.order_number DESC
,ool.line_number
,ool.shipment_number
,NVL(ool.option_number
,0);
Note: Please do not join csi_batch_txn_lines.order_header_id(or order_line_id) with csi_txn_errors.source_header_ref%(or source_line_ref%) column as there are certain errors in csi_txn_lines table which only populates source_id and not any of the source_header_ref% or souce_line_ref% fields. So, its best to join csi_batch_txn_lines.order_line_id with csi_txn_errors.souce_id.
