Sales order line status query in Oracle apps

1--Sales order line status query
2SELECT CASE
3          WHEN     flow_status_code <> 'AWAITING_SHIPPING'
4               AND flow_status_code <> 'PRODUCTION_COMPLETE'
5               AND flow_status_code <> 'PICKED'
6               AND flow_status_code <> 'PICKED_PARTIAL'
7               AND flow_status_code <> 'PO_RECEIVED'
8          THEN
9             (SELECT meaning
10                FROM apps.fnd_lookup_values lv
11               WHERE     lookup_type = 'LINE_FLOW_STATUS'
12                     AND lookup_code = flow_status_code
13                     AND LANGUAGE = USERENV ('LANG')
14                     AND VIEW_APPLICATION_ID = 660)
15          ELSE
16             (SELECT NVL (
17                        (SELECT meaning
18                           FROM fnd_lookup_values lv
19                          WHERE     lookup_type = 'LINE_FLOW_STATUS'
20                                AND lookup_code = 'PICKED'
21                                AND LANGUAGE = USERENV ('LANG')
22                                AND VIEW_APPLICATION_ID = 660
23                                AND (SELECT SUM (
24                                               DECODE (released_status,
25                                                       'Y', 1,
26                                                       'C', 1,
27                                                       0))
28                                       FROM wsh_delivery_details
29                                      WHERE     source_line_id =
30                                                   oeol.line_id
31                                            AND source_code = 'OE'
32                                            AND released_status <> 'D') =
33                                       (SELECT SUM (1)
34                                          FROM wsh_delivery_details
35                                         WHERE     source_line_id =
36                                                      oeol.line_id
37                                               AND source_code = 'OE'
38                                               AND released_status <> 'D')
39                         UNION
40                         SELECT meaning
41                           FROM fnd_lookup_values lv
42                          WHERE     lookup_type = 'LINE_FLOW_STATUS'
43                                AND lookup_code = 'PICKED_PARTIAL'
44                                AND LANGUAGE = USERENV ('LANG')
45                                AND VIEW_APPLICATION_ID = 660
46                                AND (SELECT SUM (
47                                               DECODE (released_status,
48                                                       'Y', 1,
49                                                       'C', 1,
50                                                       0))
51                                       FROM wsh_delivery_details
52                                      WHERE     source_line_id = oeol.line_id
53                                            AND source_code = 'OE'
54                                            AND released_status <> 'D') <
55                                       (SELECT SUM (1)
56                                          FROM wsh_delivery_details
57                                         WHERE     source_line_id =
58                                                      oeol.line_id
59                                               AND source_code = 'OE'
60                                               AND released_status <> 'D')
61                                AND (SELECT SUM (
62                                               DECODE (released_status,
63                                                       'Y', 1,
64                                                       'C', 1,
65                                                       0))
66                                       FROM wsh_delivery_details
67                                      WHERE     source_line_id = oeol.line_id
68                                            AND source_code = 'OE'
69                                            AND released_status <> 'D') <> 0),
70                        (SELECT meaning
71                           FROM fnd_lookup_values lv
72                          WHERE     lookup_type = 'LINE_FLOW_STATUS'
73                                AND lookup_code = flow_status_code
74                                AND LANGUAGE = USERENV ('LANG')
75                                AND VIEW_APPLICATION_ID = 660))
76                FROM DUAL)
77       END
78          line_status
79  FROM oe_order_lines_all oeol
80 WHERE header_id = 1234