select sum(C.compl_qty*D.bkc_unit_price) bkc_amt from (select nvl(sum(compl_qty),0) compl_qty,item_no from completion_trans where item_no in ('A1065336A','A1065340A','A7078598B') and joc_cd='DC001223' and sc_cd='AC001' and ws_compl_dte=20040604 group by item_no ) C, (select nvl(sum(decode(sign(i.std_cost_eff_dte -20040604),-1,i.std_cost2,i.std_cost1) * decode(m.usage_type,0,m.usage_dec,m.usage_numerator/m.usage_denominator)),0) bkc_unit_price,m.bkc_cd bkc_cd from mfg_bom_master m, item_master i where m.bkc_cd in ('A1065336A','A1065340A','A7078598B') and i.item_no=m.child_item_cd group by bkc_cd ) D where C.item_no=D.bkc_cd
from (select nvl(sum(compl_qty),0) compl_qty,item_no
from completion_trans
where item_no in ('A1065336A','A1065340A','A7078598B')
and joc_cd='DC001223'
and sc_cd='AC001'
and ws_compl_dte=20040604
group by item_no
) C,
(select nvl(sum(decode(sign(i.std_cost_eff_dte -20040604),-1,i.std_cost2,i.std_cost1)
* decode(m.usage_type,0,m.usage_dec,m.usage_numerator/m.usage_denominator)),0) bkc_unit_price,m.bkc_cd bkc_cd
from mfg_bom_master m, item_master i
where m.bkc_cd in ('A1065336A','A1065340A','A7078598B')
and i.item_no=m.child_item_cd
group by bkc_cd
) D
where C.item_no=D.bkc_cd