Please advise to re-write the below query, I want to remove the long in clause from this:
SELECT ooha.org_id,
ooha.header_id,
ooha.order_number,
oola.line_id,
oola.line_number,
oola.ordered_quantity,
oola.inventory_item_id,
oola.order_quantity_uom,
oola.ship_to_org_id,
oola.sold_to_org_id,
oola.ship_from_org_id,
oola.shipping_method_code,
mso.sales_order_id,
otta.attribute1 order_type,
msib.attribute16 dnr,
msib.segment1,
ooha.flow_status_code,
oola.attribute11 enforce_wh,
oos.name order_source,
ooha.flow_status_code order_status,
oola.attribute5 dealer_order_line
FROM apps.oe_order_headers_all ooha,
apps.oe_order_lines_all oola,
apps.mtl_sales_orders mso,
apps.oe_transaction_types_all otta,
apps.mtl_system_items_b msib,
apps.hr_operating_units haou,
apps.oe_order_sources oos,
apps.oe_transaction_types_tl ott
WHERE ooha.header_id = oola.header_id
AND ooha.flow_status_code = 'ENTERED'
AND oos.order_source_id = ooha.order_source_id
AND ooha.header_id NOT IN
(SELECT header_id
FROM oe_order_holds_all
WHERE released_flag = 'N'
AND hold_source_id IN
(SELECT hold_source_id
FROM oe_hold_sources_all
WHERE ( released_flag = 'N'
AND hold_release_id IS NULL
AND hold_entity_code = 'O')))
AND ooha.order_type_id = otta.transaction_type_id
AND oola.inventory_item_id = msib.inventory_item_id
AND oola.ship_from_org_id = msib.organization_id
AND mso.segment1 = ooha.order_number
AND haou.organization_id = cn_operating_unit
AND haou.organization_id = ooha.org_id
AND mso.segment2 = ott.name
AND ott.transaction_type_id = ooha.order_type_id
AND ooha.order_number =
NVL (cpn_order_number, ooha.order_number)
AND oola.link_to_line_id IS NULL
AND oola.unit_selling_price > 0
AND otta.attribute1 = 'S'
AND TRUNC (ordered_date) BETWEEN TRUNC (SYSDATE - 1) -- PLSQL Warning says its some column conversion issue on this step
AND TRUNC (SYSDATE)
ORDER BY oola.creation_date ASC;