物料编码自动生成方案

实现效果

• 根据用户选择物料编码规则表明细物料分类
• 自动生成按现有编码规则+1创建新编码
• 自动判断若该系列编码规则无生成记录的，默认从0001开始创建新编码规则

应用代码

• 物料主数据-物料编码规则字段应用格搜代码：

--格搜：物料主数据-选择物料编码规则表
SELECT T0.[code] AS '完整编码',T0.[Name] AS '完整描述',
 T0.[U_L1NAME], T0.[U_L2NAME], T0.[U_L3NAME], T0.[U_L1CODE]
	, T0.[U_L2CODE], T0.[U_L3CODE]
FROM [dbo].[@ITEM] T0
ORDER BY T0.[U_L1CODE], T0.[U_L2CODE], T0.[U_L3CODE]

• 物料主数据-物料编码描述字段应用格搜代码：

--格搜：物料主数据-根据物料编码规则自动显示物料编码描述
SELECT T0.[NAME]
FROM [dbo].[@ITEM] T0
WHERE T0.[code]=$[OITM.U_ITEM]

• 物料主数据-物料编号字段应用格搜代码：

--格搜：物料主数据-自动选择规则生成物料编码
DECLARE @N CHARACTER(20)
SELECT @N=MAX(T0.[ItemCode])  FROM OITM T0 WHERE T0.[U_ITEM] =$[OITM.U_ITEM]

IF @N IS NULL
SELECT $[OITM.U_ITEM] +'0001'

IF @N IS NOT NULL
SELECT CAST(CAST(MAX(T0.[ItemCode]) AS bigint) + 1 AS nvarchar)  
FROM OITM T0 WHERE T0.[U_ITEM] =$[OITM.U_ITEM]

• 物料主数据-物料编码相似物料字段应用格搜代码：

--格搜：物料主数据-根据物料编码规则显示同类物料编码
SELECT T0.[ItemCode], T0.[ItemName], T0.[FrgnName], T1.[ItmsGrpNam], 
TA.L AS '同类已编码数量'
FROM OITM T0
	INNER JOIN OITB T1 ON T0.[ItmsGrpCod] = T1.[ItmsGrpCod]
	LEFT JOIN (
		SELECT T0.[U_ITEM], COUNT(T0.[U_ITEM]) AS 'L'
		FROM OITM T0
		GROUP BY T0.[U_ITEM]
	) TA
	ON TA.[U_ITEM] = T0.[U_ITEM]
WHERE T0.[U_ITEM]=$[OITM.U_ITEM]
ORDER BY T0.[ItemCode] DESC