md_YOS/src/custom/restcontroller/webmanage/sale/item/SQL/货品档案详情.sql

SELECT t1.*,
       t2.unitname,
       t8.plm_itemextendid,
       t8.caliber,
       t8.pressure,
       t8.material,
       t8.butterflyplatedrive,
       t8.connection,
       t8.valveplatematerial,
       t8.bodymaterial,
       t8.actuatortype,
       t8.actuatorbrand,
       t8.isbutterfly,
       t8.erpitemno,
       t8.erpitemname,
       t8.specalnote,
       t8.prodline,
       t8.device,
       (case
            when t3.cansaleqty >= t1.stockstatus1 then '充足'
            when t3.cansaleqty <= t1.stockstatus2 then '缺货'
            else '紧缺' end) stockstatus,
       ifnull(t3.qty, 0)   invbal_qty,
       t4.schemename widthschemename,
       t5.schemename lengthschemename,
       t6.schemename,
       ifnull(t9.price,0) price1,
       ifnull(t10.price,0) price2,
       ifnull(t11.price,0) price3,
       ifnull(t12.price,0) price4,
       ifnull(t13.price,0) price5,
       t14.depname,
       t15.stockname,
       t15.kw,
       t16.suppno,
       t16.suppshortname,
       t16.suppname
FROM plm_item t1
         LEFT JOIN plm_unit t2 ON t2.unitid = t1.unitid AND t2.siteid = t1.siteid
         LEFT JOIN plm_itemextend t8 ON t8.itemid = t1.itemid AND t8.siteid = t1.siteid
         LEFT JOIN st_invbal_sale t3 ON t3.siteid = t1.siteid AND t3.itemid = t1.itemid
         left join sa_sizecustomizedscheme t4 on t1.widthschemeid=t4.sa_sizecustomizedschemeid and t1.siteid=t4.siteid
         left join sa_sizecustomizedscheme t5 on t1.lengthschemeid=t5.sa_sizecustomizedschemeid and t1.siteid=t5.siteid
         left join sa_customscheme t6 on t1.sa_customschemeid=t6.sa_customschemeid and t1.siteid=t6.siteid
         left join sa_itemprice t9 on t9.itemid = t1.itemid AND t9.siteid = t1.siteid and t9.pricegrade=1
         left join sa_itemprice t10 on t10.itemid = t1.itemid AND t10.siteid = t1.siteid and t10.pricegrade=2
         left join sa_itemprice t11 on t11.itemid = t1.itemid AND t11.siteid = t1.siteid and t11.pricegrade=3
         left join sa_itemprice t12 on t12.itemid = t1.itemid AND t12.siteid = t1.siteid and t12.pricegrade=4
         left join sa_itemprice t13 on t12.itemid = t1.itemid AND t12.siteid = t1.siteid and t12.pricegrade=5
         LEFT JOIN sys_department t14 ON t14.departmentid = t1.departmentid AND t14.siteid = t1.siteid
         LEFT JOIN st_stock t15 ON t15.stockno = t1.stockno AND t15.siteid = t1.siteid
         LEFT JOIN sa_supplier t16 ON t16.sa_supplierid = t1.sa_supplierid AND t16.siteid = t1.siteid
WHERE t1.siteid = $siteid$
  and t1.itemid = $itemid$