Query to get Order numbers with service lines which have not created a contract and are not in OKS_REPROCESSING table.

Query to get Order numbers with service lines which have not created a contract and are not in OKS_REPROCESSING table. 

select ord.order_number "Order_Number",

ord.header_id "Order_Id",

line.line_number,

instance_number,

line.service_reference_type_code,

line.service_reference_line_id "Reference ID",

line_id "Service_Line_ID"

from csi_item_instances ib,

oe_order_lines_all line,

oe_order_headers_all ord

where not ib.last_oe_order_line_id is null

and ib.last_oe_order_line_id = line.service_reference_line_id

and ord.header_id = line.header_id

and line.flow_status_code = 'CLOSED'

and line.service_reference_type_code = 'ORDER'

and line.line_id not in

(select order_line_id from oks_reprocessing);