NC6.5投入產(chǎn)出分析表

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
字段名 指代
CBZX 成本中心
WGWL 完工物料
WGSL 完工數(shù)量
CKWL 出庫物料
CKSL 出庫數(shù)量
WLBM 物料編碼
WLMC 物料名稱
WLGG 物料規(guī)格
WLXH 物料型號(hào)
DESL 定額數(shù)量
DEZS 定額總數(shù)
SJDWYS 實(shí)際單位用數(shù)
CYL 差異量

適配優(yōu)化

select 
t2.code 產(chǎn)品編碼,t2.name 產(chǎn)品名稱,t2.materialspec 產(chǎn)品規(guī)格,t2.materialtype 產(chǎn)品型號(hào),t3.name as bb,
t4.code 材料編碼,t4.name 材料名稱,t4.materialspec 材料規(guī)格,t4.materialtype 材料型號(hào),
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,'新鄉(xiāng)市仲達(dá)塑膠電子有限公司' 公司,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形成全外查詢 涵蓋全部物料對(duì)比

with tab1 as(
select 
t2.code 產(chǎn)品編碼,t2.name 產(chǎn)品名稱,t2.materialspec 產(chǎn)品規(guī)格,t2.materialtype 產(chǎn)品型號(hào),t3.name as bb,
t4.code 材料編碼,t4.name 材料名稱,t4.materialspec 材料規(guī)格,t4.materialtype 材料型號(hào),

t1.公司,
t1.成本中心,
case  
    when t1.成本中心 like '委外%' then '委外'
    when t1.成本中心 like '%板%' then '板材'
    else '注塑' end as 成本域,
t1.WGSL,t1.CKSL 
--t1.*
from (
select  '0001O1100000000027VN' pk_org,'新鄉(xiāng)市仲達(dá)塑膠電子有限公司' 公司,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 產(chǎn)品編碼,t2.name 產(chǎn)品名稱,t2.materialspec 產(chǎn)品規(guī)格,t2.materialtype 產(chǎn)品型號(hào),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,'新鄉(xiāng)市仲達(dá)塑膠電子有限公司' 公司,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
--排除未按照定額領(lǐng)用的異常情況
and ad.name !='蘭色周轉(zhuǎn)箱'
and ad.name !='折疊專用箱'
and ad.name !='塑料袋'
and ad.name !='紙箱'
and ad.name !='膠帶'

)

-- 實(shí)際用料
select * from(
select distinct
NVL(tab1.產(chǎn)品編碼,tab2.產(chǎn)品編碼) 產(chǎn)品編碼, 
NVL(tab1.產(chǎn)品名稱,tab2.產(chǎn)品名稱) 產(chǎn)品名稱, 
NVL(tab1.產(chǎn)品規(guī)格,tab2.產(chǎn)品規(guī)格) 產(chǎn)品規(guī)格, 
NVL(tab1.產(chǎn)品型號(hào),tab2.產(chǎn)品型號(hào)) 產(chǎn)品型號(hào), 
NVL(tab1.BB,tab2.BB) BB, 
NVL(tab1.材料編碼,tab2.WLBM) 材料編碼, 
NVL(tab1.材料名稱,tab2.WLMC) 材料名稱, 
NVL(tab1.材料規(guī)格,tab2.WLGG) 材料規(guī)格, 
NVL(tab1.材料型號(hào),tab2.WLXH) 材料型號(hào), 

NVL(tab1.公司,tab2.公司) 公司, 
NVL(tab1.成本中心,tab2.成本中心) 成本中心, 
NVL(tab1.成本域,tab2.成本域) 成本域, 
NVL(tab1.成本域,tab2.成本域) || NVL(tab1.成本中心,tab2.成本中心) || NVL(tab1.產(chǎn)品編碼,tab2.產(chǎn)品編碼)  聯(lián)合鍵,
NVL(tab1.WGSL,tab2.WGSL) WGSL, 
tab1.cksl,
tab2.dezs
from tab1 full join tab2 on tab1.產(chǎn)品編碼=tab2.產(chǎn)品編碼 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 產(chǎn)品編碼 = '" + cpcode + "'")} 

ORDER BY 聯(lián)合鍵

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', '產(chǎn)品完工', '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

投入產(chǎn)出分析有不完美的地方實(shí)際查詢有1個(gè)物料沒有調(diào)出BOM信息，查不出具體原因，所以從日志中調(diào)出查詢了BOM表的寫法，后續(xù)如果業(yè)務(wù)覺得不完美，就再更新新的。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容