ERP升级物料整理SQL语句

select *  from View_Kcloud_material  --创建视图

SELECT a.FNumber AS '物料代码', a.FName AS '物料名称', a.FModel AS '规格型号', a.FShortNumber AS '短代码', 
a.FFullName AS '全名称', a.FOrderPrice AS '采购单价', b.FName AS '物料属性', c.FName AS '单位名称', 
c.FNumber AS '单位编码', d.FName AS '仓库名称', d.FNumber AS '仓库编码', a.FQtyDecimal AS '数量精度', 
a.FLowLimit AS '最低存量', a.FHighLimit AS '最高存量', a.FSecInv AS '安全库存', 
CASE a.FIsEquipment WHEN 0 THEN '否' ELSE '是' END AS '是否设备', 
CASE a.FBatchManager WHEN 0 THEN '否' ELSE '是' END AS '批号管理', 
CASE a.FTrack WHEN 76 THEN '加权平均' WHEN 77 THEN '移动平均' WHEN 78 THEN '先进先出' WHEN 79 THEN '后进先出'
WHEN 80 THEN '分批认定' WHEN 81 THEN '计划价' END AS '计价方法', e.FNumber AS '存货科目代码', 
e.FName AS '存货科目名称', f.FNumber AS '销售科目代码', f.FName AS '销售科目名称', g.FNumber AS '成本科目代码', 
g.FName AS '成本科目名称', CASE a.FIsSnManage WHEN 0 THEN '否' ELSE '是' END AS '是否启用序列号', 
a.FTaxRate AS '税率', 
CASE a.FPlanTrategy WHEN 321 THEN 'MRP' WHEN 322 THEN 'MPS' WHEN 323 THEN 'FAS' WHEN 324 THEN 'None' END AS '计划策略',
CASE a.FOrderTrategy WHEN 331 THEN '批对批(LFL)' WHEN 332 THEN '期间订货量(POQ)' WHEN 333 THEN '固定批量(FOQ)'
WHEN 334 THEN '再订货点(ROP)' END AS '订货策略', a.FLeadTime AS '提前期', a.FQtyMin AS '最小订货量', 
a.FQtyMax AS '最大订货量', a.FDefaultRoutingID AS '默认工艺路线', i.FName AS '计划员', i.FNumber AS '计划员代码', 
j.FName AS '计划模式', 
CASE a.FInspectionLevel WHEN 351 THEN '全检' WHEN 352 THEN '免检' WHEN 353 THEN '抽检' END AS '检验方式', 
a.FInspectionProject AS '检验方案', 
CASE a.FProChkMde WHEN 351 THEN '全检' WHEN 352 THEN '免检' WHEN 353 THEN '抽检' END AS '产品检验方式', 
CASE a.FWWChkMde WHEN 351 THEN '全检' WHEN 352 THEN '免检' WHEN 353 THEN '抽检' END AS '委外加工检验方式', 
CASE a.FSOChkMde WHEN 351 THEN '全检' WHEN 352 THEN '免检' WHEN 353 THEN '抽检' END AS Expr1, 
CASE a.FWthDrwChkMde WHEN 351 THEN '全检' WHEN 352 THEN '免检' WHEN 353 THEN '抽检' END AS '退货检验方式 ', 
CASE a.FStkChkMde WHEN 351 THEN '全检' WHEN 352 THEN '免检' WHEN 353 THEN '抽检' END AS '库存检验方式', 
CASE a.FOtherChkMde WHEN 351 THEN '全检' WHEN 352 THEN '免检' WHEN 353 THEN '抽检' END AS '其他检验方式', 
a.FStkChkPrd AS '库存检验', a.FLength AS '长', a.FWidth AS '宽', a.FHeight AS '高', a.FSize AS '体积', k.FName, k.FNumber, 
a.F_163 AS 'ERP取数时间', a.F_162 AS '备用', a.F_161 AS '自定义防爆等级', 
CASE a.F_160 WHEN 351 THEN '全检' WHEN 352 THEN '免检' WHEN 353 THEN '抽检' END AS 'WMS检验方式', 
a.F_158 AS '减速比/速度', a.F_157 AS '最大力矩/推力', a.F_156 AS '输入信号', a.F_155 AS '液压油', a.F_154 AS '压力等级', 
a.F_153 AS 'SRM检验员', a.F_152 AS '防爆证号', a.F_151 AS '防护等级', a.F_150 AS '环境温度', a.F_149 AS '电源电压', 
a.F_148 AS '行程/速度', a.F_147 AS 'PLM系统编码', a.F_146 AS '装箱物料', 
CASE a.F_145 WHEN 40183 THEN '否' ELSE '是' END AS '是否看板物料', 
CASE a.F_144 WHEN 40186 THEN '否' ELSE '是' END AS '是否推送WMS', a.F_143 AS '物料是否修改', 
a.F_142 AS '包装类别', a.F_141 AS '外形尺寸', a.F_140 AS '防爆等级', a.F_139 AS '连接', a.F_138 AS '重量', 
a.F_137 AS '触点容量', a.F_136 AS '润滑油', a.F_135 AS '接线图号', a.F_134 AS '额定电流', a.F_133 AS '力矩/推力', 
a.F_132 AS '电机功率', a.F_131 AS '执行标准', a.F_130 AS '产品大类', a.F_129 AS '最小包装量', l.FName AS '物料大类', 
l.FNumber AS '物料大类代码', a.F_127 AS '是否军工产品', a.F_126 AS '（军工产品）结束日期', 
a.F_125 AS '（军工产品）生效日期', a.F_124 AS '是否重庆名牌', a.F_123 AS '（重庆名牌）结束日期', 
a.F_122 AS '（重庆名牌）生效日期', a.F_121 AS '是否中国名牌', a.F_120 AS '（中国名牌）结束日期', 
a.F_119 AS '（中国名牌）生效日期', a.F_118 AS '是否高新产品', a.F_117 AS '（高新产品）结束日期', 
a.F_116 AS '（高新产品）生效日期', a.F_115 AS '是否新产品', a.F_113 AS '结束日期', a.F_112 AS '生效日期', 
a.F_110 AS '阀规格型号', a.F_109 AS '二级齿轮型号', a.F_108 AS '执行器规格型号', a.F_107 AS '物料归类测试', 
a.F_106 AS '物料归类', a.F_105 AS '考核标准成本', a.F_104 AS '旧物料代码', a.F_103 AS '客户名称', a.F_102 AS '表号', 
a.F_101 AS '合同号'
FROM dbo.t_ICItem AS a LEFT OUTER JOIN
dbo.t_Item AS c ON a.FUnitID = c.FItemID AND c.FItemClassID = 7 AND c.FExternID = - 1 LEFT OUTER JOIN
dbo.t_SubMessage AS b ON b.FTypeID = 210 AND a.FErpClsID = b.FInterID LEFT OUTER JOIN
dbo.t_Stock AS d ON a.FDefaultLoc = d.FItemID LEFT OUTER JOIN
dbo.t_Account AS e ON a.FAcctID = e.FAccountID LEFT OUTER JOIN
dbo.t_Account AS f ON a.FSaleAcctID = f.FAccountID LEFT OUTER JOIN
dbo.t_Account AS g ON a.FCostAcctID = g.FAccountID LEFT OUTER JOIN
dbo.t_Account AS h ON a.FAPAcctID = h.FAccountID LEFT OUTER JOIN
dbo.t_Emp AS i ON a.FPlanner = i.FItemID LEFT OUTER JOIN
dbo.t_SubMessage AS j ON a.FPlanMode = j.FInterID LEFT OUTER JOIN
dbo.t_WorkType AS k ON k.FInterID = a.FDefaultWorkTypeID LEFT OUTER JOIN
dbo.t_Item AS l ON l.FItemID = a.F_128
WHERE (a.FDeleted = 0)

 

--科目表
select *  from t_account

select *  from t_icitem where FCostProject<>0


--物料使用状态
select *  from t_submessage where FTypeID=170
--物料属性：委外 自制
select *  from t_submessage where FTypeID=210


--检验方式
select *  from t_submessage where FTypeID=171



--计划模式
select *  from t_submessage where FTypeID=606


--是否WMS
select *  from t_submessage where FTypeID=10013


--是否看板
select *  from t_submessage where FTypeID=10012




--仓库
select *  from t_stock  

--计划策略
select *  from t_submessage where FTypeID=167

--订货策略
select *  from t_submessage where FTypeID=169

--生产类型
select *  from t_WorkType


--基本单位
select *  from  t_item where FItemClassID=7 and     FExternID=-1
select *  from t_item where fname='件'  


--基础表信息
select *  from t_tabledescription  where FTableName='t_icitem'
select *  from t_fielddescription  where FTableid=17                                                                



--自定义信息表
select *  from t_tabledescription  where FTableName like 't_icitem%'
select *  from t_fielddescription  where FTableid=60