select inv_status,
depo_id,
depo_name,
good_code,
type_id,
type_name,
steel_num,
specs,
brand_id,
brand_name,
sccj_id,
sccj_name,
business_pattern_id,
business_pattern_name,
quality_grade,
quality_grade_name,
sales_grade,
sales_grade_name,
pt_id,
zt_id,
nvl(qty, 0) qty,
nvl(pie, 0) pie,
nvl(ck_pie, 0) ck_pie,
nvl(ck_qty, 0) ck_qty,
nvl(rk_pie, 0) rk_pie,
nvl(rk_qty, 0) rk_qty,
nvl(kd_qty, 0) kd_qty,
nvl(kd_pie, 0) kd_pie,
nvl(wth_qty, 0) wth_qty,
nvl(wth_pie, 0) wth_pie,
nvl(kgy_qty, 0) kgy_qty,
nvl(kgy_pie, 0) kgy_pie
from (select inv_status,
depo_id,
depo_name,
good_code,
type_id,
type_name,
steel_num,
specs,
brand_id,
brand_name,
sccj_id,
sccj_name,
business_pattern_id,
business_pattern_name,
quality_grade,
quality_grade_name,
sales_grade,
sales_grade_name,
pt_id,
zt_id,
(sum(rk_qty) - sum(ck_qty)) qty,
(sum(rk_pie) - sum(ck_pie)) pie,
sum(ck_pie) ck_pie,
sum(ck_qty) ck_qty,
sum(rk_pie) rk_pie,
sum(rk_qty) rk_qty,
(select sum(co_pick_qty)
from cx032
where cx032.pt_id = wx001.pt_id
and (select depo_id
from cx031
where cx031.pt_id = cx032.pt_id
and cx031.co_pick_num = cx032.co_pick_num) =
wx001.depo_id
and cx032.good_code = wx001.good_code
and cx032.steel_num = wx001.steel_num
and cx032.specs = wx001.specs
and cx032.type_id = wx001.type_id
and cx032.sccj_id = wx001.sccj_id
and cx032.brand_id = wx001.brand_id
and cx032.quality_grade = wx001.quality_grade
and cx032.sales_grade = wx001.sales_grade
and cx032.business_pattern_id = wx001.business_pattern_id
and cx032.co_pick_status >= '20'
and cx032.is_pound_diff = '0') kd_qty,
(select sum(co_pick_pie)
from cx032
where cx032.pt_id = wx001.pt_id
and (select depo_id
from cx031
where cx031.pt_id = cx032.pt_id
and cx031.co_pick_num = cx032.co_pick_num) =
wx001.depo_id
and cx032.good_code = wx001.good_code
and cx032.steel_num = wx001.steel_num
and cx032.specs = wx001.specs
and cx032.type_id = wx001.type_id
and cx032.sccj_id = wx001.sccj_id
and cx032.brand_id = wx001.brand_id
and cx032.quality_grade = wx001.quality_grade
and cx032.sales_grade = wx001.sales_grade
and cx032.business_pattern_id = wx001.business_pattern_id
and cx032.co_pick_status >= '20'
and cx032.is_pound_diff = '0') kd_pie,
(select sum(co_pick_qty)
from cx032
where cx032.pt_id = wx001.pt_id
and (select depo_id
from cx031
where cx031.pt_id = cx032.pt_id
and cx031.co_pick_num = cx032.co_pick_num) =
wx001.depo_id
and cx032.good_code = wx001.good_code
and cx032.steel_num = wx001.steel_num
and cx032.specs = wx001.specs
and cx032.type_id = wx001.type_id
and cx032.sccj_id = wx001.sccj_id
and cx032.brand_id = wx001.brand_id
and cx032.quality_grade = wx001.quality_grade
and cx032.sales_grade = wx001.sales_grade
and cx032.business_pattern_id = wx001.business_pattern_id
and cx032.co_ck_qty = 0
and cx032.co_pick_status >= '20'
and cx032.is_pound_diff = '0') wth_qty,
(select sum(co_pick_pie)
from cx032
where cx032.pt_id = wx001.pt_id
and (select depo_id
from cx031
where cx031.pt_id = cx032.pt_id
and cx031.co_pick_num = cx032.co_pick_num) =
wx001.depo_id
and cx032.good_code = wx001.good_code
and cx032.steel_num = wx001.steel_num
and cx032.specs = wx001.specs
and cx032.type_id = wx001.type_id
and cx032.sccj_id = wx001.sccj_id
and cx032.brand_id = wx001.brand_id
and cx032.quality_grade = wx001.quality_grade
and cx032.sales_grade = wx001.sales_grade
and cx032.business_pattern_id = wx001.business_pattern_id
and cx032.co_ck_qty = 0
and cx032.co_pick_status >= '20'
and cx032.is_pound_diff = '0') wth_pie,
((sum(rk_qty) - sum(ck_qty)) -
nvl((select sum(co_pick_qty)
from cx032
where cx032.pt_id = wx001.pt_id
and (select depo_id
from cx031
where cx031.pt_id = cx032.pt_id
and cx031.co_pick_num = cx032.co_pick_num) =
wx001.depo_id
and cx032.good_code = wx001.good_code
and cx032.steel_num = wx001.steel_num
and cx032.specs = wx001.specs
and cx032.type_id = wx001.type_id
and cx032.sccj_id = wx001.sccj_id
and cx032.brand_id = wx001.brand_id
and cx032.quality_grade = wx001.quality_grade
and cx032.sales_grade = wx001.sales_grade
and cx032.business_pattern_id = wx001.business_pattern_id
and cx032.co_ck_qty = 0
and cx032.co_pick_status >= '20'
and cx032.is_pound_diff = '0'),0)) kgy_qty,
((sum(rk_pie) - sum(ck_pie)) -
nvl((select sum(co_pick_pie)
from cx032
where cx032.pt_id = wx001.pt_id
and (select depo_id
from cx031
where cx031.pt_id = cx032.pt_id
and cx031.co_pick_num = cx032.co_pick_num) =
wx001.depo_id
and cx032.good_code = wx001.good_code
and cx032.steel_num = wx001.steel_num
and cx032.specs = wx001.specs
and cx032.type_id = wx001.type_id
and cx032.sccj_id = wx001.sccj_id
and cx032.brand_id = wx001.brand_id
and cx032.quality_grade = wx001.quality_grade
and cx032.sales_grade = wx001.sales_grade
and cx032.business_pattern_id = wx001.business_pattern_id
and cx032.co_ck_qty = 0
and cx032.co_pick_status >= '20'
and cx032.is_pound_diff = '0'),0)) kgy_pie
from wx001
group by inv_status,
depo_id,
depo_name,
good_code,
type_id,
type_name,
steel_num,
specs,
brand_id,
brand_name,
sccj_id,
sccj_name,
business_pattern_id,
business_pattern_name,
quality_grade,
quality_grade_name,
sales_grade,
sales_grade_name,
pt_id,
zt_id)
where 1 = 1
depo_id,
depo_name,
good_code,
type_id,
type_name,
steel_num,
specs,
brand_id,
brand_name,
sccj_id,
sccj_name,
business_pattern_id,
business_pattern_name,
quality_grade,
quality_grade_name,
sales_grade,
sales_grade_name,
pt_id,
zt_id,
nvl(qty, 0) qty,
nvl(pie, 0) pie,
nvl(ck_pie, 0) ck_pie,
nvl(ck_qty, 0) ck_qty,
nvl(rk_pie, 0) rk_pie,
nvl(rk_qty, 0) rk_qty,
nvl(kd_qty, 0) kd_qty,
nvl(kd_pie, 0) kd_pie,
nvl(wth_qty, 0) wth_qty,
nvl(wth_pie, 0) wth_pie,
nvl(kgy_qty, 0) kgy_qty,
nvl(kgy_pie, 0) kgy_pie
from (select inv_status,
depo_id,
depo_name,
good_code,
type_id,
type_name,
steel_num,
specs,
brand_id,
brand_name,
sccj_id,
sccj_name,
business_pattern_id,
business_pattern_name,
quality_grade,
quality_grade_name,
sales_grade,
sales_grade_name,
pt_id,
zt_id,
(sum(rk_qty) - sum(ck_qty)) qty,
(sum(rk_pie) - sum(ck_pie)) pie,
sum(ck_pie) ck_pie,
sum(ck_qty) ck_qty,
sum(rk_pie) rk_pie,
sum(rk_qty) rk_qty,
(select sum(co_pick_qty)
from cx032
where cx032.pt_id = wx001.pt_id
and (select depo_id
from cx031
where cx031.pt_id = cx032.pt_id
and cx031.co_pick_num = cx032.co_pick_num) =
wx001.depo_id
and cx032.good_code = wx001.good_code
and cx032.steel_num = wx001.steel_num
and cx032.specs = wx001.specs
and cx032.type_id = wx001.type_id
and cx032.sccj_id = wx001.sccj_id
and cx032.brand_id = wx001.brand_id
and cx032.quality_grade = wx001.quality_grade
and cx032.sales_grade = wx001.sales_grade
and cx032.business_pattern_id = wx001.business_pattern_id
and cx032.co_pick_status >= '20'
and cx032.is_pound_diff = '0') kd_qty,
(select sum(co_pick_pie)
from cx032
where cx032.pt_id = wx001.pt_id
and (select depo_id
from cx031
where cx031.pt_id = cx032.pt_id
and cx031.co_pick_num = cx032.co_pick_num) =
wx001.depo_id
and cx032.good_code = wx001.good_code
and cx032.steel_num = wx001.steel_num
and cx032.specs = wx001.specs
and cx032.type_id = wx001.type_id
and cx032.sccj_id = wx001.sccj_id
and cx032.brand_id = wx001.brand_id
and cx032.quality_grade = wx001.quality_grade
and cx032.sales_grade = wx001.sales_grade
and cx032.business_pattern_id = wx001.business_pattern_id
and cx032.co_pick_status >= '20'
and cx032.is_pound_diff = '0') kd_pie,
(select sum(co_pick_qty)
from cx032
where cx032.pt_id = wx001.pt_id
and (select depo_id
from cx031
where cx031.pt_id = cx032.pt_id
and cx031.co_pick_num = cx032.co_pick_num) =
wx001.depo_id
and cx032.good_code = wx001.good_code
and cx032.steel_num = wx001.steel_num
and cx032.specs = wx001.specs
and cx032.type_id = wx001.type_id
and cx032.sccj_id = wx001.sccj_id
and cx032.brand_id = wx001.brand_id
and cx032.quality_grade = wx001.quality_grade
and cx032.sales_grade = wx001.sales_grade
and cx032.business_pattern_id = wx001.business_pattern_id
and cx032.co_ck_qty = 0
and cx032.co_pick_status >= '20'
and cx032.is_pound_diff = '0') wth_qty,
(select sum(co_pick_pie)
from cx032
where cx032.pt_id = wx001.pt_id
and (select depo_id
from cx031
where cx031.pt_id = cx032.pt_id
and cx031.co_pick_num = cx032.co_pick_num) =
wx001.depo_id
and cx032.good_code = wx001.good_code
and cx032.steel_num = wx001.steel_num
and cx032.specs = wx001.specs
and cx032.type_id = wx001.type_id
and cx032.sccj_id = wx001.sccj_id
and cx032.brand_id = wx001.brand_id
and cx032.quality_grade = wx001.quality_grade
and cx032.sales_grade = wx001.sales_grade
and cx032.business_pattern_id = wx001.business_pattern_id
and cx032.co_ck_qty = 0
and cx032.co_pick_status >= '20'
and cx032.is_pound_diff = '0') wth_pie,
((sum(rk_qty) - sum(ck_qty)) -
nvl((select sum(co_pick_qty)
from cx032
where cx032.pt_id = wx001.pt_id
and (select depo_id
from cx031
where cx031.pt_id = cx032.pt_id
and cx031.co_pick_num = cx032.co_pick_num) =
wx001.depo_id
and cx032.good_code = wx001.good_code
and cx032.steel_num = wx001.steel_num
and cx032.specs = wx001.specs
and cx032.type_id = wx001.type_id
and cx032.sccj_id = wx001.sccj_id
and cx032.brand_id = wx001.brand_id
and cx032.quality_grade = wx001.quality_grade
and cx032.sales_grade = wx001.sales_grade
and cx032.business_pattern_id = wx001.business_pattern_id
and cx032.co_ck_qty = 0
and cx032.co_pick_status >= '20'
and cx032.is_pound_diff = '0'),0)) kgy_qty,
((sum(rk_pie) - sum(ck_pie)) -
nvl((select sum(co_pick_pie)
from cx032
where cx032.pt_id = wx001.pt_id
and (select depo_id
from cx031
where cx031.pt_id = cx032.pt_id
and cx031.co_pick_num = cx032.co_pick_num) =
wx001.depo_id
and cx032.good_code = wx001.good_code
and cx032.steel_num = wx001.steel_num
and cx032.specs = wx001.specs
and cx032.type_id = wx001.type_id
and cx032.sccj_id = wx001.sccj_id
and cx032.brand_id = wx001.brand_id
and cx032.quality_grade = wx001.quality_grade
and cx032.sales_grade = wx001.sales_grade
and cx032.business_pattern_id = wx001.business_pattern_id
and cx032.co_ck_qty = 0
and cx032.co_pick_status >= '20'
and cx032.is_pound_diff = '0'),0)) kgy_pie
from wx001
group by inv_status,
depo_id,
depo_name,
good_code,
type_id,
type_name,
steel_num,
specs,
brand_id,
brand_name,
sccj_id,
sccj_name,
business_pattern_id,
business_pattern_name,
quality_grade,
quality_grade_name,
sales_grade,
sales_grade_name,
pt_id,
zt_id)
where 1 = 1