03.集成原材料物料编码进入金蝶K3

BXS_null
于 2024-08-27 18:50:59 发布
阅读量77
点赞数 1
分类专栏：金蝶K3数据集成文章标签：金蝶K3数据集成
本文链接：https://blog.csdn.net/weixin_40068689/article/details/141610088
版权
金蝶K3数据集成专栏收录该内容
3 篇文章 0 订阅
订阅专栏
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