Create PROCEDURE [dbo].[sp_AddMaterialCode]
@materialCode varchar(50),
@materialName varchar(100),
@materialModel VARCHAR(100),
@materialType VARCHAR(100),
@basicUnit VARCHAR(100),
@purchaseUnit VARCHAR(100),
@FCoefficient int,-- 换算比例
@materailCategoryCode varchar(50),
@materailCategoryName varchar(500)
AS
BEGIN
--声明参数
--DECLARE @materialCode VARCHAR(100) = '42.03.01.00000326';
--DECLARE @materialName VARCHAR(100) = '测试3';
--DECLARE @materialModel VARCHAR(100) = '测试3';
----仓位
--DECLARE @position VARCHAR(100) = 'L01';
----基本单位
--DECLARE @basicUnit VARCHAR(100) = 'PCS';
----采购单位
--DECLARE @purchaseUnit VARCHAR(100) = 'PCS';
DECLARE @materialNamePinYin VARCHAR(100) = '';
DECLARE @materialNamePY VARCHAR(50) = '';
DECLARE @ParentId int;
DECLARE @ParentFullName VARCHAR(100);
DECLARE @ParentFLevel int;
DECLARE @FItemID int;
DECLARE @FSPID int;
--基本单位
DECLARE @FUnitID int;
--采购单位
DECLARE @FOrderUnitID int;
DECLARE @Position VARCHAR(100);
set @Position='L01'
if @materialType='设计件'
begin
set @Position='AA01'
end
DECLARE @UserId int;
select @UserId=FUserID from t_Base_User where FName='Administrator'
-- @materialCode 查询物料是否存在
DECLARE @dataMaterialCode int=0;
select top 1 @dataMaterialCode =FItemID from t_Item
where FItemClassID = 4 and FNumber =@materialCode
if @dataMaterialCode!=0
begin
return @dataMaterialCode;
end
-- 开始事务
BEGIN TRY
BEGIN TRANSACTION;
--插入上级物料
-- 定义分隔符
DECLARE @Separator VARCHAR(10)='.';
DECLARE @MaxPosition INT;
SELECT @MaxPosition = LEN(@materailCategoryName) - LEN(REPLACE(@materailCategoryName, @Separator, '')) + 1;
DECLARE @StartPos INT = 1; -- 从第一段开始
DECLARE @StartPos2 INT = 1; -- 从第一段开始
DECLARE @OutputString NVARCHAR(100);
DECLARE @OutputString2 NVARCHAR(100);
DECLARE @ResultCode NVARCHAR(100)='';
DECLARE @CurrentPos INT = 1;
DECLARE @EndPos INT = 1;
DECLARE @EndPos2 INT = 1;
WHILE @CurrentPos <= @MaxPosition
BEGIN
-- 代码
SET @EndPos2 = CHARINDEX(@Separator, @materailCategoryCode, @StartPos2);
IF @EndPos2 = 0
BEGIN
SET @EndPos2 = LEN(@materailCategoryCode) + 1;
END
SET @OutputString2 = SUBSTRING(@materailCategoryCode, @StartPos2, @EndPos2-@StartPos2);
SET @ResultCode = @ResultCode +'.'+ @OutputString2;
IF LEFT(@ResultCode, 1) ='.'
BEGIN
SET @ResultCode =STUFF(@ResultCode, 1, 1, '');
END
--PRINT @ResultCode;
-- 名称
SET @EndPos = CHARINDEX(@Separator, @materailCategoryName, @StartPos);
IF @EndPos = 0
BEGIN
SET @EndPos = LEN(@materailCategoryName) + 1;
END
SET @OutputString = SUBSTRING(@materailCategoryName, @StartPos, @EndPos-@StartPos);
--PRINT @OutputString;
SET @StartPos = @EndPos + 1;
SET @StartPos2 = @EndPos2 + 1;
SET @CurrentPos = @CurrentPos + 1;
DECLARE @AddItemResult varchar(100)='' ;
exec sp_In3AddItem @ResultCode,@OutputString,'','L01','PCS','PCS',0,@AddItemResult output;
END
declare @FItemClassIDMaterial int;
declare @FItemClassIDWarehouse int;
declare @FItemClassIDUnit int;
select @FItemClassIDMaterial=FItemClassID from t_ItemClass where FName='物料'
select @FItemClassIDWarehouse=FItemClassID from t_ItemClass where FName='仓库'
select @FItemClassIDUnit=FItemClassID from t_ItemClass where FName='计量单位'
-- 获取数量组ID
DECLARE @FUnitGroupID int ;
select @FUnitGroupID=FUnitGroupID from t_UnitGroup where FName='数量组'
-- 查询parent Info
select @ParentId=FItemID,@ParentFLevel=FLevel,@ParentFullName=FFullName from t_Item
where FItemClassID = @FItemClassIDMaterial and FNumber =(SELECT LEFT(@materialCode, LEN(@materialCode) - CHARINDEX('.', REVERSE(@materialCode))))
-- 插入 t_Item 表数据
INSERT INTO t_Item (FItemClassID,FParentID,FLevel,FName,FNumber,FShortNumber,FFullNumber,FDetail,UUID,FDeleted,FFullName,FGrControl)
VALUES (@FItemClassIDMaterial,@ParentId,@ParentFLevel+1,@materialName,@materialCode,(SELECT RIGHT(@materialCode, CHARINDEX('.',REVERSE(@materialCode)) - 1)),@materialCode,1,(Select newid()),0,@ParentFullName+'_'+@materialName,-1)
select @FItemID=FItemID from t_Item where FItemClassID=@FItemClassIDMaterial and FFullNumber=@materialCode
-- 查询仓库信息 仓库默认原材料仓库 1097
-- 查询仓位信息 默认为 IN仓位
-- Update t1 SET t1.FSPID=t2.FSPID
--FROM ##Imp_t_Item t1 inner join t_StockPlace t2
--ON t1.FSPID_FName= t2.FName inner join t_StockPlaceGroup t3 On t1.FSPID_FGroupName = t3.FName And t2.FSPGroupID = t3.FSPGroupID
select @FSPID=FSPID from t_StockPlace t1
inner join t_StockPlaceGroup t2 On 'IN仓位'= t2.FName And t1.FSPGroupID = t2.FSPGroupID
where t1.FName=@position
-- 基本单位 默认为数量组
-- Update t1 SET t1.FUnitID=t2.FMeasureUnitID
--FROM ##Imp_t_Item t1 inner join t_MeasureUnit t2
--ON t1.FUnitID_FName= t2.FName inner join t_UnitGroup t3 On t1.FUnitID_FGroupName = t3.FName And t2.FUnitGroupID = t3.FUnitGroupID
--首先判断单位存不存在,不存在则新建
set @FUnitID=0;
select @FUnitID=FMeasureUnitID from t_MeasureUnit t1
inner join t_UnitGroup t2 On t1.FUnitGroupID = t2.FUnitGroupID
where t1.FName=@basicUnit
if @FUnitID=0
begin
-- 新建单位
Insert Into t_Item(FItemClassID,FDetail,FLevel,FParentID,FNumber,FName,FShortNumber,FFullNumber,FDeleted)
Values (@FItemClassIDUnit,1,2,@FUnitGroupID,@basicUnit,@basicUnit,@basicUnit,@basicUnit,0)
DECLARE @FUnitFItemID int ;
select @FUnitFItemID=FItemID from t_Item where FItemClassID=@FItemClassIDUnit and FNumber=@basicUnit
INSERT INTO t_MeasureUnit (FMeasureUnitID,FName,FNumber,FAuxClass,FCoefficient,FConversation,FNameEn,FNameEnPlu,FUnitGroupID,FPrecision,FShortNumber,FParentID)
VALUES (@FUnitFItemID,@basicUnit,@basicUnit,' ',1,1,NULL,NULL,@FUnitGroupID,0,@basicUnit,@FUnitGroupID)
set @FUnitID=@FUnitFItemID;
Insert Into t_BaseProperty(FTypeID, FItemID, FCreateDate, FCreateUser, FLastModDate, FLastModUser, FDeleteDate, FDeleteUser)
Values(@FItemClassIDUnit, @FUnitFItemID,getdate(), 'administrator', Null, Null, Null, Null)
end
--采购单位 默认为数量组
-- Update t1 SET t1.FOrderUnitID=t2.FMeasureUnitID
--FROM ##Imp_t_Item t1 inner join t_MeasureUnit t2
--ON t1.FOrderUnitID_FName= t2.FName inner join t_UnitGroup t3 On t1.FOrderUnitID_FGroupName = t3.FName And t2.FUnitGroupID = t3.FUnitGroupID
if @FCoefficient<>1
begin
set @purchaseUnit=@purchaseUnit+''+@FCoefficient;
end
else if @purchaseUnit='包'
begin
set @purchaseUnit=@purchaseUnit+''+@FCoefficient;
end
set @FOrderUnitID=0;
select @FOrderUnitID=FMeasureUnitID from t_MeasureUnit t1
inner join t_UnitGroup t2 On t1.FUnitGroupID = t2.FUnitGroupID
where t1.FName=@purchaseUnit
if @FOrderUnitID=0
begin
-- 新建单位
Insert Into t_Item(FItemClassID,FDetail,FLevel,FParentID,FNumber,FName,FShortNumber,FFullNumber,FDeleted)
Values (@FItemClassIDUnit,1,2,@FUnitGroupID,@basicUnit,@basicUnit,@basicUnit,@basicUnit,0)
DECLARE @FOrderUnitFItemID int ;
select @FOrderUnitFItemID=FItemID from t_Item where FItemClassID=@FItemClassIDUnit and FNumber=@basicUnit
INSERT INTO t_MeasureUnit (FMeasureUnitID,FName,FNumber,FAuxClass,FCoefficient,FConversation,FNameEn,FNameEnPlu,FUnitGroupID,FPrecision,FShortNumber,FParentID)
VALUES (@FOrderUnitFItemID,@basicUnit,@basicUnit,' ',@FCoefficient,1,NULL,NULL,@FUnitGroupID,0,@basicUnit,@FUnitGroupID)
set @FOrderUnitID=@FOrderUnitFItemID;
Insert Into t_BaseProperty(FTypeID, FItemID, FCreateDate, FCreateUser, FLastModDate, FLastModUser, FDeleteDate, FDeleteUser)
Values(@FItemClassIDUnit, @FOrderUnitFItemID,getdate(), 'administrator', Null, Null, Null, Null)
end
-- 获取销售单位 产品单位 与基本单位一直
DECLARE @FSaleUnitID int ;
DECLARE @FProductUnitID int ;
select @FSaleUnitID=FMeasureUnitID,@FProductUnitID=FMeasureUnitID from t_MeasureUnit t1
inner join t_UnitGroup t2 On t1.FUnitGroupID = t2.FUnitGroupID
where t1.FName=@basicUnit
-- 获取仓库ID SZ001 原材料仓库
DECLARE @FDefaultLoc int;
select @FDefaultLoc=FItemID from t_Item where FItemClassID = @FItemClassIDWarehouse and FNumber='SZ001'
-- 查询使用状态ID
DECLARE @FUseState int;
select @FUseState=FInterID from t_Submessage where FName='使用' and FTypeID = 170
-- 查询计价方法
DECLARE @FTrack int ;
select @FTrack=FInterID from t_SubMessage where FName='加权平均法' and FTypeID = 200
-- 查询存货科目
declare @FAcctID int;
select @FAcctID=FAccountID from t_Account where FNumber='1403'
--销售收入科目代码
declare @FSaleAcctID int;
select @FSaleAcctID=FAccountID from t_Account where FNumber='6051'
--销售成本科目代码
declare @FCostAcctID int;
select @FCostAcctID=FAccountID from t_Account where FNumber='6402'
-- 查询计划策略
declare @FPlanTrategy int;
select @FPlanTrategy=FInterID from t_Submessage where FName='无' and FTypeID = 167
-- 查询计划模式
declare @FPlanMode int;
select @FPlanMode=FInterID from t_Submessage where FName='MTS计划模式' and FTypeID = 606
--查询订货策略
declare @FOrderTrategy int;
select @FOrderTrategy=FInterID from t_Submessage where FName='最小最大计划(MMP)' and FTypeID = 169
-- 查询 采购检验方式
--采购检验方式
declare @FInspectionLevel int;
--产品检验方式
declare @FProChkMde int;
--委外加工检验方式
declare @FWWChkMde int;
--发货检验方式
declare @FSOChkMde int;
--退货检验方式
declare @FWthDrwChkMde int;
--库存检验方式
declare @FStkChkMde int;
--其他检验方式
declare @FOtherChkMde int;
select @FInspectionLevel=FInterID,
@FProChkMde=FInterID,
@FWWChkMde=FInterID,
@FSOChkMde=FInterID,
@FWthDrwChkMde=FInterID,
@FStkChkMde=FInterID,
@FOtherChkMde=FInterID
from t_Submessage where FName='免检' and FTypeID = 171
-- 查询控制类型
declare @FCtrlType int;
select @FCtrlType=FInterID from t_Submessage where FName='ERP' and FTypeID = 607
-- 插入 t_ICItem 表数据
INSERT INTO t_ICItem
(FHelpCode,FModel,FAuxClassID,FErpClsID ,FTypeID,FUnitGroupID,FUnitID,FOrderUnitID,FSaleUnitID,FProductUnitID,FStoreUnitID,
FSecUnitID,FSecCoefficient,FDefaultLoc,FSPID,FSource,FQtyDecimal,FLowLimit,FHighLimit,FSecInv,FUseState,FIsEquipment,FEquipmentNum,FIsSparePart,FFullName,FApproveNo,FAlias,FOrderRector,FPOHighPrice,FPOHghPrcMnyType,FWWHghPrc,FWWHghPrcMnyType,FSOLowPrc,FSOLowPrcMnyType,FIsSale,FProfitRate,FOrderPrice,FSalePrice,FIsSpecialTax,FISKFPeriod,FKFPeriod,FStockTime,FBatchManager,FBookPlan,FBeforeExpire,FCheckCycUnit,FOIHighLimit,FOILowLimit,FSOHighLimit,FSOLowLimit,FInHighLimit,FInLowLimit,FPickHighLimit,FPickLowLimit,FTrack,FPlanPrice,FPriceDecimal,FAcctID,FSaleAcctID,FCostAcctID,FAPAcctID,FAdminAcctID,FGoodSpec,FTaxRate,FCostProject,FIsSNManage,F_101,F_102,F_103,FCBRestore,FNote,FPlanTrategy,FPlanMode,FOrderTrategy,FFixLeadTime,FLeadTime,FTotalTQQ,FOrderInterVal,FQtyMin,FQtyMax,FBatchAppendQty,FOrderPoint,FBatFixEconomy,FBatChangeEconomy,FRequirePoint,FPlanPoint,FDefaultRoutingID,FDefaultWorkTypeID,FProductPrincipal,FPlanner,FIsBackFlush,FBackFlushStockID,FBackFlushSPID,FPutInteger,FDailyConsume,FMRPCon,FMRPOrder,FChartNumber,FIsKeyItem,FGrossWeight,FNetWeight,FMaund,FLength,FWidth,FHeight,FSize,FCubicMeasure,FStandardCost,FCBAppendRate,FCBAppendProject,FCostBomID,FCBRouting,FStdBatchQty,FStandardManHour,FStdPayRate,FChgFeeRate,FStdFixFeeRate,FOutMachFee,FOutMachFeeProject,FPieceRate,FPOVAcctID,FPIVAcctID,FMCVAcctID,FPCVAcctID,FSLAcctID,FCAVAcctID,FInspectionLevel,FProChkMde,FWWChkMde,FSOChkMde,FWthDrwChkMde,FStkChkMde,FOtherChkMde,FSampStdCritical,FSampStdStrict,FSampStdSlight,FStkChkPrd,FStkChkAlrm,FInspectionProject,FIdentifier,FVersion,FNameEn,FModelEn,FHSNumber,FExportRate,FFirstUnit,FSecondUnit,FImpostTaxRate,FConsumeTaxRate,FFirstUnitRate,FSecondUnitRate,FIsManage,FManageType,FLenDecimal,FCubageDecimal,FWeightDecimal,FIsCharSourceItem,FCtrlType,FCtrlStraregy,FContainerName,FKanBanCapability,FBatchSplitDays,FBatchSplit,FDefaultReadyLoc,FSPIDReady,FStartService,FMakeFile,FIsFix,FTtermOfService,FTtermOfUsefulTime,FIsFixedReOrder,FOnlineShopPNo,FOnlineShopPName,FForbbitBarcodeEdit,FDSManagerID,FUnitPackageNumber,FOrderDept,FAuxInMrpCal,FProductDesigner,
FPinYin,FPY,FIsSNBanDing,FIsSNRpt,FShortNumber,FNumber,FName,
FParentID,FItemID)
VALUES
(
NULL,@materialModel,0,1 ,0,@FUnitGroupID,@FUnitID,@FOrderUnitID,@FSaleUnitID,@FProductUnitID,@FUnitID,
0,'0',@FDefaultLoc,@FSPID,0,1,'0','1000000','0',@FUseState,0,NULL,0,NULL,NULL,NULL,0,'0',1,'0',1,'0',1,0,'0','0','0',0,0,0,0,0,0,0,0,'0','0','0','0','0','0','0','0',
@FTrack,'0',4,@FAcctID,@FSaleAcctID,@FCostAcctID,0,0,0,'0',0,0,0,NULL,NULL,0,NULL,
@FPlanTrategy,@FPlanMode,@FOrderTrategy,0,1,0,0,'1','100000','0','0','0','1',1,1,0,0,0,0,0,0,0,0,'0',0,0,NULL,0,'0','0',0,'0','0','0','0',0,'0','0',0,0,0,'1','0','0','0','0','0',0,'0',0,0,0,0,0,0,
@FInspectionLevel,@FProChkMde,@FWWChkMde,@FSOChkMde,@FWthDrwChkMde,@FStkChkMde,@FOtherChkMde,0,0,0,0,0,0,0,NULL,NULL,NULL,0,'0',NULL,NULL,'0','0','0','0',0,0,0,0,0,0,
@FCtrlType,0,NULL,1,0,0,0,0,0,0,0,0,0,0,NULL,NULL,0,0,'0',0,0,0,
@materialNamePinYin,@materialNamePY,0,0,( SELECT RIGHT(@materialCode, CHARINDEX('.',REVERSE(@materialCode)) - 1)),@materialCode,@materialName,
@ParentId,@FItemID
)
-- 插入 log
INSERT INTO t_Log (FDate,FUserID,FFunctionID,FStatement,FDescription,FMachineName,FIPAddress)
VALUES (getdate(),@UserId,'A00701',5,'新建核算项目:'+@materialCode+' 核算项目类别:物料','administrator','存储过程:sp_In3AddMaterialCode')
-- 插入 t_BaseProperty 表数据
Insert Into t_BaseProperty(FTypeID, FItemID, FCreateDate, FCreateUser, FLastModDate, FLastModUser, FDeleteDate, FDeleteUser)
Values(3, @FItemID,getdate(),'administrator', Null, Null, Null, Null)
-- 插入 Access_t_ICItem 表数据
Delete from Access_t_ICItem where FItemID=@FItemID
Insert into Access_t_ICItem(FItemID,FParentIDX,FDataAccessView,FDataAccessEdit,FDataAccessDelete)
Values(@FItemID,@ParentId,convert(varbinary(7200),REPLICATE(char(255),100)),convert(varbinary(7200),REPLICATE(char(255),100)),convert(varbinary(7200),REPLICATE(char(255),100)))
-- 审核
Update t_Item set FChkUserID= @UserId Where Isnull(FChkUserID,'')='' And FItemID in (@FItemID)
DECLARE @param VARCHAR(100) = '(param)';
SET @param = REPLACE(@param, 'param', CAST(@FItemID AS VARCHAR));
exec p_BASE_AutoCheckParentItems @UserId,@param
-- 提交事务
COMMIT TRANSACTION;
select top 1 @dataMaterialCode =FItemID from t_Item
where FItemClassID = 4 and FNumber =@materialCode
RETURN @dataMaterialCode;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION; -- 回滚事务
SELECT ERROR_MESSAGE(); -- 输出错误信息(如果有)
RETURN 0;
END CATCH
END
09-01