Query to find shipset and fulfilment set

1SELECT order_number,
2         ship_set_id,
3         set_id fullfillment_set_id,
4         COUNT (DISTINCT PAYMENT_TERM_ID)
5    FROM (SELECT ship_set_id,
6                 order_number,
7                 line_number,
8                 ool.payment_term_id,
9                 set_id
10            FROM oe_order_headers_all ooh,
11                 oe_order_lines_all ool,
12                 (SELECT s.set_id,
13                         s.set_name,
14                         set_status,
15                         s.header_id,
16                         (SELECT open_flag
17                            FROM oe_order_lines_all lin
18                           WHERE lin.line_id = ls.line_id)
19                            line_open,
20                         ls.line_id
21                    FROM oe_sets s, oe_line_sets ls, oe_order_headers_all oh
22                   WHERE     s.set_id = ls.set_id
23                         AND s.set_type = 'FULFILLMENT_SET'
24                         AND s.header_id = oh.header_id) fullfillment_set
25           WHERE     ooh.header_id = ool.header_id
26                 AND ooh.org_id = 1234            -- and order_number=92000065
27                 AND fullfillment_set.line_id = ool.line_id)
28GROUP BY order_number, ship_set_id, set_id
29  HAVING COUNT (DISTINCT PAYMENT_TERM_ID) > 1