Query to get details of WIP discrete jobs

1SELECT wdj.wip_entity_id  cum_entity_id,
2         wdj.organization_id cum_org_id,
3         ml_elem_type.meaning elem_var_type,
4         SUM (
5            ROUND (
6               DECODE (
7                  ml_elem_type.lookup_code,
8                  1, NVL (wpb.pl_material_in, 0),
9                  2, NVL (wpb.pl_material_overhead_in, 0),
10                  3, (NVL (wpb.pl_resource_in, 0) + NVL (wpb.tl_resource_in, 0)),
11                  4, (  NVL (wpb.pl_outside_processing_in, 0)
12                      + NVL (wpb.tl_outside_processing_in, 0)),
13                  5, (NVL (wpb.pl_overhead_in, 0) + NVL (wpb.tl_overhead_in, 0)),
14                  12, NVL (wpb.tl_scrap_in, 0),
15                  NULL),
16               :PRECISION))
17            costs_in,
18         SUM (
19            ROUND (
20               DECODE (
21                  ml_elem_type.lookup_code,
22                  1, (  NVL (wpb.pl_material_out, 0)
23                      + NVL (wpb.tl_material_out, 0)),
24                  2, (  NVL (wpb.pl_material_overhead_out, 0)
25                      + NVL (wpb.tl_material_overhead_out, 0)),
26                  3, (  NVL (wpb.pl_resource_out, 0)
27                      + NVL (wpb.tl_resource_out, 0)),
28                  4, (  NVL (wpb.pl_outside_processing_out, 0)
29                      + NVL (wpb.tl_outside_processing_out, 0)),
30                  5, (  NVL (wpb.pl_overhead_out, 0)
31                      + NVL (wpb.tl_overhead_out, 0)),
32                  12, NVL (wpb.tl_scrap_out, 0),
33                  NULL),
34               :PRECISION))
35            costs_out,
36         SUM (
37            ROUND (
38               DECODE (
39                  ml_elem_type.lookup_code,
40                  1, (  NVL (wpb.pl_material_var, 0)
41                      + NVL (wpb.tl_material_var, 0)),
42                  2, (  NVL (wpb.pl_material_overhead_var, 0)
43                      + NVL (wpb.tl_material_overhead_var, 0)),
44                  3, (  NVL (wpb.pl_resource_var, 0)
45                      + NVL (wpb.tl_resource_var, 0)),
46                  4, (  NVL (wpb.pl_outside_processing_var, 0)
47                      + NVL (wpb.tl_outside_processing_var, 0)),
48                  5, (  NVL (wpb.pl_overhead_var, 0)
49                      + NVL (wpb.tl_overhead_var, 0)),
50                  12, NVL (wpb.tl_scrap_var, 0),
51                  NULL),
52               :PRECISION))
53            costs_var,
54         SUM (
55            ROUND (
56               DECODE (
57                  ml_elem_type.lookup_code,
58                  1, (  NVL (wpb.pl_material_in, 0)
59                      - NVL (wpb.pl_material_out, 0)
60                      - NVL (wpb.tl_material_out, 0)),
61                  2, (  NVL (wpb.pl_material_overhead_in, 0)
62                      - NVL (wpb.pl_material_overhead_out, 0)
63                      - NVL (wpb.tl_material_overhead_out, 0)),
64                  3, (  NVL (wpb.pl_resource_in, 0)
65                      + NVL (wpb.tl_resource_in, 0)
66                      - NVL (wpb.pl_resource_out, 0)
67                      - NVL (wpb.tl_resource_out, 0)),
68                  4, (  NVL (wpb.pl_outside_processing_in, 0)
69                      + NVL (wpb.tl_outside_processing_in, 0)
70                      - NVL (wpb.pl_outside_processing_out, 0)
71                      - NVL (wpb.tl_outside_processing_out, 0)),
72                  5, (  NVL (wpb.pl_overhead_in, 0)
73                      + NVL (wpb.tl_overhead_in, 0)
74                      - NVL (wpb.pl_overhead_out, 0)
75                      - NVL (wpb.tl_overhead_out, 0)),
76                  12, (NVL (wpb.tl_scrap_in, 0) - NVL (wpb.tl_scrap_out, 0)),
77                  NULL),
78               :PRECISION))
79            period_act,
80         SUM (
81            ROUND (
82               DECODE (
83                  ml_elem_type.lookup_code,
84                  6,   NVL (wpb.tl_material_var, 0)
85                     + NVL (wpb.tl_material_overhead_var, 0)
86                     + NVL (wpb.pl_material_var, 0)
87                     + NVL (wpb.pl_material_overhead_var, 0)
88                     + NVL (wpb.pl_resource_var, 0)
89                     + NVL (wpb.pl_overhead_var, 0)
90                     + NVL (wpb.pl_outside_processing_var, 0),
91                  7, NVL (wpb.tl_resource_var, 0),
92                  8, NVL (wpb.tl_outside_processing_var, 0),
93                  9, NVL (wpb.tl_overhead_var, 0),
94                  13, NVL (wpb.tl_scrap_var, 0),
95                  NULL),
96               :PRECISION))
97            single_level
98    FROM wip_period_balances wpb,
99         wip_discrete_jobs  wdj,
100         gl_code_combinations gcc,
101         mfg_lookups        ml_elem_type
102   WHERE     wdj.wip_entity_id = wpb.wip_entity_id
103         AND wdj.WIP_ENTITY_ID = 101791
104         AND wpb.organization_id = 123
105         AND ml_elem_type.lookup_type = 'WIP_ELEMENT_VAR_TYPE'
106         AND DECODE (ml_elem_type.lookup_code,
107                     1, wdj.material_account,
108                     2, wdj.material_overhead_account,
109                     3, wdj.resource_account,
110                     4, wdj.outside_processing_account,
111                     5, wdj.overhead_account,
112                     6, wdj.material_variance_account,
113                     7, wdj.resource_variance_account,
114                     8, wdj.outside_proc_variance_account,
115                     9, wdj.overhead_variance_account,
116                     12, wdj.est_scrap_account,
117                     13, wdj.est_scrap_var_account) = gcc.code_combination_id
118GROUP BY wdj.wip_entity_id,
119         wdj.organization_id,
120         ml_elem_type.meaning,
121         ml_elem_type.lookup_code
122ORDER BY ml_elem_type.lookup_code