NC6.5投入产出分析表

select  '0001O1100000000027VN' pk_org,nvl(a.cbzx,b.cbzx2) cbzx1,nvl(a.wgwl,b.wgwl2) wgwl1,a.*,b.* ,e.code wlbm,e.name wlmc,e.materialspec wlgg,e.materialtype wlxh,d.nitemnum/d.ibasenum desl  ,
d.nitemnum/d.ibasenum*a.wgsl dezs,
case when nvl(a.wgsl,0)=0 then 0 else nvl(b.cksl,0)/a.wgsl end sjdwyl,
case when nvl(b.cksl,0)=0 and decode(d.ibasenum,null,0,d.nitemnum/d.ibasenum*a.wgsl) =0 then 9999
else (nvl(b.cksl,0)-decode(d.ibasenum,null,0,d.nitemnum/d.ibasenum*a.wgsl)) end cyl



from  (
select distinct b.ccostcenterid cbzx,b.cinventoryvid wgwl,sum(b.nnum)over(partition by b.ccostcenterid,b.cinventoryvid ) wgsl from ia_i3bill a
inner join ia_i3bill_b b on a.cbillid=b.cbillid and b.dr=0
inner join resa_costcenter c on b.ccostcenterid=c.pk_costcenter and c.dr=0
where substr(b.dbilldate,1,10)>=parameter('date1') and  substr(b.dbilldate,1,10)<=parameter('date2')
and a.cstockorgid='0001O1100000000027VN'
and a.ctrantypeid<>'1001O11000000000U5JK'
) a
full join 
 (
select distinct b.ccostcenterid cbzx2,b.cinventoryvid ckwl,sum(b.nnum)over(partition by b.ccostcenterid,b.cinventoryvid ,b.ccostobjid) cksl,b.ccostobjid wgwl2 from ia_i6bill a 
inner join ia_i6bill_b b on a.cbillid=b.cbillid and b.dr=0
inner join resa_costcenter c on b.ccostcenterid=c.pk_costcenter and c.dr=0
inner join bd_material e on b.cinventoryvid=e.pk_material
where b.ccostobjid<>'~'
and substr(b.dbilldate,1,10)>=parameter('date1') and  substr(b.dbilldate,1,10)<=parameter('date2')
and a.cstockorgid='0001O1100000000027VN'
) b on a.cbzx=b.cbzx2 and a.wgwl=b.wgwl2

left join bd_bom c on nvl(a.wgwl,b.wgwl2)=c.hcmaterialvid  and c.dr=0 and c.hbdefault ='Y'
left join bd_bom_b d on c.cbomid=d.cbomid and b.ckwl=d.cmaterialvid  and d.dr=0
left join bd_material e on d.cmaterialvid=e.pk_material
left join bd_defdoc f on e.def1=f.pk_defdoc and f.dr=0

适配优化

select 
t2.code 产品编码,t2.name 产品名称,t2.materialspec 产品规格,t2.materialtype 产品型号,t3.name as bb,
t4.code 材料编码,t4.name 材料名称,t4.materialspec 材料规格,t4.materialtype 材料型号,
t5.name as 物料分类,t6.name as 大分类,
t1.公司,
t1.成本中心,
case  
    when t1.成本中心 like '委外%' then '委外'
    when t1.成本中心 like '%板%' then '板材'
    else '注塑' end as 成本域,
t1.WGSL,t1.CKSL,t1.DESL,t1.DEZS,t1.SJDWYL,t1.CYL 
--t1.*
from (
select  '0001O1100000000027VN' pk_org,'新乡市仲达塑胶电子有限公司' 公司,g.ccname as 成本中心,nvl(a.cbzx,b.cbzx2) cbzx1,nvl(a.wgwl,b.wgwl2) wgwl1,a.*,b.* ,e.code wlbm,e.name wlmc,e.materialspec wlgg,e.materialtype wlxh,d.nitemnum/d.ibasenum desl  ,
d.nitemnum/d.ibasenum*a.wgsl dezs,
case when nvl(a.wgsl,0)=0 then 0 else nvl(b.cksl,0)/a.wgsl end sjdwyl,
case when nvl(b.cksl,0)=0 and decode(d.ibasenum,null,0,d.nitemnum/d.ibasenum*a.wgsl) =0 then 9999
else (nvl(b.cksl,0)-decode(d.ibasenum,null,0,d.nitemnum/d.ibasenum*a.wgsl)) end cyl



from  (
select distinct b.ccostcenterid cbzx,b.cinventoryvid wgwl,sum(b.nnum)over(partition by b.ccostcenterid,b.cinventoryvid ) wgsl from ia_i3bill a
inner join ia_i3bill_b b on a.cbillid=b.cbillid and b.dr=0
inner join resa_costcenter c on b.ccostcenterid=c.pk_costcenter and c.dr=0
where 1=1 
--and substr(b.dbilldate,1,10)>=parameter('date1') and  substr(b.dbilldate,1,10)<=parameter('date2')
and substr(b.dbilldate,1,10)>='2025-10-01' and  substr(b.dbilldate,1,10)<='2025-10-31'
and a.cstockorgid='0001O1100000000027VN'
and a.ctrantypeid<>'1001O11000000000U5JK'
) a
full join 
 (
select distinct b.ccostcenterid cbzx2,b.cinventoryvid ckwl,sum(b.nnum)over(partition by b.ccostcenterid,b.cinventoryvid ,b.ccostobjid) cksl,b.ccostobjid wgwl2 from ia_i6bill a 
inner join ia_i6bill_b b on a.cbillid=b.cbillid and b.dr=0
inner join resa_costcenter c on b.ccostcenterid=c.pk_costcenter and c.dr=0
inner join bd_material e on b.cinventoryvid=e.pk_material
where b.ccostobjid<>'~'
--and substr(b.dbilldate,1,10)>=parameter('date1') and  substr(b.dbilldate,1,10)<=parameter('date2')
and substr(b.dbilldate,1,10)>='2025-10-01' and  substr(b.dbilldate,1,10)<='2025-10-31'
and a.cstockorgid='0001O1100000000027VN'
) b on a.cbzx=b.cbzx2 and a.wgwl=b.wgwl2

left join bd_bom c on nvl(a.wgwl,b.wgwl2)=c.hcmaterialvid  and c.dr=0 and c.hbdefault ='Y'
left join bd_bom_b d on c.cbomid=d.cbomid and b.ckwl=d.cmaterialvid  and d.dr=0
left join bd_material e on d.cmaterialvid=e.pk_material
left join bd_defdoc f on e.def1=f.pk_defdoc and f.dr=0
left join resa_costcenter g on nvl(a.cbzx,b.cbzx2) = g.pk_costcenter
) t1 
left join bd_material t2 on t1.wgwl1=t2.pk_material
left join bd_defdoc t3 on t2.def1=t3.pk_defdoc and t3.dr=0
left join bd_material t4 on t1.ckwl=t4.pk_material
left join bd_marbasclass t5 on t4.pk_marbasclass = t5.pk_marbasclass 
left join bd_marbasclass t6 on t5.pk_parent=t6.pk_marbasclass 
-- resa_costcenter 成本中心
-- org_orgs 组织
-- bd_material 物料表
-- bd_marbasclass 物料分类表

与BOM形成全外查询 涵盖全部物料对比

with tab1 as(
select 
t2.code 产品编码,t2.name 产品名称,t2.materialspec 产品规格,t2.materialtype 产品型号,t3.name as bb,
t4.code 材料编码,t4.name 材料名称,t4.materialspec 材料规格,t4.materialtype 材料型号,

t1.公司,
t1.成本中心,
case  
    when t1.成本中心 like '委外%' then '委外'
    when t1.成本中心 like '%板%' then '板材'
    else '注塑' end as 成本域,
t1.WGSL,t1.CKSL 
--t1.*
from (
select  '0001O1100000000027VN' pk_org,'新乡市仲达塑胶电子有限公司' 公司,g.ccname as 成本中心,nvl(a.cbzx,b.cbzx2) cbzx1,nvl(a.wgwl,b.wgwl2) wgwl1,a.*,b.* ,e.code wlbm,e.name wlmc,e.materialspec wlgg,e.materialtype wlxh,d.nitemnum/d.ibasenum desl  ,
d.nitemnum/d.ibasenum*a.wgsl dezs,
case when nvl(a.wgsl,0)=0 then 0 else nvl(b.cksl,0)/a.wgsl end sjdwyl,
case when nvl(b.cksl,0)=0 and decode(d.ibasenum,null,0,d.nitemnum/d.ibasenum*a.wgsl) =0 then 9999
else (nvl(b.cksl,0)-decode(d.ibasenum,null,0,d.nitemnum/d.ibasenum*a.wgsl)) end cyl



from  (
select distinct b.ccostcenterid cbzx,b.cinventoryvid wgwl,sum(b.nnum)over(partition by b.ccostcenterid,b.cinventoryvid ) wgsl from ia_i3bill a
inner join ia_i3bill_b b on a.cbillid=b.cbillid and b.dr=0
inner join resa_costcenter c on b.ccostcenterid=c.pk_costcenter and c.dr=0
where 1=1 
--and substr(b.dbilldate,1,10)>=parameter('date1') and  substr(b.dbilldate,1,10)<=parameter('date2')
and substr(b.dbilldate,1,10)>='2025-10-01' and  substr(b.dbilldate,1,10)<='2025-10-31'
and a.cstockorgid='0001O1100000000027VN'
and a.ctrantypeid<>'1001O11000000000U5JK'
) a
full join 
 (
select distinct b.ccostcenterid cbzx2,b.cinventoryvid ckwl,sum(b.nnum)over(partition by b.ccostcenterid,b.cinventoryvid ,b.ccostobjid) cksl,b.ccostobjid wgwl2 from ia_i6bill a 
inner join ia_i6bill_b b on a.cbillid=b.cbillid and b.dr=0
inner join resa_costcenter c on b.ccostcenterid=c.pk_costcenter and c.dr=0
inner join bd_material e on b.cinventoryvid=e.pk_material
where b.ccostobjid<>'~'
--and substr(b.dbilldate,1,10)>=parameter('date1') and  substr(b.dbilldate,1,10)<=parameter('date2')
and substr(b.dbilldate,1,10)>='2025-10-01' and  substr(b.dbilldate,1,10)<='2025-10-31'
and a.cstockorgid='0001O1100000000027VN'
) b on a.cbzx=b.cbzx2 and a.wgwl=b.wgwl2

left join bd_bom c on nvl(a.wgwl,b.wgwl2)=c.hcmaterialvid  and c.dr=0 and c.hbdefault ='Y'
left join bd_bom_b d on c.cbomid=d.cbomid and b.ckwl=d.cmaterialvid  and d.dr=0
left join bd_material e on d.cmaterialvid=e.pk_material
left join bd_defdoc f on e.def1=f.pk_defdoc and f.dr=0
left join resa_costcenter g on nvl(a.cbzx,b.cbzx2) = g.pk_costcenter
) t1 
left join bd_material t2 on t1.wgwl1=t2.pk_material
left join bd_defdoc t3 on t2.def1=t3.pk_defdoc and t3.dr=0
left join bd_material t4 on t1.ckwl=t4.pk_material

),
--------------------------------------------------------------------------------
tab2 as(
select aa.*,
ad.code wlbm,ad.name wlmc,ad.materialspec wlgg,ad.materialtype wlxh,ac.nitemnum/ac.ibasenum*aa.wgsl dezs 
from (
select distinct
t1.WGWL1,
t2.code 产品编码,t2.name 产品名称,t2.materialspec 产品规格,t2.materialtype 产品型号,t3.name as bb,

t1.公司,
t1.成本中心,
case  
    when t1.成本中心 like '委外%' then '委外'
    when t1.成本中心 like '%板%' then '板材'
    else '注塑' end as 成本域,
t1.WGSL
--t1.CKSL,t1.DESL,t1.DEZS,t1.SJDWYL,t1.CYL 
--t1.*
from (
select  '0001O1100000000027VN' pk_org,'新乡市仲达塑胶电子有限公司' 公司,g.ccname as 成本中心,nvl(a.cbzx,b.cbzx2) cbzx1,nvl(a.wgwl,b.wgwl2) wgwl1,a.*,b.* ,e.code wlbm,e.name wlmc,e.materialspec wlgg,e.materialtype wlxh,d.nitemnum/d.ibasenum desl  ,
d.nitemnum/d.ibasenum*a.wgsl dezs,
case when nvl(a.wgsl,0)=0 then 0 else nvl(b.cksl,0)/a.wgsl end sjdwyl,
case when nvl(b.cksl,0)=0 and decode(d.ibasenum,null,0,d.nitemnum/d.ibasenum*a.wgsl) =0 then 9999
else (nvl(b.cksl,0)-decode(d.ibasenum,null,0,d.nitemnum/d.ibasenum*a.wgsl)) end cyl



from  (
select distinct b.ccostcenterid cbzx,b.cinventoryvid wgwl,sum(b.nnum)over(partition by b.ccostcenterid,b.cinventoryvid ) wgsl from ia_i3bill a
inner join ia_i3bill_b b on a.cbillid=b.cbillid and b.dr=0
inner join resa_costcenter c on b.ccostcenterid=c.pk_costcenter and c.dr=0
where 1=1 
--and substr(b.dbilldate,1,10)>=parameter('date1') and  substr(b.dbilldate,1,10)<=parameter('date2')
and substr(b.dbilldate,1,10)>='2025-10-01' and  substr(b.dbilldate,1,10)<='2025-10-31'
and a.cstockorgid='0001O1100000000027VN'
and a.ctrantypeid<>'1001O11000000000U5JK'
) a
full join 
 (
select distinct b.ccostcenterid cbzx2,b.cinventoryvid ckwl,sum(b.nnum)over(partition by b.ccostcenterid,b.cinventoryvid ,b.ccostobjid) cksl,b.ccostobjid wgwl2 from ia_i6bill a 
inner join ia_i6bill_b b on a.cbillid=b.cbillid and b.dr=0
inner join resa_costcenter c on b.ccostcenterid=c.pk_costcenter and c.dr=0
inner join bd_material e on b.cinventoryvid=e.pk_material
where b.ccostobjid<>'~'
--and substr(b.dbilldate,1,10)>=parameter('date1') and  substr(b.dbilldate,1,10)<=parameter('date2')
and substr(b.dbilldate,1,10)>='2025-10-01' and  substr(b.dbilldate,1,10)<='2025-10-31'
and a.cstockorgid='0001O1100000000027VN'
) b on a.cbzx=b.cbzx2 and a.wgwl=b.wgwl2

left join bd_bom c on nvl(a.wgwl,b.wgwl2)=c.hcmaterialvid  and c.dr=0 and c.hbdefault ='Y'
left join bd_bom_b d on c.cbomid=d.cbomid and b.ckwl=d.cmaterialvid  and d.dr=0
left join bd_material e on d.cmaterialvid=e.pk_material
left join bd_defdoc f on e.def1=f.pk_defdoc and f.dr=0
left join resa_costcenter g on nvl(a.cbzx,b.cbzx2) = g.pk_costcenter
) t1 
left join bd_material t2 on t1.wgwl1=t2.pk_material
left join bd_defdoc t3 on t2.def1=t3.pk_defdoc and t3.dr=0
left join bd_material t4 on t1.ckwl=t4.pk_material

where 1=1 and t1.WGSL>0
) aa 
left join bd_bom ab on aa.wgwl1=ab.hcmaterialvid  and ab.dr=0 and ab.hbdefault ='Y'
left join bd_bom_b ac on ab.cbomid=ac.cbomid   and ac.dr=0
left join bd_material ad on ac.cmaterialvid=ad.pk_material
left join bd_defdoc ae on ad.def1=ae.pk_defdoc and ae.dr=0
where 1=1
--排除未按照定额领用的异常情况
and ad.name !='兰色周转箱'
and ad.name !='折叠专用箱'
and ad.name !='塑料袋'
and ad.name !='纸箱'
and ad.name !='胶带'

)

-- 实际用料
select * from(
select distinct
NVL(tab1.产品编码,tab2.产品编码) 产品编码, 
NVL(tab1.产品名称,tab2.产品名称) 产品名称, 
NVL(tab1.产品规格,tab2.产品规格) 产品规格, 
NVL(tab1.产品型号,tab2.产品型号) 产品型号, 
NVL(tab1.BB,tab2.BB) BB, 
NVL(tab1.材料编码,tab2.WLBM) 材料编码, 
NVL(tab1.材料名称,tab2.WLMC) 材料名称, 
NVL(tab1.材料规格,tab2.WLGG) 材料规格, 
NVL(tab1.材料型号,tab2.WLXH) 材料型号, 

NVL(tab1.公司,tab2.公司) 公司, 
NVL(tab1.成本中心,tab2.成本中心) 成本中心, 
NVL(tab1.成本域,tab2.成本域) 成本域, 
NVL(tab1.成本域,tab2.成本域) || NVL(tab1.成本中心,tab2.成本中心) || NVL(tab1.产品编码,tab2.产品编码)  联合键,
NVL(tab1.WGSL,tab2.WGSL) WGSL, 
tab1.cksl,
tab2.dezs
from tab1 full join tab2 on tab1.产品编码=tab2.产品编码 and tab1.材料编码=tab2.WLBM and tab1.成本中心=tab2.成本中心
)
where 1=1 
--${if(len(category) == 0,"","and 成本域 = '" + category + "'")} 
--${if(len(costcenter) == 0,"","and 成本中心 = '" + costcenter + "'")} 
--${if(len(cpcode) == 0,"","and 产品编码 = '" + cpcode + "'")} 

ORDER BY 联合键

BOM表

SELECT
    t_1.wl WL,
    t_1.cbom_bid CBOM_BID,
    t_1.dc DC,
    t_1.cbom_wipid CBOM_WIPID,
    t_1.crtid CRTID,
    t_1.px PX 
FROM
    (
    SELECT
        nvl( a.hcmaterialvid, e.pk_material ) wl,
        a.cbomid cbomid,
        b.cbom_bid cbom_bid,
        c.cbom_wipid cbom_wipid,
        d.crtid crtid,
        decode( b.fbackflushtime, '1', '产品完工', '2', '工序完工' ) dc,
        a.pk_group pk_group,
        decode( a.hcmaterialvid, NULL, '2', '1' ) px,
        a.hfversiontype hfversiontype 
    FROM
        bd_material e LEFT outer
        JOIN bd_bom a ON a.hcmaterialvid = e.pk_material LEFT outer
        JOIN bd_bom_b b ON a.cbomid = b.cbomid 
        AND b.dr = 0 LEFT outer
        JOIN bd_bom_wip c ON b.cbom_bid = c.cbom_bid 
        AND c.dr = 0 LEFT outer
        JOIN pd_rt d ON a.cbomid = d.mbomverno 
        AND d.dr = 0
        INNER JOIN bd_marbasclass f ON e.pk_marbasclass = f.pk_marbasclass 
    WHERE
        ( substr( f.code, 1, 4 ) = '0102' OR substr( f.code, 1, 4 ) = '1102' ) 
        AND a.hfversiontype = 1 
    ORDER BY
        decode( a.hcmaterialvid, NULL, '2', '1' ) 
    ) t_1 LEFT outer
    JOIN bd_material meta ON t_1.wl = meta.pk_material 
WHERE
    t_1.pk_group = '0001O110000000000IIT' 
ORDER BY
    meta.code,
    meta.name,
    meta.materialspec,
    meta.materialtype

投入产出分析有不完美的地方实际查询有1个物料没有调出BOM信息，查不出具体原因，所以从日志中调出查询了BOM表的写法，后续如果业务觉得不完美，就再更新新的。