U8 用友 ERP 库存查询SQL

支持 主计量单位和辅助计量单位

select   a.cinvcode,f.cinvstd,f.cinvname,b.cwhname,convert(float ,a.iquantity)  ,
g.cComUnitName as MainUnit,g.iChangRate as mainrate,h.cComUnitName as asUnit,h.iChangRate as asRate
from currentstock as a inner join warehouse as b on a.cwhcode=b.cwhcode inner join v_inventory as f on a.cinvcode=f.cinvcode  inner join inventory_extradefine as fe on f.cinvcode=fe.cinvcode
left join (select * from  ComputationUnit where bMainUnit =1) as g on g.cComUnitCode=f.cComUnitCode and g.cGroupCode =f.cGroupCode
left join (select * from  ComputationUnit where bMainUnit =0) as h on h.cComUnitCode=f.cSTComUnitCode and h.cGroupCode =f.cGroupCode
where iQuantity>0 and a.cinvcode like '%'+'500291'+'%'

cinvcode    cinvname    cwhname    storagepos    kc    MainUnit    kcas    asUnit    cinvstd    asRate    mainrate
500291    PE膜    GE成品库    1号库    1003.5842    平米    17985.38    公斤    幅宽1500，厚度0.06-单面6丝单刨    17.921147    1.000000
500291    PE膜    辅料库    1号库    3584.40145    平米    64236.59    公斤    幅宽1500，厚度0.06-单面6丝单刨    17.921147    1.000000