用定时任务，主要点：1、要处理成本对象，成本对象的也要同步过去，否则金蝶界面修改物料会报错。 2、要处理父节点，否则在B账套会对应不上大类。 3、B账套缺少的大类要通过sql来创建上。
一、在B账套上创建t_Item_synLog表来记录同步记录，以便有得跟踪
CREATE TABLE [dbo].[t_Item_synLog](
[FItemID] [nvarchar](50) NULL,
[FOutFItemID] [nvarchar](50) NULL,
[FItemClassID] [nvarchar](50) NULL,
[FParentID] [nvarchar](50) NULL,
[FOutParentID] [nvarchar](50) NULL,
[FisParent] [nvarchar](50) NULL,
[datetime] [datetime] NULL,
[FNumber] [nvarchar](250) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Item_synLog] ADD CONSTRAINT [DF_t_Item_synLog_datetime] DEFAULT (sysdatetime()) FOR [datetime]二、在A账套创建视图两视图1、 v_Item_ParentID_JB 用于A账套B对应物料父节点的ID v_Item_ParentID_JB2001 用于A账套B对应成本对象父节点的ID
CREATE view [dbo].[v_Item_ParentID_JB] as
select distinct p.FItemID , p.FNumber , p.fname,po.FItemID as FItemIDOut ,po.FNumber as FNumberOut, po.fname as FNameOut from AIS20230113160504.dbo.t_Item i
inner join AIS20230113160504.dbo.t_Item p on i.FParentID=p.FItemID
left join jb2023.dbo.t_Item po on po.FNumber=p.FNumber
where i.FItemClassID=4 and p.FItemClassID=4 and po.FItemClassID=4
GO
CREATE view [dbo].[v_Item_ParentID_JB2001] as
select distinct p.FItemID , p.FNumber , p.fname,po.FItemID as FItemIDOut ,po.FNumber as FNumberOut, po.fname as FNameOut from AIS20230113160504.dbo.t_Item i
inner join AIS20230113160504.dbo.t_Item p on i.FParentID=p.FItemID
left join jb2023.dbo.t_Item po on po.FNumber=p.FNumber
where i.FItemClassID=2001 and p.FItemClassID=2001 and po.FItemClassID=2001
GO
Create view [dbo].[v_Item_ParentID_SM] as
select distinct p.FItemID , p.FNumber , p.fname,po.FItemID as FItemIDOut ,po.FNumber as FNumberOut, po.fname as FNameOut from AIS20230113160504.dbo.t_Item i
inner join AIS20230113160504.dbo.t_Item p on i.FParentID=p.FItemID
left join summit2023.dbo.t_Item po on po.FNumber=p.FNumber
where i.FItemClassID=4 and p.FItemClassID=4 and po.FItemClassID=4
GO
create view [dbo].[v_Item_ParentID_SM2001] as
select distinct p.FItemID , p.FNumber , p.fname,po.FItemID as FItemIDOut ,po.FNumber as FNumberOut, po.fname as FNameOut from AIS20230113160504.dbo.t_Item i
inner join AIS20230113160504.dbo.t_Item p on i.FParentID=p.FItemID
left join summit2023.dbo.t_Item po on po.FNumber=p.FNumber
where i.FItemClassID=2001 and p.FItemClassID=2001 and po.FItemClassID=2001
GO
三、创建定时同步的sql，注意单位的对应
在敬邦和时美特账号分别创建单位对应视图
CREATE view [dbo].[t_MeasureUnit_LB_compare]
as
select jbu.*,lbu.FMeasureUnitID as FMeasureUnitIDlb, lbu.FUnitGroupID as FUnitGroupIDlb from AIS20230113160504.dbo. t_MeasureUnit lbu
join t_MeasureUnit jbu on lbu.FName=jbu.FName
where lbu.FDeleted=0 and jbu.FDeleted=0 and jbu.FStandard=1
GO
----处理成本对象
-- 先处理分类在目标账套上创建对应少的分类 用于Item表的parentID
declare @FItemID_Parent2001 int,@FParentID_Parent2001 int ,@FNumber_Parent2001 varchar(250)
--定义游标
declare ICItemParent_cursor2001 cursor for
select FItemID,FParentID,FNumber from AIS20230113160504.dbo.t_Item where FDeleted=0 and FItemClassID =2001 and FParentID
in ( select FItemID from AIS20230113160504.dbo.t_Item where FNumber in('60','61','62','63','64','65','66','67','68','69','70','80','81','88','89') and FDeleted=0 and FItemClassID =2001)
and FNumber not in ( select FNumber from jb2023.dbo.t_Item where FItemClassID =2001 and FDeleted=0
and FParentID in ( select FItemID from jb2023.dbo.t_Item where FNumber in('60','61','62','63','64','65','66','67','68','69','70','80','81','88','89') and FItemClassID =2001 ) )
--打开游标
open ICItemParent_cursor2001
--取值
fetch next from ICItemParent_cursor2001 into @FItemID_Parent2001 ,@FParentID_Parent2001 ,@FNumber_Parent2001
while(@@FETCH_STATUS=0)
begin
-- 生成新的FInterID
DECLARE @FItemID_Parent_New2001 INT
exec jb2023.dbo. GetICMaxNum 't_Item', @FItemID_Parent_New2001 output
--select * from [jb2023].[dbo].[t_Item] where FItemID=37045 ; FNumber ='69.306'
INSERT INTO [jb2023].[dbo].[t_Item]
([FItemID]
,[FItemClassID]
,[FExternID]
,[FNumber]
,[FParentID]
,[FLevel]
,[FDetail]
,[FName]
,[FUnUsed]
,[FBrNo]
,[FFullNumber]
,[FDiff]
,[FDeleted]
,[FShortNumber]
,[FFullName]
-- ,[UUID]
,[FGRCommonID]
,[FSystemType]
,[FUseSign]
,[FChkUserID]
,[FAccessory]
,[FGrControl]
,[FHavePicture])
SELECT @FItemID_Parent_New2001 -- [FItemID]
,FItemClassID
,FExternID
,FNumber
,isnull((select FItemIDOut from AIS20230113160504.dbo.v_Item_ParentID_JB2001 where FItemID=@FParentID_Parent2001),0)--[FParentID]
,[FLevel]
,[FDetail]
,[FName]
,[FUnUsed]
,[FBrNo]
,[FFullNumber]
,[FDiff]
,[FDeleted]
,[FShortNumber]
,[FFullName]
-- ,[UUID]
,[FGRCommonID]
,[FSystemType]
,[FUseSign]
,[FChkUserID]
,[FAccessory]
,[FGrControl]
,[FHavePicture]
FROM [AIS20230113160504].[dbo].[t_Item] where FItemID=@FItemID_Parent2001
INSERT INTO [jb2023].[dbo].[t_Item_synLog]
([FItemID] --敬邦的ID
,[FOutFItemID]-- 力邦的ID
,[FItemClassID]
,[FParentID]
,[FOutParentID]
,[FisParent] ,
FNumber)
VALUES
(@FItemID_Parent_New2001,
@FItemID_Parent2001,
'2001',
isnull((select FItemIDOut from AIS20230113160504.dbo.v_Item_ParentID_JB2001 where FItemID=@FParentID_Parent2001),0),
@FParentID_Parent2001,
'Y',
@FNumber_Parent2001
)
fetch next from ICItemParent_cursor2001 into @FItemID_Parent2001 ,@FParentID_Parent2001 ,@FNumber_Parent2001
end
close ICItemParent_cursor2001
deallocate ICItemParent_cursor2001;
-- 处理物料的父节点
-- 先处理分类在目标账套上创建对应少的分类 用于Item表的parentID
declare @FItemID_Parent int,@FParentID_Parent int , @FNumber_Parent varchar(250);
--定义游标
declare ICItemParent_cursor cursor for
select FItemID,FParentID ,FNumber from AIS20230113160504.dbo.t_Item where FDeleted=0 and FItemClassID =4 and FParentID
in ( select FItemID from AIS20230113160504.dbo.t_Item where FNumber in('60','61','62','63','64','65','66','67','68','69','70','80','81','88','89') and FDeleted=0 and FItemClassID =4)
and FNumber not in ( select FNumber from jb2023.dbo.t_Item where FItemClassID =4 and FDeleted=0
and FParentID in ( select FItemID from jb2023.dbo.t_Item where FNumber in('60','61','62','63','64','65','66','67','68','69','70','80','81','88','89') and FItemClassID =4 ) )
--打开游标
open ICItemParent_cursor
--取值
fetch next from ICItemParent_cursor into @FItemID_Parent ,@FParentID_Parent ,@FNumber_Parent
while(@@FETCH_STATUS=0)
begin
-- 生成新的FInterID
DECLARE @FItemID_Parent_New INT
exec jb2023.dbo. GetICMaxNum 't_Item', @FItemID_Parent_New output
--select * from [jb2023].[dbo].[t_Item] where FItemID=37045 ; FNumber ='69.306'
INSERT INTO [jb2023].[dbo].[t_Item]
([FItemID]
,[FItemClassID]
,[FExternID]
,[FNumber]
,[FParentID]
,[FLevel]
,[FDetail]
,[FName]
,[FUnUsed]
,[FBrNo]
,[FFullNumber]
,[FDiff]
,[FDeleted]
,[FShortNumber]
,[FFullName]
-- ,[UUID]
,[FGRCommonID]
,[FSystemType]
,[FUseSign]
,[FChkUserID]
,[FAccessory]
,[FGrControl]
,[FHavePicture])
SELECT @FItemID_Parent_New -- [FItemID]
,FItemClassID
,FExternID
,FNumber
,isnull((select FItemIDOut from AIS20230113160504.dbo.v_Item_ParentID_JB where FItemID=@FParentID_Parent),0)--[FParentID]
,[FLevel]
,[FDetail]
,[FName]
,[FUnUsed]
,[FBrNo]
,[FFullNumber]
,[FDiff]
,[FDeleted]
,[FShortNumber]
,[FFullName]
-- ,[UUID]
,[FGRCommonID]
,[FSystemType]
,[FUseSign]
,[FChkUserID]
,[FAccessory]
,[FGrControl]
,[FHavePicture]
FROM [AIS20230113160504].[dbo].[t_Item] where FItemID=@FItemID_Parent
INSERT INTO [jb2023].[dbo].[t_Item_synLog]
([FItemID] --敬邦的ID
,[FOutFItemID]-- 力邦的ID
,[FItemClassID]
,[FParentID] --敬邦的ID
,[FOutParentID] -- 力邦的ID
,[FisParent],
FNumber )
VALUES
(@FItemID_Parent_New,
@FItemID_Parent,
'4',
isnull((select FItemIDOut from AIS20230113160504.dbo.v_Item_ParentID_JB where FItemID=@FParentID_Parent),0),
@FParentID_Parent,
'Y',
@FNumber_Parent
)
fetch next from ICItemParent_cursor into @FItemID_Parent ,@FParentID_Parent ,@FNumber_Parent
end
close ICItemParent_cursor
deallocate ICItemParent_cursor;
--- 同步到敬邦账套
declare @FItemID_old int,@FNumber varchar(80), @FParentID int;
--定义游标
declare ICItem_cursor cursor for
select FItemID,FNumber,FParentID from AIS20230113160504.dbo.t_ICItem where F_108=2 and FNumber not in (select FNumber from jb2023.dbo.t_ICItem)
--打开游标
open ICItem_cursor
--取值
fetch next from ICItem_cursor into @FItemID_old,@FNumber,@FParentID
while(@@FETCH_STATUS=0)
begin
-- 生成新的FInterID
DECLARE @FInterID INT
exec jb2023.dbo. GetICMaxNum 't_Item', @FInterID output
-- select * from jb2023.dbo.ICMaxNum where FTableName='t_Item'
--物料视图
INSERT INTO [jb2023].[dbo].[t_ICItem]
([FItemID]
,[FModel]
,[FName]
,[FHelpCode]
,[FDeleted]
,[FShortNumber]
,[FNumber]
,[FParentID]
,[FBrNo]
,[FTopID]
,[FRP]
,[FOmortize]
,[FOmortizeScale]
,[FForSale]
,[FStaCost]
,[FOrderPrice]
,[FOrderMethod]
,[FPriceFixingType]
,[FSalePriceFixingType]
,[FPerWastage]
,[FARAcctID]
,[FPlanPriceMethod]
,[FPlanClass]
,[FPY]
,[FPinYin]
,[FErpClsID]
,[FUnitID]
,[FUnitGroupID]
,[FDefaultLoc]
,[FSPID]
,[FSource]
,[FQtyDecimal]
,[FLowLimit]
,[FHighLimit]
,[FSecInv]
,[FUseState]
,[FIsEquipment]
,[FEquipmentNum]
,[FIsSparePart]
,[FFullName]
,[FSecUnitID]
,[FSecCoefficient]
,[FSecUnitDecimal]
,[FAlias]
,[FOrderUnitID]
,[FSaleUnitID]
,[FStoreUnitID]
,[FProductUnitID]
,[FApproveNo]
,[FAuxClassID]
,[FTypeID]
,[FPreDeadLine]
,[FSerialClassID]
,[FDefaultReadyLoc]
,[FSPIDReady]
,[FDSManagerID]
,[FForbbitBarcodeEdit]
,[FOrderRector]
,[FPOHghPrcMnyType]
,[FPOHighPrice]
,[FWWHghPrc]
,[FWWHghPrcMnyType]
,[FSOLowPrc]
,[FSOLowPrcMnyType]
,[FIsSale]
,[FProfitRate]
,[FSalePrice]
,[FBatchManager]
,[FISKFPeriod]
,[FKFPeriod]
,[FTrack]
,[FPlanPrice]
,[FPriceDecimal]
,[FAcctID]
,[FSaleAcctID]
,[FCostAcctID]
,[FAPAcctID]
,[FGoodSpec]
,[FCostProject]
,[FIsSnManage]
,[FStockTime]
,[FBookPlan]
,[FBeforeExpire]
,[FTaxRate]
,[FAdminAcctID]
,[FNote]
,[FIsSpecialTax]
,[FSOHighLimit]
,[FSOLowLimit]
,[FOIHighLimit]
,[FOILowLimit]
,[FDaysPer]
,[FLastCheckDate]
,[FCheckCycle]
,[FCheckCycUnit]
,[FStockPrice]
,[FABCCls]
,[FBatchQty]
,[FClass]
,[FCostDiffRate]
,[FDepartment]
,[FSaleTaxAcctID]
,[FCBBmStandardID]
,[FCBRestore]
,[FPickHighLimit]
,[FPickLowLimit]
,[FOnlineShopPName]
,[FOnlineShopPNo]
,[FUnitPackageNumber]
,[FOrderDept]
,[FBarcodeQtyEachTime]
,[FIsSNBanDing]
,[FIsSNRpt]
,[FPlanTrategy]
,[FOrderTrategy]
,[FLeadTime]
,[FFixLeadTime]
,[FTotalTQQ]
,[FQtyMin]
,[FQtyMax]
,[FCUUnitID]
,[FOrderInterVal]
,[FBatchAppendQty]
,[FOrderPoint]
,[FBatFixEconomy]
,[FBatChangeEconomy]
,[FRequirePoint]
,[FPlanPoint]
,[FDefaultRoutingID]
,[FDefaultWorkTypeID]
,[FProductPrincipal]
,[FDailyConsume]
,[FMRPCon]
,[FPlanner]
,[FPutInteger]
,[FInHighLimit]
,[FInLowLimit]
,[FLowestBomCode]
,[FMRPOrder]
,[FIsCharSourceItem]
,[FCharSourceItemID]
,[FPlanMode]
,[FCtrlType]
,[FCtrlStraregy]
,[FContainerName]
,[FKanBanCapability]
,[FIsBackFlush]
,[FBackFlushStockID]
,[FBackFlushSPID]
,[FBatchSplitDays]
,[FBatchSplit]
,[FIsFixedReOrder]
,[FAuxInMrpCal]
,[FProductDesigner]
,[F_101]
,[F_102]
,[F_103]
,[F_104]
,[FChartNumber]
,[FIsKeyItem]
,[FMaund]
,[FGrossWeight]
,[FNetWeight]
,[FCubicMeasure]
,[FLength]
,[FWidth]
,[FHeight]
,[FSize]
,[FVersion]
,[FStartService]
,[FMakeFile]
,[FIsFix]
,[FTtermOfService]
,[FTtermOfUsefulTime]
,[FStandardCost]
,[FStandardManHour]
,[FStdPayRate]
,[FChgFeeRate]
,[FStdFixFeeRate]
,[FOutMachFee]
,[FPieceRate]
,[FStdBatchQty]
,[FPOVAcctID]
,[FPIVAcctID]
,[FMCVAcctID]
,[FPCVAcctID]
,[FSLAcctID]
,[FCAVAcctID]
,[FCBAppendRate]
,[FCBAppendProject]
,[FCostBomID]
,[FCBRouting]
,[FOutMachFeeProject]
,[FInspectionLevel]
,[FInspectionProject]
,[FIsListControl]
,[FProChkMde]
,[FWWChkMde]
,[FSOChkMde]
,[FWthDrwChkMde]
,[FStkChkMde]
,[FOtherChkMde]
,[FStkChkPrd]
,[FStkChkAlrm]
,[FIdentifier]
,[FSampStdCritical]
,[FSampStdStrict]
,[FSampStdSlight]
,[FNameEn]
,[FModelEn]
,[FHSNumber]
,[FFirstUnit]
,[FSecondUnit]
,[FFirstUnitRate]
,[FSecondUnitRate]
,[FIsManage]
,[FPackType]
,[FLenDecimal]
,[FCubageDecimal]
,[FWeightDecimal]
,[FImpostTaxRate]
,[FConsumeTaxRate]
,[FManageType]
,[FExportRate]
,[FBarcode])
SELECT @FInterID
,[FModel]
,[FName]
,[FHelpCode]
,[FDeleted]
,[FShortNumber]
,[FNumber]
,[FParentID]
,[FBrNo]
,[FTopID]
,[FRP]
,[FOmortize]
,[FOmortizeScale]
,[FForSale]
,[FStaCost]
,[FOrderPrice]
,[FOrderMethod]
,[FPriceFixingType]
,[FSalePriceFixingType]
,[FPerWastage]
,[FARAcctID]
,[FPlanPriceMethod]
,[FPlanClass]
,[FPY]
,[FPinYin]
,[FErpClsID]
,( select top 1 jb2023.dbo.t_MeasureUnit_LB_compare.FMeasureUnitID from jb2023.dbo.t_MeasureUnit_LB_compare where FUnitGroupIDlb=[AIS20230113160504].[dbo].[t_ICItem].FUnitGroupID and FMeasureUnitIDlb=[AIS20230113160504].[dbo].[t_ICItem].FUnitID)-- 251 FUnitID]
,( select top 1 jb2023.dbo.t_MeasureUnit_LB_compare.FUnitGroupID from jb2023.dbo.t_MeasureUnit_LB_compare where FUnitGroupIDlb=[AIS20230113160504].[dbo].[t_ICItem].FUnitGroupID and FMeasureUnitIDlb=[AIS20230113160504].[dbo].[t_ICItem].FUnitID) --250 -- [FUnitGroupID]
,[FDefaultLoc]
,[FSPID]
,[FSource]
,[FQtyDecimal]
,[FLowLimit]
,[FHighLimit]
,[FSecInv]
,[FUseState]
,[FIsEquipment]
,[FEquipmentNum]
,[FIsSparePart]
,[FFullName]
,[FSecUnitID]
,[FSecCoefficient]
,[FSecUnitDecimal]
,[FAlias]
,( select top 1 jb2023.dbo.t_MeasureUnit_LB_compare.FMeasureUnitID from jb2023.dbo.t_MeasureUnit_LB_compare where FUnitGroupIDlb=[AIS20230113160504].[dbo].[t_ICItem].FUnitGroupID and FMeasureUnitIDlb=[AIS20230113160504].[dbo].[t_ICItem].FOrderUnitID) --FOrderUnitID
,( select top 1 jb2023.dbo.t_MeasureUnit_LB_compare.FMeasureUnitID from jb2023.dbo.t_MeasureUnit_LB_compare where FUnitGroupIDlb=[AIS20230113160504].[dbo].[t_ICItem].FUnitGroupID and FMeasureUnitIDlb=[AIS20230113160504].[dbo].[t_ICItem].FSaleUnitID) --FSaleUnitID
,( select top 1 jb2023.dbo.t_MeasureUnit_LB_compare.FMeasureUnitID from jb2023.dbo.t_MeasureUnit_LB_compare where FUnitGroupIDlb=[AIS20230113160504].[dbo].[t_ICItem].FUnitGroupID and FMeasureUnitIDlb=[AIS20230113160504].[dbo].[t_ICItem].FStoreUnitID) --FStoreUnitID
,( select top 1 jb2023.dbo.t_MeasureUnit_LB_compare.FMeasureUnitID from jb2023.dbo.t_MeasureUnit_LB_compare where FUnitGroupIDlb=[AIS20230113160504].[dbo].[t_ICItem].FUnitGroupID and FMeasureUnitIDlb=[AIS20230113160504].[dbo].[t_ICItem].FProductUnitID) --FProductUnitID
,[FApproveNo]
,[FAuxClassID]
,[FTypeID]
,[FPreDeadLine]
,[FSerialClassID]
,[FDefaultReadyLoc]
,[FSPIDReady]
,[FDSManagerID]
,[FForbbitBarcodeEdit]
,[FOrderRector]
,[FPOHghPrcMnyType]
,[FPOHighPrice]
,[FWWHghPrc]
,[FWWHghPrcMnyType]
,[FSOLowPrc]
,[FSOLowPrcMnyType]
,[FIsSale]
,[FProfitRate]
,[FSalePrice]
,[FBatchManager]
,[FISKFPeriod]
,[FKFPeriod]
,[FTrack]
,[FPlanPrice]
,[FPriceDecimal]
,1050--[FAcctID]
,1149--[FSaleAcctID]
,1166--[FCostAcctID]
,[FAPAcctID]
,[FGoodSpec]
,[FCostProject]
,[FIsSnManage]
,[FStockTime]
,[FBookPlan]
,[FBeforeExpire]
,[FTaxRate]
,[FAdminAcctID]
,[FNote]
,[FIsSpecialTax]
,[FSOHighLimit]
,[FSOLowLimit]
,[FOIHighLimit]
,[FOILowLimit]
,[FDaysPer]
,[FLastCheckDate]
,[FCheckCycle]
,[FCheckCycUnit]
,[FStockPrice]
,[FABCCls]
,[FBatchQty]
,[FClass]
,[FCostDiffRate]
,[FDepartment]
,[FSaleTaxAcctID]
,[FCBBmStandardID]
,[FCBRestore]
,[FPickHighLimit]
,[FPickLowLimit]
,[FOnlineShopPName]
,[FOnlineShopPNo]
,[FUnitPackageNumber]
,[FOrderDept]
,[FBarcodeQtyEachTime]
,[FIsSNBanDing]
,[FIsSNRpt]
,[FPlanTrategy]
,[FOrderTrategy]
,[FLeadTime]
,[FFixLeadTime]
,[FTotalTQQ]
,[FQtyMin]
,[FQtyMax]
,[FCUUnitID]
,[FOrderInterVal]
,[FBatchAppendQty]
,[FOrderPoint]
,[FBatFixEconomy]
,[FBatChangeEconomy]
,[FRequirePoint]
,[FPlanPoint]
,[FDefaultRoutingID]
,[FDefaultWorkTypeID]
,[FProductPrincipal]
,[FDailyConsume]
,[FMRPCon]
,[FPlanner]
,[FPutInteger]
,[FInHighLimit]
,[FInLowLimit]
,[FLowestBomCode]
,[FMRPOrder]
,[FIsCharSourceItem]
,[FCharSourceItemID]
,[FPlanMode]
,[FCtrlType]
,[FCtrlStraregy]
,[FContainerName]
,[FKanBanCapability]
,[FIsBackFlush]
,[FBackFlushStockID]
,[FBackFlushSPID]
,[FBatchSplitDays]
,[FBatchSplit]
,[FIsFixedReOrder]
,[FAuxInMrpCal]
,[FProductDesigner]
,[F_104]
,[F_105]
,[F_106]
,[F_107]
,[FChartNumber]
,[FIsKeyItem]
,[FMaund]
,[FGrossWeight]
,[FNetWeight]
,[FCubicMeasure]
,[FLength]
,[FWidth]
,[FHeight]
,[FSize]
,[FVersion]
,[FStartService]
,[FMakeFile]
,[FIsFix]
,[FTtermOfService]
,[FTtermOfUsefulTime]
,[FStandardCost]
,[FStandardManHour]
,[FStdPayRate]
,[FChgFeeRate]
,[FStdFixFeeRate]
,[FOutMachFee]
,[FPieceRate]
,[FStdBatchQty]
,[FPOVAcctID]
,[FPIVAcctID]
,[FMCVAcctID]
,[FPCVAcctID]
,[FSLAcctID]
,[FCAVAcctID]
,[FCBAppendRate]
,[FCBAppendProject]
,[FCostBomID]
,[FCBRouting]
,[FOutMachFeeProject]
,[FInspectionLevel]
,[FInspectionProject]
,[FIsListControl]
,[FProChkMde]
,[FWWChkMde]
,[FSOChkMde]
,[FWthDrwChkMde]
,[FStkChkMde]
,[FOtherChkMde]
,[FStkChkPrd]
,[FStkChkAlrm]
,[FIdentifier]
,[FSampStdCritical]
,[FSampStdStrict]
,[FSampStdSlight]
,[FNameEn]
,[FModelEn]
,[FHSNumber]
,[FFirstUnit]
,[FSecondUnit]
,[FFirstUnitRate]
,[FSecondUnitRate]
,[FIsManage]
,[FPackType]
,[FLenDecimal]
,[FCubageDecimal]
,[FWeightDecimal]
,[FImpostTaxRate]
,[FConsumeTaxRate]
,[FManageType]
,[FExportRate]
,[FBarcode]
FROM [AIS20230113160504].[dbo].[t_ICItem] where FItemID= @FItemID_old
--item表处理物料
INSERT INTO [jb2023].[dbo].[t_Item]
([FItemID]
,[FItemClassID]
,[FExternID]
,[FNumber]
,[FParentID]
,[FLevel]
,[FDetail]
,[FName]
,[FUnUsed]
,[FBrNo]
,[FFullNumber]
,[FDiff]
,[FDeleted]
,[FShortNumber]
,[FFullName]
,[UUID]
,[FGRCommonID]
,[FSystemType]
,[FUseSign]
,[FChkUserID]
,[FAccessory]
,[FGrControl]
,[FHavePicture])
SELECT @FInterID,
[FItemClassID]
,[FExternID]
,[FNumber]
,isnull((select FItemIDOut from AIS20230113160504.dbo.v_Item_ParentID_JB where FItemID=FParentID),0)--[FParentID]
,[FLevel]
,[FDetail]
,[FName]
,[FUnUsed]
,[FBrNo]
,[FFullNumber]
,[FDiff]
,[FDeleted]
,[FShortNumber]
,[FFullName]
,[UUID]
,[FGRCommonID]
,[FSystemType]
,[FUseSign]
,[FChkUserID]
,[FAccessory]
,[FGrControl]
,[FHavePicture]
from [AIS20230113160504].[dbo].[t_Item] where FItemID=@FItemID_old
--物料图片表
INSERT INTO [jb2023].[dbo].[t_Accessory]
([FTypeID]
,[FItemID]
,[FDesc]
,[FFileName]
,[FFile]
,[FFileSize]
,[FUploader]
,[FUploadTime]
,[FChecker]
,[FIsPIC]
,[FData]
,[FVersion]
,[FSaveMode]
,[FPage]
,[FEntryID]
,[FShowFileName])
SELECT [FTypeID]
,@FInterID--[FItemID]
,[FDesc]
,[FFileName]
,[FFile]
,[FFileSize]
,[FUploader]
,[FUploadTime]
,[FChecker]
,[FIsPIC]
,[FData]
,[FVersion]
,[FSaveMode]
,[FPage]
,[FEntryID]
,[FShowFileName] FROM [AIS20230113160504].[dbo].t_Accessory where FItemID= @FItemID_old
--物料
INSERT INTO [jb2023].[dbo].[t_Item_synLog]
([FItemID] --敬邦的ID
,[FOutFItemID]-- 力邦的ID
,[FItemClassID]
,[FParentID] --敬邦的ID
,[FOutParentID] -- 力邦的ID
,[FisParent],
FNumber )
VALUES
(@FInterID,
@FItemID_old,
'4',
isnull((select FItemIDOut from AIS20230113160504.dbo.v_Item_ParentID_JB where FItemID=@FParentID),0),
@FParentID,
'N',
@FNumber
)
DECLARE @FInterID2001 INT
exec jb2023.dbo. GetICMaxNum 't_Item', @FInterID2001 output
--item表处理成本对象
INSERT INTO [jb2023].[dbo].[t_Item]
([FItemID]
,[FItemClassID]
,[FExternID]
,[FNumber]
,[FParentID]
,[FLevel]
,[FDetail]
,[FName]
,[FUnUsed]
,[FBrNo]
,[FFullNumber]
,[FDiff]
,[FDeleted]
,[FShortNumber]
,[FFullName]
,[UUID]
,[FGRCommonID]
,[FSystemType]
,[FUseSign]
,[FChkUserID]
,[FAccessory]
,[FGrControl]
,[FHavePicture])
SELECT @FInterID2001,
[FItemClassID]
,[FExternID]
,[FNumber]
,isnull((select FItemIDOut from AIS20230113160504.dbo.v_Item_ParentID_JB2001 where FItemID=FParentID),0)--[FParentID]
,[FLevel]
,[FDetail]
,[FName]
,[FUnUsed]
,[FBrNo]
,[FFullNumber]
,[FDiff]
,[FDeleted]
,[FShortNumber]
,[FFullName]
,[UUID]
,[FGRCommonID]
,[FSystemType]
,[FUseSign]
,[FChkUserID]
,[FAccessory]
,[FGrControl]
,[FHavePicture]
from [AIS20230113160504].[dbo].[t_Item] where FNumber=@FNumber and FItemID<>@FItemID_old
--处理成本对象
INSERT INTO [jb2023].[dbo].[t_Item_synLog]
([FItemID] --敬邦的ID
,[FOutFItemID]-- 力邦的ID
,[FItemClassID]
,[FParentID] --敬邦的ID
,[FOutParentID] -- 力邦的ID
,[FisParent]
,FNumber)
VALUES
(@FInterID2001,
(select top 1 FItemID from [AIS20230113160504].[dbo].[t_Item] where FNumber=@FNumber and FItemID<>@FItemID_old),
'2001',
'',
'',
'N',
@FNumber
)
fetch next from ICItem_cursor into @FItemID_old,@FNumber,@FParentID
end
close ICItem_cursor
deallocate ICItem_cursor;
----处理成本对象
-- 先处理分类在目标账套上创建对应少的分类 用于Item表的parentID
declare @FItemID_Parent2001 int,@FParentID_Parent2001 int ,@FNumber_Parent2001 varchar(250)
--定义游标
declare ICItemParent_cursor2001 cursor for
select FItemID,FParentID,FNumber from AIS20230113160504.dbo.t_Item where FDeleted=0 and FItemClassID =2001 and FParentID
in ( select FItemID from AIS20230113160504.dbo.t_Item where FNumber in('60','61','62','63','64','65','66','67','68','69','70','80','81','88','89') and FDeleted=0 and FItemClassID =2001)
and FNumber not in ( select FNumber from summit2023.dbo.t_Item where FItemClassID =2001 and FDeleted=0
and FParentID in ( select FItemID from summit2023.dbo.t_Item where FNumber in('60','61','62','63','64','65','66','67','68','69','70','80','81','88','89') and FItemClassID =2001 ) )
--打开游标
open ICItemParent_cursor2001
--取值
fetch next from ICItemParent_cursor2001 into @FItemID_Parent2001 ,@FParentID_Parent2001 ,@FNumber_Parent2001
while(@@FETCH_STATUS=0)
begin
-- 生成新的FInterID
DECLARE @FItemID_Parent_New2001 INT
exec summit2023.dbo. GetICMaxNum 't_Item', @FItemID_Parent_New2001 output
--select * from [summit2023].[dbo].[t_Item] where FItemID=37045 ; FNumber ='69.306'
INSERT INTO [summit2023].[dbo].[t_Item]
([FItemID]
,[FItemClassID]
,[FExternID]
,[FNumber]
,[FParentID]
,[FLevel]
,[FDetail]
,[FName]
,[FUnUsed]
,[FBrNo]
,[FFullNumber]
,[FDiff]
,[FDeleted]
,[FShortNumber]
,[FFullName]
-- ,[UUID]
,[FGRCommonID]
,[FSystemType]
,[FUseSign]
,[FChkUserID]
,[FAccessory]
,[FGrControl]
,[FHavePicture])
SELECT @FItemID_Parent_New2001 -- [FItemID]
,FItemClassID
,FExternID
,FNumber
,isnull((select FItemIDOut from AIS20230113160504.dbo.v_Item_ParentID_SM2001 where FItemID=@FParentID_Parent2001),0)--[FParentID]
,[FLevel]
,[FDetail]
,[FName]
,[FUnUsed]
,[FBrNo]
,[FFullNumber]
,[FDiff]
,[FDeleted]
,[FShortNumber]
,[FFullName]
-- ,[UUID]
,[FGRCommonID]
,[FSystemType]
,[FUseSign]
,[FChkUserID]
,[FAccessory]
,[FGrControl]
,[FHavePicture]
FROM [AIS20230113160504].[dbo].[t_Item] where FItemID=@FItemID_Parent2001
INSERT INTO [summit2023].[dbo].[t_Item_synLog]
([FItemID] --敬邦的ID
,[FOutFItemID]-- 力邦的ID
,[FItemClassID]
,[FParentID]
,[FOutParentID]
,[FisParent] ,
FNumber)
VALUES
(@FItemID_Parent_New2001,
@FItemID_Parent2001,
'2001',
isnull((select FItemIDOut from AIS20230113160504.dbo.v_Item_ParentID_SM2001 where FItemID=@FParentID_Parent2001),0),
@FParentID_Parent2001,
'Y',
@FNumber_Parent2001
)
fetch next from ICItemParent_cursor2001 into @FItemID_Parent2001 ,@FParentID_Parent2001 ,@FNumber_Parent2001
end
close ICItemParent_cursor2001
deallocate ICItemParent_cursor2001;
-- 处理物料的父节点
-- 先处理分类在目标账套上创建对应少的分类 用于Item表的parentID
declare @FItemID_Parent int,@FParentID_Parent int , @FNumber_Parent varchar(250);
--定义游标
declare ICItemParent_cursor cursor for
select FItemID,FParentID ,FNumber from AIS20230113160504.dbo.t_Item where FDeleted=0 and FItemClassID =4 and FParentID
in ( select FItemID from AIS20230113160504.dbo.t_Item where FNumber in('60','61','62','63','64','65','66','67','68','69','70','80','81','88','89') and FDeleted=0 and FItemClassID =4)
and FNumber not in ( select FNumber from summit2023.dbo.t_Item where FItemClassID =4 and FDeleted=0
and FParentID in ( select FItemID from summit2023.dbo.t_Item where FNumber in('60','61','62','63','64','65','66','67','68','69','70','80','81','88','89') and FItemClassID =4 ) )
--打开游标
open ICItemParent_cursor
--取值
fetch next from ICItemParent_cursor into @FItemID_Parent ,@FParentID_Parent ,@FNumber_Parent
while(@@FETCH_STATUS=0)
begin
-- 生成新的FInterID
DECLARE @FItemID_Parent_New INT
exec summit2023.dbo. GetICMaxNum 't_Item', @FItemID_Parent_New output
--select * from [summit2023].[dbo].[t_Item] where FItemID=37045 ; FNumber ='69.306'
INSERT INTO [summit2023].[dbo].[t_Item]
([FItemID]
,[FItemClassID]
,[FExternID]
,[FNumber]
,[FParentID]
,[FLevel]
,[FDetail]
,[FName]
,[FUnUsed]
,[FBrNo]
,[FFullNumber]
,[FDiff]
,[FDeleted]
,[FShortNumber]
,[FFullName]
-- ,[UUID]
,[FGRCommonID]
,[FSystemType]
,[FUseSign]
,[FChkUserID]
,[FAccessory]
,[FGrControl]
,[FHavePicture])
SELECT @FItemID_Parent_New -- [FItemID]
,FItemClassID
,FExternID
,FNumber
,isnull((select FItemIDOut from AIS20230113160504.dbo.v_Item_ParentID_SM where FItemID=@FParentID_Parent),0)--[FParentID]
,[FLevel]
,[FDetail]
,[FName]
,[FUnUsed]
,[FBrNo]
,[FFullNumber]
,[FDiff]
,[FDeleted]
,[FShortNumber]
,[FFullName]
-- ,[UUID]
,[FGRCommonID]
,[FSystemType]
,[FUseSign]
,[FChkUserID]
,[FAccessory]
,[FGrControl]
,[FHavePicture]
FROM [AIS20230113160504].[dbo].[t_Item] where FItemID=@FItemID_Parent
INSERT INTO [summit2023].[dbo].[t_Item_synLog]
([FItemID] --敬邦的ID
,[FOutFItemID]-- 力邦的ID
,[FItemClassID]
,[FParentID] --敬邦的ID
,[FOutParentID] -- 力邦的ID
,[FisParent],
FNumber )
VALUES
(@FItemID_Parent_New,
@FItemID_Parent,
'4',
isnull((select FItemIDOut from AIS20230113160504.dbo.v_Item_ParentID_SM where FItemID=@FParentID_Parent),0),
@FParentID_Parent,
'Y',
@FNumber_Parent
)
fetch next from ICItemParent_cursor into @FItemID_Parent ,@FParentID_Parent ,@FNumber_Parent
end
close ICItemParent_cursor
deallocate ICItemParent_cursor;
--- 同步到敬邦账套
declare @FItemID_old int,@FNumber varchar(80), @FParentID int;
--定义游标
declare ICItem_cursor cursor for
select FItemID,FNumber,FParentID from AIS20230113160504.dbo.t_ICItem where F_108=2 and FNumber not in (select FNumber from summit2023.dbo.t_ICItem)
--打开游标
open ICItem_cursor
--取值
fetch next from ICItem_cursor into @FItemID_old,@FNumber,@FParentID
while(@@FETCH_STATUS=0)
begin
-- 生成新的FInterID
DECLARE @FInterID INT
exec summit2023.dbo. GetICMaxNum 't_Item', @FInterID output
-- select * from summit2023.dbo.ICMaxNum where FTableName='t_Item'
--物料视图
INSERT INTO [summit2023].[dbo].[t_ICItem]
([FItemID]
,[FModel]
,[FName]
,[FHelpCode]
,[FDeleted]
,[FShortNumber]
,[FNumber]
,[FParentID]
,[FBrNo]
,[FTopID]
,[FRP]
,[FOmortize]
,[FOmortizeScale]
,[FForSale]
,[FStaCost]
,[FOrderPrice]
,[FOrderMethod]
,[FPriceFixingType]
,[FSalePriceFixingType]
,[FPerWastage]
,[FARAcctID]
,[FPlanPriceMethod]
,[FPlanClass]
,[FPY]
,[FPinYin]
,[FErpClsID]
,[FUnitID]
,[FUnitGroupID]
,[FDefaultLoc]
,[FSPID]
,[FSource]
,[FQtyDecimal]
,[FLowLimit]
,[FHighLimit]
,[FSecInv]
,[FUseState]
,[FIsEquipment]
,[FEquipmentNum]
,[FIsSparePart]
,[FFullName]
,[FSecUnitID]
,[FSecCoefficient]
,[FSecUnitDecimal]
,[FAlias]
,[FOrderUnitID]
,[FSaleUnitID]
,[FStoreUnitID]
,[FProductUnitID]
,[FApproveNo]
,[FAuxClassID]
,[FTypeID]
,[FPreDeadLine]
,[FSerialClassID]
,[FDefaultReadyLoc]
,[FSPIDReady]
,[FDSManagerID]
,[FForbbitBarcodeEdit]
,[FOrderRector]
,[FPOHghPrcMnyType]
,[FPOHighPrice]
,[FWWHghPrc]
,[FWWHghPrcMnyType]
,[FSOLowPrc]
,[FSOLowPrcMnyType]
,[FIsSale]
,[FProfitRate]
,[FSalePrice]
,[FBatchManager]
,[FISKFPeriod]
,[FKFPeriod]
,[FTrack]
,[FPlanPrice]
,[FPriceDecimal]
,[FAcctID]
,[FSaleAcctID]
,[FCostAcctID]
,[FAPAcctID]
,[FGoodSpec]
,[FCostProject]
,[FIsSnManage]
,[FStockTime]
,[FBookPlan]
,[FBeforeExpire]
,[FTaxRate]
,[FAdminAcctID]
,[FNote]
,[FIsSpecialTax]
,[FSOHighLimit]
,[FSOLowLimit]
,[FOIHighLimit]
,[FOILowLimit]
,[FDaysPer]
,[FLastCheckDate]
,[FCheckCycle]
,[FCheckCycUnit]
,[FStockPrice]
,[FABCCls]
,[FBatchQty]
,[FClass]
,[FCostDiffRate]
,[FDepartment]
,[FSaleTaxAcctID]
,[FCBBmStandardID]
,[FCBRestore]
,[FPickHighLimit]
,[FPickLowLimit]
,[FOnlineShopPName]
,[FOnlineShopPNo]
,[FUnitPackageNumber]
,[FOrderDept]
,[FBarcodeQtyEachTime]
,[FIsSNBanDing]
,[FIsSNRpt]
,[FPlanTrategy]
,[FOrderTrategy]
,[FLeadTime]
,[FFixLeadTime]
,[FTotalTQQ]
,[FQtyMin]
,[FQtyMax]
,[FCUUnitID]
,[FOrderInterVal]
,[FBatchAppendQty]
,[FOrderPoint]
,[FBatFixEconomy]
,[FBatChangeEconomy]
,[FRequirePoint]
,[FPlanPoint]
,[FDefaultRoutingID]
,[FDefaultWorkTypeID]
,[FProductPrincipal]
,[FDailyConsume]
,[FMRPCon]
,[FPlanner]
,[FPutInteger]
,[FInHighLimit]
,[FInLowLimit]
,[FLowestBomCode]
,[FMRPOrder]
,[FIsCharSourceItem]
,[FCharSourceItemID]
,[FPlanMode]
,[FCtrlType]
,[FCtrlStraregy]
,[FContainerName]
,[FKanBanCapability]
,[FIsBackFlush]
,[FBackFlushStockID]
,[FBackFlushSPID]
,[FBatchSplitDays]
,[FBatchSplit]
,[FIsFixedReOrder]
,[FAuxInMrpCal]
,[FProductDesigner]
,[F_101]
,[F_102]
,[F_103]
,[F_104]
,[FChartNumber]
,[FIsKeyItem]
,[FMaund]
,[FGrossWeight]
,[FNetWeight]
,[FCubicMeasure]
,[FLength]
,[FWidth]
,[FHeight]
,[FSize]
,[FVersion]
,[FStartService]
,[FMakeFile]
,[FIsFix]
,[FTtermOfService]
,[FTtermOfUsefulTime]
,[FStandardCost]
,[FStandardManHour]
,[FStdPayRate]
,[FChgFeeRate]
,[FStdFixFeeRate]
,[FOutMachFee]
,[FPieceRate]
,[FStdBatchQty]
,[FPOVAcctID]
,[FPIVAcctID]
,[FMCVAcctID]
,[FPCVAcctID]
,[FSLAcctID]
,[FCAVAcctID]
,[FCBAppendRate]
,[FCBAppendProject]
,[FCostBomID]
,[FCBRouting]
,[FOutMachFeeProject]
,[FInspectionLevel]
,[FInspectionProject]
,[FIsListControl]
,[FProChkMde]
,[FWWChkMde]
,[FSOChkMde]
,[FWthDrwChkMde]
,[FStkChkMde]
,[FOtherChkMde]
,[FStkChkPrd]
,[FStkChkAlrm]
,[FIdentifier]
,[FSampStdCritical]
,[FSampStdStrict]
,[FSampStdSlight]
,[FNameEn]
,[FModelEn]
,[FHSNumber]
,[FFirstUnit]
,[FSecondUnit]
,[FFirstUnitRate]
,[FSecondUnitRate]
,[FIsManage]
,[FPackType]
,[FLenDecimal]
,[FCubageDecimal]
,[FWeightDecimal]
,[FImpostTaxRate]
,[FConsumeTaxRate]
,[FManageType]
,[FExportRate]
,[FBarcode])
SELECT @FInterID
,[FModel]
,[FName]
,[FHelpCode]
,[FDeleted]
,[FShortNumber]
,[FNumber]
,[FParentID]
,[FBrNo]
,[FTopID]
,[FRP]
,[FOmortize]
,[FOmortizeScale]
,[FForSale]
,[FStaCost]
,[FOrderPrice]
,[FOrderMethod]
,[FPriceFixingType]
,[FSalePriceFixingType]
,[FPerWastage]
,[FARAcctID]
,[FPlanPriceMethod]
,[FPlanClass]
,[FPY]
,[FPinYin]
,[FErpClsID]
,( select top 1 summit2023.dbo.t_MeasureUnit_LB_compare.FMeasureUnitID from summit2023.dbo.t_MeasureUnit_LB_compare where FUnitGroupIDlb=[AIS20230113160504].[dbo].[t_ICItem].FUnitGroupID and FMeasureUnitIDlb=[AIS20230113160504].[dbo].[t_ICItem].FUnitID)-- 251 FUnitID]
,( select top 1 summit2023.dbo.t_MeasureUnit_LB_compare.FUnitGroupID from summit2023.dbo.t_MeasureUnit_LB_compare where FUnitGroupIDlb=[AIS20230113160504].[dbo].[t_ICItem].FUnitGroupID and FMeasureUnitIDlb=[AIS20230113160504].[dbo].[t_ICItem].FUnitID) --250 -- [FUnitGroupID]
,[FDefaultLoc]
,[FSPID]
,[FSource]
,[FQtyDecimal]
,[FLowLimit]
,[FHighLimit]
,[FSecInv]
,[FUseState]
,[FIsEquipment]
,[FEquipmentNum]
,[FIsSparePart]
,[FFullName]
,[FSecUnitID]
,[FSecCoefficient]
,[FSecUnitDecimal]
,[FAlias]
,( select top 1 summit2023.dbo.t_MeasureUnit_LB_compare.FMeasureUnitID from summit2023.dbo.t_MeasureUnit_LB_compare where FUnitGroupIDlb=[AIS20230113160504].[dbo].[t_ICItem].FUnitGroupID and FMeasureUnitIDlb=[AIS20230113160504].[dbo].[t_ICItem].FOrderUnitID) --FOrderUnitID
,( select top 1 summit2023.dbo.t_MeasureUnit_LB_compare.FMeasureUnitID from summit2023.dbo.t_MeasureUnit_LB_compare where FUnitGroupIDlb=[AIS20230113160504].[dbo].[t_ICItem].FUnitGroupID and FMeasureUnitIDlb=[AIS20230113160504].[dbo].[t_ICItem].FSaleUnitID) --FSaleUnitID
,( select top 1 summit2023.dbo.t_MeasureUnit_LB_compare.FMeasureUnitID from summit2023.dbo.t_MeasureUnit_LB_compare where FUnitGroupIDlb=[AIS20230113160504].[dbo].[t_ICItem].FUnitGroupID and FMeasureUnitIDlb=[AIS20230113160504].[dbo].[t_ICItem].FStoreUnitID) --FStoreUnitID
,( select top 1 summit2023.dbo.t_MeasureUnit_LB_compare.FMeasureUnitID from summit2023.dbo.t_MeasureUnit_LB_compare where FUnitGroupIDlb=[AIS20230113160504].[dbo].[t_ICItem].FUnitGroupID and FMeasureUnitIDlb=[AIS20230113160504].[dbo].[t_ICItem].FProductUnitID) --FProductUnitID
,[FApproveNo]
,[FAuxClassID]
,[FTypeID]
,[FPreDeadLine]
,[FSerialClassID]
,[FDefaultReadyLoc]
,[FSPIDReady]
,[FDSManagerID]
,[FForbbitBarcodeEdit]
,[FOrderRector]
,[FPOHghPrcMnyType]
,[FPOHighPrice]
,[FWWHghPrc]
,[FWWHghPrcMnyType]
,[FSOLowPrc]
,[FSOLowPrcMnyType]
,[FIsSale]
,[FProfitRate]
,[FSalePrice]
,[FBatchManager]
,[FISKFPeriod]
,[FKFPeriod]
,[FTrack]
,[FPlanPrice]
,[FPriceDecimal]
,1050--[FAcctID]
,1149--[FSaleAcctID]
,1166--[FCostAcctID]
,[FAPAcctID]
,[FGoodSpec]
,[FCostProject]
,[FIsSnManage]
,[FStockTime]
,[FBookPlan]
,[FBeforeExpire]
,[FTaxRate]
,[FAdminAcctID]
,[FNote]
,[FIsSpecialTax]
,[FSOHighLimit]
,[FSOLowLimit]
,[FOIHighLimit]
,[FOILowLimit]
,[FDaysPer]
,[FLastCheckDate]
,[FCheckCycle]
,[FCheckCycUnit]
,[FStockPrice]
,[FABCCls]
,[FBatchQty]
,[FClass]
,[FCostDiffRate]
,[FDepartment]
,[FSaleTaxAcctID]
,[FCBBmStandardID]
,[FCBRestore]
,[FPickHighLimit]
,[FPickLowLimit]
,[FOnlineShopPName]
,[FOnlineShopPNo]
,[FUnitPackageNumber]
,[FOrderDept]
,[FBarcodeQtyEachTime]
,[FIsSNBanDing]
,[FIsSNRpt]
,[FPlanTrategy]
,[FOrderTrategy]
,[FLeadTime]
,[FFixLeadTime]
,[FTotalTQQ]
,[FQtyMin]
,[FQtyMax]
,[FCUUnitID]
,[FOrderInterVal]
,[FBatchAppendQty]
,[FOrderPoint]
,[FBatFixEconomy]
,[FBatChangeEconomy]
,[FRequirePoint]
,[FPlanPoint]
,[FDefaultRoutingID]
,[FDefaultWorkTypeID]
,[FProductPrincipal]
,[FDailyConsume]
,[FMRPCon]
,[FPlanner]
,[FPutInteger]
,[FInHighLimit]
,[FInLowLimit]
,[FLowestBomCode]
,[FMRPOrder]
,[FIsCharSourceItem]
,[FCharSourceItemID]
,[FPlanMode]
,[FCtrlType]
,[FCtrlStraregy]
,[FContainerName]
,[FKanBanCapability]
,[FIsBackFlush]
,[FBackFlushStockID]
,[FBackFlushSPID]
,[FBatchSplitDays]
,[FBatchSplit]
,[FIsFixedReOrder]
,[FAuxInMrpCal]
,[FProductDesigner]
,[F_104]
,[F_105]
,[F_106]
,[F_107]
,[FChartNumber]
,[FIsKeyItem]
,[FMaund]
,[FGrossWeight]
,[FNetWeight]
,[FCubicMeasure]
,[FLength]
,[FWidth]
,[FHeight]
,[FSize]
,[FVersion]
,[FStartService]
,[FMakeFile]
,[FIsFix]
,[FTtermOfService]
,[FTtermOfUsefulTime]
,[FStandardCost]
,[FStandardManHour]
,[FStdPayRate]
,[FChgFeeRate]
,[FStdFixFeeRate]
,[FOutMachFee]
,[FPieceRate]
,[FStdBatchQty]
,[FPOVAcctID]
,[FPIVAcctID]
,[FMCVAcctID]
,[FPCVAcctID]
,[FSLAcctID]
,[FCAVAcctID]
,[FCBAppendRate]
,[FCBAppendProject]
,[FCostBomID]
,[FCBRouting]
,[FOutMachFeeProject]
,[FInspectionLevel]
,[FInspectionProject]
,[FIsListControl]
,[FProChkMde]
,[FWWChkMde]
,[FSOChkMde]
,[FWthDrwChkMde]
,[FStkChkMde]
,[FOtherChkMde]
,[FStkChkPrd]
,[FStkChkAlrm]
,[FIdentifier]
,[FSampStdCritical]
,[FSampStdStrict]
,[FSampStdSlight]
,[FNameEn]
,[FModelEn]
,[FHSNumber]
,[FFirstUnit]
,[FSecondUnit]
,[FFirstUnitRate]
,[FSecondUnitRate]
,[FIsManage]
,[FPackType]
,[FLenDecimal]
,[FCubageDecimal]
,[FWeightDecimal]
,[FImpostTaxRate]
,[FConsumeTaxRate]
,[FManageType]
,[FExportRate]
,[FBarcode]
FROM [AIS20230113160504].[dbo].[t_ICItem] where FItemID= @FItemID_old
--item表处理物料
INSERT INTO [summit2023].[dbo].[t_Item]
([FItemID]
,[FItemClassID]
,[FExternID]
,[FNumber]
,[FParentID]
,[FLevel]
,[FDetail]
,[FName]
,[FUnUsed]
,[FBrNo]
,[FFullNumber]
,[FDiff]
,[FDeleted]
,[FShortNumber]
,[FFullName]
,[UUID]
,[FGRCommonID]
,[FSystemType]
,[FUseSign]
,[FChkUserID]
,[FAccessory]
,[FGrControl]
,[FHavePicture])
SELECT @FInterID,
[FItemClassID]
,[FExternID]
,[FNumber]
,isnull((select FItemIDOut from AIS20230113160504.dbo.v_Item_ParentID_SM where FItemID=FParentID),0)--[FParentID]
,[FLevel]
,[FDetail]
,[FName]
,[FUnUsed]
,[FBrNo]
,[FFullNumber]
,[FDiff]
,[FDeleted]
,[FShortNumber]
,[FFullName]
,[UUID]
,[FGRCommonID]
,[FSystemType]
,[FUseSign]
,[FChkUserID]
,[FAccessory]
,[FGrControl]
,[FHavePicture]
from [AIS20230113160504].[dbo].[t_Item] where FItemID=@FItemID_old
--物料图片表
INSERT INTO [summit2023].[dbo].[t_Accessory]
([FTypeID]
,[FItemID]
,[FDesc]
,[FFileName]
,[FFile]
,[FFileSize]
,[FUploader]
,[FUploadTime]
,[FChecker]
,[FIsPIC]
,[FData]
,[FVersion]
,[FSaveMode]
,[FPage]
,[FEntryID]
,[FShowFileName])
SELECT [FTypeID]
,@FInterID--[FItemID]
,[FDesc]
,[FFileName]
,[FFile]
,[FFileSize]
,[FUploader]
,[FUploadTime]
,[FChecker]
,[FIsPIC]
,[FData]
,[FVersion]
,[FSaveMode]
,[FPage]
,[FEntryID]
,[FShowFileName] FROM [AIS20230113160504].[dbo].t_Accessory where FItemID= @FItemID_old
--物料
INSERT INTO [summit2023].[dbo].[t_Item_synLog]
([FItemID] --敬邦的ID
,[FOutFItemID]-- 力邦的ID
,[FItemClassID]
,[FParentID] --敬邦的ID
,[FOutParentID] -- 力邦的ID
,[FisParent],
FNumber )
VALUES
(@FInterID,
@FItemID_old,
'4',
isnull((select FItemIDOut from AIS20230113160504.dbo.v_Item_ParentID_SM where FItemID=@FParentID),0),
@FParentID,
'N',
@FNumber
)
DECLARE @FInterID2001 INT
exec summit2023.dbo. GetICMaxNum 't_Item', @FInterID2001 output
--item表处理成本对象
INSERT INTO [summit2023].[dbo].[t_Item]
([FItemID]
,[FItemClassID]
,[FExternID]
,[FNumber]
,[FParentID]
,[FLevel]
,[FDetail]
,[FName]
,[FUnUsed]
,[FBrNo]
,[FFullNumber]
,[FDiff]
,[FDeleted]
,[FShortNumber]
,[FFullName]
,[UUID]
,[FGRCommonID]
,[FSystemType]
,[FUseSign]
,[FChkUserID]
,[FAccessory]
,[FGrControl]
,[FHavePicture])
SELECT @FInterID2001,
[FItemClassID]
,[FExternID]
,[FNumber]
,isnull((select FItemIDOut from AIS20230113160504.dbo.v_Item_ParentID_SM2001 where FItemID=FParentID),0)--[FParentID]
,[FLevel]
,[FDetail]
,[FName]
,[FUnUsed]
,[FBrNo]
,[FFullNumber]
,[FDiff]
,[FDeleted]
,[FShortNumber]
,[FFullName]
,[UUID]
,[FGRCommonID]
,[FSystemType]
,[FUseSign]
,[FChkUserID]
,[FAccessory]
,[FGrControl]
,[FHavePicture]
from [AIS20230113160504].[dbo].[t_Item] where FNumber=@FNumber and FItemID<>@FItemID_old
--处理成本对象
INSERT INTO [summit2023].[dbo].[t_Item_synLog]
([FItemID] --敬邦的ID
,[FOutFItemID]-- 力邦的ID
,[FItemClassID]
,[FParentID] --敬邦的ID
,[FOutParentID] -- 力邦的ID
,[FisParent]
,FNumber)
VALUES
(@FInterID2001,
(select top 1 FItemID from [AIS20230113160504].[dbo].[t_Item] where FNumber=@FNumber and FItemID<>@FItemID_old),
'2001',
'',
'',
'N',
@FNumber
)
fetch next from ICItem_cursor into @FItemID_old,@FNumber,@FParentID
end
close ICItem_cursor
deallocate ICItem_cursor;
解决同步之后，零星的修改同步就得增加触发器了：改名称 改规格 改单位，改图片
alter TRIGGER [dbo].[tr_t_ICItemBase_update]
ON [dbo].[t_ICItemBase]
AFTER Update
AS
BEGIN
DECLARE @FItemID_LB INT,@FItemID_JB INT,@FItemID_SM INT,@msg varchar(1000),@FUnitGroupID_JB int , @FUnitID_JB int ,@FUnitGroupID_SM int , @FUnitID_SM int
select @FItemID_LB=inserted.FItemID from inserted
select @FItemID_JB=FItemID from [jb2023].[dbo].[t_Item_synLog] where FOutFItemID=@FItemID_LB and FItemClassID=4
select @FItemID_SM=FItemID from [summit2023].[dbo].[t_Item_synLog] where FOutFItemID=@FItemID_LB and FItemClassID=4
select @FUnitGroupID_JB=a.FUnitGroupID,@FUnitID_JB=a.FMeasureUnitID from [jb2023].[dbo].t_MeasureUnit_LB_compare a join inserted b on a.FUnitGroupIDlb=b.FUnitGroupID and a.FMeasureUnitIDlb=b.FUnitID
select @FUnitGroupID_SM=a.FUnitGroupID,@FUnitID_SM=a.FMeasureUnitID from [summit2023].[dbo].t_MeasureUnit_LB_compare a join inserted b on a.FUnitGroupIDlb=b.FUnitGroupID and a.FMeasureUnitIDlb=b.FUnitID
if @FItemID_JB is not NULL
begin
-- 修改敬邦账套的物料名称和规格
UPDATE [jb2023].[dbo].[t_ICItemCore]
SET
FModel=b.FModel
,FName =b.FName
from [AIS20230113160504].[dbo].[t_ICItemCore] b, [jb2023].[dbo].[t_ICItemCore] n
WHERE n.FItemID=@FItemID_JB and b.FItemID=@FItemID_LB
UPDATE [jb2023].[dbo].[t_Item]
SET
FName=b.FName
from [AIS20230113160504].[dbo].[t_Item] b, [jb2023].[dbo].[t_Item] n
WHERE n.FItemID=@FItemID_JB and b.FItemID=@FItemID_LB
if ( @FUnitGroupID_JB is not NULL and @FUnitID_JB is not NULL)
begin
--修改敬邦账套的物料 单位
UPDATE [jb2023].[dbo].[t_ICItemBase]
SET
[FUnitID] = @FUnitID_JB
,[FUnitGroupID] = @FUnitGroupID_JB
,[FOrderUnitID] = @FUnitID_JB
,[FSaleUnitID] = @FUnitID_JB
,[FStoreUnitID] = @FUnitID_JB
,[FProductUnitID] = @FUnitID_JB
,[FQtyDecimal] = b.FQtyDecimal
,[FLowLimit] = b.FLowLimit
,[FHighLimit] =b.FHighLimit
,[FSecInv] = b.FSecInv
,[FUseState] = b.FUseState
,[FFullName] = b.FFullName
from [AIS20230113160504].[dbo].[t_ICItemBase] b, [jb2023].[dbo].[t_ICItemBase] n
WHERE n.FItemID=@FItemID_JB and b.FItemID=@FItemID_LB
end
end
if @FItemID_SM is not NULL
begin
-- 修改敬邦账套的物料名称和规格
UPDATE [summit2023].[dbo].[t_ICItemCore]
SET
FModel=b.FModel
,FName =b.FName
from [AIS20230113160504].[dbo].[t_ICItemCore] b, [summit2023].[dbo].[t_ICItemCore] n
WHERE n.FItemID=@FItemID_SM and b.FItemID=@FItemID_LB
UPDATE [summit2023].[dbo].[t_Item]
SET
FName=b.FName
from [AIS20230113160504].[dbo].[t_Item] b, [summit2023].[dbo].[t_Item] n
WHERE n.FItemID=@FItemID_SM and b.FItemID=@FItemID_LB
if ( @FUnitGroupID_JB is not NULL and @FUnitID_JB is not NULL)
begin
--修改敬邦账套的物料 单位
UPDATE [summit2023].[dbo].[t_ICItemBase]
SET
[FUnitID] = @FUnitID_JB
,[FUnitGroupID] = @FUnitGroupID_JB
,[FOrderUnitID] = @FUnitID_JB
,[FSaleUnitID] = @FUnitID_JB
,[FStoreUnitID] = @FUnitID_JB
,[FProductUnitID] = @FUnitID_JB
,[FQtyDecimal] = b.FQtyDecimal
,[FLowLimit] = b.FLowLimit
,[FHighLimit] =b.FHighLimit
,[FSecInv] = b.FSecInv
,[FUseState] = b.FUseState
,[FFullName] = b.FFullName
from [AIS20230113160504].[dbo].[t_ICItemBase] b, [summit2023].[dbo].[t_ICItemBase] n
WHERE n.FItemID=@FItemID_SM and b.FItemID=@FItemID_LB
end
end
END
GO
CREATE TRIGGER [dbo].[tr_t_Accessory_insert]
ON [dbo].[t_Accessory]
AFTER INSERT ,Update
AS
BEGIN
DECLARE @FItemID_LB INT,@FItemID_JB INT,@FItemID_SM INT,@msg varchar(1000)
select @FItemID_LB=inserted.FItemID from inserted
select @FItemID_JB=FItemID from [jb2023].[dbo].[t_Item_synLog] where FOutFItemID=@FItemID_LB and FItemClassID=4
select @FItemID_SM=FItemID from [summit2023].[dbo].[t_Item_synLog] where FOutFItemID=@FItemID_LB and FItemClassID=4
if @FItemID_JB is not NULL
begin
--删除敬邦账套的图片
delete [jb2023].[dbo].[t_Accessory] where FItemID= @FItemID_JB
--插入敬邦物料图片表
INSERT INTO [jb2023].[dbo].[t_Accessory]
([FTypeID]
,[FItemID]
,[FDesc]
,[FFileName]
,[FFile]
,[FFileSize]
,[FUploader]
,[FUploadTime]
,[FChecker]
,[FIsPIC]
,[FData]
,[FVersion]
,[FSaveMode]
,[FPage]
,[FEntryID]
,[FShowFileName])
SELECT [FTypeID]
,@FItemID_JB--[FItemID]
,[FDesc]
,[FFileName]
,[FFile]
,[FFileSize]
,[FUploader]
,[FUploadTime]
,[FChecker]
,[FIsPIC]
,[FData]
,[FVersion]
,[FSaveMode]
,[FPage]
,[FEntryID]
,[FShowFileName] FROM [AIS20230113160504].[dbo].t_Accessory where FItemID= @FItemID_LB
end
if @FItemID_SM is not NULL
begin
--删除时美特账套的图片
delete [summit2023].[dbo].[t_Accessory] where FItemID= @FItemID_SM
--插入时美特物料图片表
INSERT INTO [summit2023].[dbo].[t_Accessory]
([FTypeID]
,[FItemID]
,[FDesc]
,[FFileName]
,[FFile]
,[FFileSize]
,[FUploader]
,[FUploadTime]
,[FChecker]
,[FIsPIC]
,[FData]
,[FVersion]
,[FSaveMode]
,[FPage]
,[FEntryID]
,[FShowFileName])
SELECT [FTypeID]
,@FItemID_SM--[FItemID]
,[FDesc]
,[FFileName]
,[FFile]
,[FFileSize]
,[FUploader]
,[FUploadTime]
,[FChecker]
,[FIsPIC]
,[FData]
,[FVersion]
,[FSaveMode]
,[FPage]
,[FEntryID]
,[FShowFileName] FROM [AIS20230113160504].[dbo].t_Accessory where FItemID= @FItemID_LB
end
END
GO
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
