Create PROCEDURE [dbo].[sp_addProductCode]
@materialName varchar(100),
@materailCategoryCode varchar(50),
@materailCategoryName varchar(500),
@result varchar(100) output
AS
BEGIN
--声明参数
--DECLARE @materialCode VARCHAR(100) = '11.02.01.000004';
--DECLARE @materialName VARCHAR(100) = '测试3';
DECLARE @materialNamePinYin VARCHAR(100) = '';
DECLARE @materialNamePY VARCHAR(50) = '';
-- 定义分隔符
DECLARE @Separator VARCHAR(10)='.';
-- 获取上级物料编码
DECLARE @ParentMaterialCode VARCHAR(100)=REPLACE(@materailCategoryCode,@Separator,'.')
DECLARE @ParentId int;
DECLARE @ParentFullName VARCHAR(100);
DECLARE @ParentFLevel int;
DECLARE @FItemID int;
DECLARE @dataMaterialCode VARCHAR(100)='';
DECLARE @UserId int;
select @UserId=FUserID from t_Base_User where FName='Administrator'
-- 查询物料是否存在
select top 1 @dataMaterialCode =FNumber from t_Item
where FItemClassID = 4 and FName=@materialName
if @dataMaterialCode!=''
begin
set @result=@dataMaterialCode;
--print @dataMaterialCode;
return 1;
end
-- 开始事务
BEGIN TRY
BEGIN TRANSACTION;
--插入上级物料
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
-- 查询parent Info
--select @ParentId=FItemID,@ParentFLevel=FLevel,@ParentFullName=FFullName from t_Item
--where FItemClassID = 4 and FNumber =(SELECT LEFT(@materialCode, LEN(@materialCode) - CHARINDEX('.', REVERSE(@materialCode))))
declare @FItemClassIDMaterial int;
declare @FItemClassIDWarehouse int;
select @FItemClassIDMaterial=FItemClassID from t_ItemClass where FName='物料'
select @FItemClassIDWarehouse=FItemClassID from t_ItemClass where FName='仓库'
select @ParentId=FItemID,@ParentFLevel=FLevel,@ParentFullName=FFullName from t_Item
where FItemClassID = @FItemClassIDMaterial and FNumber =@ParentMaterialCode
-- 自动生成短号
DECLARE @FShortNumber varchar(20)='60000000';
select top 1@FShortNumber=FShortNumber from t_Item
where FItemClassID = 4 and FParentID=@ParentId and LEFT(FShortNumber, 1)='6' order by FShortNumber desc
set @FShortNumber=@FShortNumber+1
-- 生成完整成品编码Code
DECLARE @materialCode varchar(50)=@ParentMaterialCode+'.'+RIGHT(REPLICATE('0', 8) + @FShortNumber, 8);
-- 插入 t_Item 表数据
INSERT INTO t_Item (FItemClassID,FParentID,FLevel,FName,FNumber,FShortNumber,FFullNumber,FDetail,UUID,FDeleted,FFullName)
VALUES (@FItemClassIDMaterial,@ParentId,@ParentFLevel+1,@materialName,@materialCode,(SELECT RIGHT(@materialCode, CHARINDEX('.',REVERSE(@materialCode)) - 1)),@materialCode,1,(Select newid()),0,@ParentFullName+'_'+@materialName)
select @FItemID=FItemID from t_Item where FItemClassID=4 and FFullNumber=@materialCode
-- 获取数量组ID
DECLARE @FUnitGroupID int ;
select @FUnitGroupID=FUnitGroupID from t_UnitGroup where FName='数量组'
-- 基本单位 默认为数量组
--基本单位
DECLARE @FUnitID int;
--采购单位
DECLARE @FOrderUnitID int;
DECLARE @FSaleUnitID int;
DECLARE @FProductUnitID int;
select @FUnitID=FMeasureUnitID,@FOrderUnitID=FMeasureUnitID ,
@FSaleUnitID=FMeasureUnitID,@FProductUnitID=FMeasureUnitID
from t_MeasureUnit t1
inner join t_UnitGroup t2 On '数量组'= t2.FName And t1.FUnitGroupID = t2.FUnitGroupID
where t1.FName='PCS'
-- 获取仓库ID
DECLARE @FDefaultLoc int;
select @FDefaultLoc=FItemID from t_Item where FItemClassID = @FItemClassIDWarehouse and FNumber='SZ002'
-- 查询使用状态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='1405'
--销售收入科目代码
declare @FSaleAcctID int;
select @FSaleAcctID=FAccountID from t_Account where FNumber='6001.001.02'
--销售成本科目代码
declare @FCostAcctID int;
select @FCostAcctID=FAccountID from t_Account where FNumber='6401.02'
-- 查询计划策略
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='批对批(LFL)' 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,'',0,2 ,0,@FUnitGroupID,@FUnitID,@FOrderUnitID,@FSaleUnitID,@FProductUnitID,@FOrderUnitID,
0,'0',@FDefaultLoc,0,0,4,'0','1000','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,'13',0,0,0,NULL,NULL,1,NULL,
@FPlanTrategy,@FPlanMode,@FOrderTrategy,0,0,0,0,'1','10000','1','0','0','1',1,1,0,0,0,0,0,0,0,0,'0',1,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,9999,0,0,0,NULL,NULL,NULL,0,'0',NULL,NULL,'0','0','0','0',0,0,2,4,2,0,
@FCtrlType,0,NULL,1,0,0,0,0,0,0,0,0,0,1,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+' 核算项目类别:物料','admin','存储过程:sp_In3AddProductCode')
-- 插入 t_BaseProperty 表数据
Insert Into t_BaseProperty(FTypeID, FItemID, FCreateDate, FCreateUser, FLastModDate, FLastModUser, FDeleteDate, FDeleteUser)
Values(3, @FItemID,getdate(),'admin', 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;
set @result=@materialCode;
return 1;
--print @materialCode
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION; -- 回滚事务
SELECT ERROR_MESSAGE(); -- 输出错误信息(如果有)
set @result='false';
return 0;
END CATCH
END;
GO
02.集成成品料号数据进入金蝶K3
于 2024-08-26 11:40:23 首次发布