We help IT Professionals succeed at work.
Get Started

Oracle Summarized explosion with Prior

Vinum
Vinum asked
on
282 Views
Last Modified: 2014-05-07
I have to calculate a summarized Bill of material. That is a structure of fx. a machine and I have to calculate the amount of every material summarized in the structure.

Data:
Niveau;Component Part;Qty Per Assembly
0;TEST_NIV0;1
.1;TEST-NIV1;1
..2;TEST-NIV2;      2
...3;TEST-NIV3;3
....4;TEST-NIV4;4
.....5;TEST-NIV5;5
......6;TEST-NIV6;6
.......7;TEST-NIV7;7
........8;TEST-NIV8;8
.........9;TEST-NIV9;9
..........10;TEST-NIV10;10

I have 1 TEST-NIV0. That consist of 1 TEST-NIV1.
TEST-NIV1 consist of 2 TEST-NIV2 - in total, there is 2 (1*1*2) of TEST-NIV2
TEST-NIV2 consist of 3 TEST-NIV3 - in total, there is  6 (1*1*2*3) of TEST-NIV3
TEST-NIV4 consist of 4 TEST-NIV4 - in total, there is  24  (1*1*2*3*4) of TEST-NIV4
and so on...

My program:

select 
2 as srt,
a.COMPONENT_PART as CP,
case 
       when level = 1 then a.QTY_PER_ASSEMBLY
       else PRIOR a.QTY_PER_ASSEMBLY * a.QTY_PER_ASSEMBLY
end as qty,
prior a.bom_type,
a.bom_type,
a.qty_per_assembly,
prior a.qty_per_assembly
from KK.prod_STRUCTURE a
where a.alternative_no = '*'

CONNECT BY
PRIOR a.COMPONENT_PART = a.PART_NO AND  
PRIOR a.CONTRACT = a.CONTRACT AND  
PRIOR a.BOM_TYPE = a.BOM_TYPE 

START WITH a.CONTRACT = upper ('&SITE') 
and  a.PART_NO in upper('&PART_NO')
order by cp

Open in new window


Result - please see attached file.
The column "IS" is the result of the above, but I want the column "SHOULD BE".
See the formulas in the SUM.XLSX and a description in SUM.JPG

The problem is that I do not get the in-between sums.
Is that possible with or do I have to use a CURSOR?
Sum.xlsx
SUM.jpg
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE