Sales Order Return Invoice Query

SELECT ref_hdr.order_number primary_so_no,
TO_CHAR (ref_hdr.ordered_date, 'DD-MON-YYYY') primary_so_date,
hdr.order_number return_so_no,
TO_CHAR (hdr.ordered_date, 'DD-MON-YYYY') return_so_date,
ref_dtl.line_number return_so_line, ract.trx_number,
TO_CHAR (ract.trx_date, 'DD-MON-YYYY') trx_date,
TO_CHAR (hdr.ordered_date, 'DD-MON-YYYY') ord_date, ot.NAME order_type,
hdr.order_category_code, party.party_name sold_to,
cust_acct.account_number customer_number, dtl.ordered_quantity,
msi.segment1 item_code, msi.description item_desc,
ship_from_org.organization_code ship_from, pl.NAME price_list,
ship_su.LOCATION ship_to, ship_su.LOCATION ship_to_location,
ship_loc.address1 ship_to_address1, ship_loc.address2 ship_to_address2,
ship_loc.address3 ship_to_address3, ship_loc.address4 ship_to_address4,
DECODE (ship_loc.city,
NULL, NULL,
ship_loc.city || ', '
)
|| DECODE (ship_loc.state,
NULL, ship_loc.province || ', ',
ship_loc.state || ', '
)
|| DECODE (ship_loc.postal_code,
NULL, NULL,
ship_loc.postal_code || ', '
)
|| DECODE (ship_loc.country, NULL, NULL, ship_loc.country)
ship_to_address5,
dtl.CONTEXT, dtl.attribute1, dtl.attribute2, dtl.attribute3,
hdr.header_id, dtl.reference_header_id, dtl.reference_line_id
FROM oe_order_headers_all hdr,
oe_order_lines_all dtl,
oe_transaction_types_tl ot,
qp_list_headers_tl pl,
hz_cust_accounts cust_acct,
hz_parties party,
mtl_parameters ship_from_org,
hz_locations ship_loc,
hz_cust_site_uses_all ship_su,
hz_cust_acct_sites_all ship_cas,
hz_party_sites ship_ps,
mtl_system_items_b msi,
oe_order_headers_all ref_hdr,
oe_order_lines_all ref_dtl,
ra_customer_trx_lines_all ractl,
ra_customer_trx_all ract
WHERE hdr.header_id = dtl.header_id
AND hdr.order_type_id = ot.transaction_type_id
AND hdr.price_list_id = pl.list_header_id(+)
AND hdr.sold_to_org_id = cust_acct.cust_account_id(+)
AND cust_acct.party_id = party.party_id(+)
AND hdr.ship_from_org_id = ship_from_org.organization_id(+)
AND hdr.ship_to_org_id = ship_su.site_use_id(+)
AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
AND ship_cas.party_site_id = ship_ps.party_site_id(+)
AND ship_loc.location_id(+) = ship_ps.location_id
AND msi.inventory_item_id = dtl.inventory_item_id
AND msi.organization_id = hdr.ship_from_org_id
AND ot.NAME IN ('SKG ORDER RETURN - QTY', 'SKG ORDER RETURN WITH CM')
AND ref_hdr.header_id = dtl.reference_header_id
AND ref_dtl.line_id = dtl.reference_line_id
AND dtl.credit_invoice_line_id = ractl.customer_trx_line_id(+)
AND ract.customer_trx_id(+) = ractl.customer_trx_id
-- AND hdr.order_number = '7'
-- AND hdr.org_id = 1