操作前必读
以下为wise系统各主要模块，在初始化时从旧系统引入数据覆盖到新系统的sql脚本代码。因以下操作相当危险，使用时请务必做到：
a. 必须由系统管理员或授权人员进行操作, 操作前请先备份好目标数据库。
b. 以下为sql脚本指令，在运行时请选择在目标系统的数据库（如AISUDB)下运行.
c. 以下的指令默认旧系统数据库与新系统数据库在同一个服务器上（所以对旧系统表的访问类似这样的形式：WiseNew.dbo.HR_Employee）。如果不在同一服务器上，需要先创建链接服务器并修改脚本中对旧系统表的访问形式（类似如hrlink.WiseNew.dbo.HR_Employee).
d. 如果某个时候只需要引特定模块的数据，比如只想重新引工程定额的数据，则只需要找到并复制工程定额部分的代码在sql server management studio中运行，并在最后运行刷新内码的存储过程Exec SE_RefreshBillID
-- =====================================
--汇入人事资料
-- =====================================
alter table hr_employee drop HR_Employee_DepartID_fk
delete from HR_Employee
Insert HR_Employee
(
FItemID,FJobNo, FCardNo, FEmpName, FSex,FBirthDate, FCitizenNo, FBirthPlace, FDepartID, FParentID,
FLevel,FJobs, FDegree, FProfessional, FGraduated,
FStartDate,FRevBillDate,ApplyDate,FEndDate,FAwayReason, FStatus, FForeman,
FRecruitmentChannel, FIntroducer, FContactor, FCategoryCode, FPayApproacth, FWorkLocation, FMaterialFor,
FContractCategory,FContractEndDate,FNote,FDeleted, CreateDate, CreateMan, MemberID, TabMan, TabDate
)
Select Oid,Job_Number, CardNo, Name, case when Sex = 1 then 'F' else 'M' end,Birth_Date, Citizen_CertificateParticulars, Birth_Place, Department_id, Parent_ID,
Level,Jobs, Degree, Professinal, Graduated,
Start_Date,RevShipperOfResignationDate,WhenProposeDate,RevShipperOfResignationDate,ResignationReason, Status, Foreman,
RecruitmentChannel, Introducer, Emergency_RelationInfo, Category_Code, Pay_Approach, WorkLocation, MaterialFor,
ContractCategory,Contact_MaturityDate,Note,Case When Active = 1 then 0 else 1 End, CreateOn, CreateBy, 'Admin','Admin', GETDATE()
from WiseNew.dbo.HR_Employee
where not Job_Number is Null
and len(Job_Number) > 3
and GCRecord is null
--工号重复的不汇入
and Job_Number not in (
select Job_Number
from WiseNew.dbo.HR_Employee
group by Job_Number
having count(job_Number) > 1
)
--工号重复的要汇入一笔(即不能一律不汇入）
Insert HR_Employee
(
FItemID,FJobNo, FCardNo, FEmpName, FSex,FBirthDate, FCitizenNo, FBirthPlace, FDepartID, FParentID,
FLevel,FJobs, FDegree, FProfessional, FGraduated,
FStartDate,FRevBillDate,ApplyDate,FEndDate,FAwayReason, FStatus, FForeman,
FRecruitmentChannel, FIntroducer, FContactor, FCategoryCode, FPayApproacth, FWorkLocation, FMaterialFor,
FContractCategory,FContractEndDate,FNote,FDeleted, CreateDate, CreateMan, MemberID, TabMan, TabDate
)
Select Oid,Job_Number, CardNo, Name, case when Sex = 1 then 'M' else 'F' end,Birth_Date, Citizen_CertificateParticulars, Birth_Place, Department_id, Parent_ID,
Level,Jobs, Degree, Professinal, Graduated,
Start_Date,RevShipperOfResignationDate,WhenProposeDate,RevShipperOfResignationDate,ResignationReason, Status, Foreman,
RecruitmentChannel, Introducer, Emergency_RelationInfo, Category_Code, Pay_Approach, WorkLocation, MaterialFor,
ContractCategory,Contact_MaturityDate,Note,Case When Active = 1 then 0 else 1 End, CreateOn, CreateBy, 'Admin','Admin', GETDATE()
from WiseNew.dbo.HR_Employee
where not Job_Number is Null
and Job_Number in (
select Job_Number
from WiseNew.dbo.HR_Employee
group by Job_Number
having count(job_Number) > 1
)
and GCRecord is null
and Active = 1
and len(Job_Number) > 3
order by Job_Number
--将部门内码改为新系统的内码
;with tab as
(
Select a.oid, a.name, b.fitemid, b.DepartName
from wiseNew.dbo.hr_department a, Department b
where a.Name collate Database_Default = b.DepartName
)
update a set
FDepartID = b.FItemID
from HR_Employee a, tab b
where a.FDepartID = b.oid
-- ===================================================
-- 复制调动记录
-- ===================================================
Truncate Table HR_EmployeeTransfer
Insert HR_EmployeeTransfer(FItemID, RowID, TransferInfo, TransferReason, TransferDate, FNote)
Select EmployeeID, 1 as RowID, TransferInfo, TransferReason, TransferDate, Note
from WiseNew.dbo.HR_EmployeeTransfer
Where EmployeeID in (Select FItemID from HR_Employee)
-- ==================================================
-- 雜料管理
-- ==================================================
truncate table LM_LittleMaterialMange
Insert LM_LittleMaterialMange(FID, IoFlag, FNumber, MatNo, Quantity, Purpose, EmployeeID, TransactorID, FDate,
FNote, MemberID, TabMan, TabDate, TabMonth)
Select OID, case when State='ST-21' then 0 else 1 end as IoFlag, ShipNO, Material, Qty, Usage, Employee, Operator, OperateDate,
Note, 'Admin', 'Admin', CreateOn, convert(varchar(6), CreateOn,112)
from WiseNew.dbo.LM_LittleMaterialMange
-- ===================================================
-- 培训记录
-- ===================================================
truncate table HR_TrainingRecord
Insert HR_TrainingRecord
(
EmployeeID,SignTime,ExaminationResult,FCategory,FCourse,FAddress,FAssessmentMethod,
Trainer,TrainDate,FHide,FNote,MemberID,TabMan,TabDate
)
Select c.FItemID,SignTime,ExaminationResult,Category,Course,[Address],AssessmentMethod,
Trainer,TrainDate,0,a.Note,a.CreateBy,a.CreateBy,a.CreateOn
from WiseNew.dbo.HR_TrainingRecord a left join WiseNew.dbo.HR_Employee b
on a.EmployeeID = b.Oid
left join HR_Employee c
on c.FJobNo collate Database_Default = b.Job_Number
where not c.FItemID is null
-- ========================================
--汇入物料（销售类别、仓别等记得更新一下）
-- ========================================
truncate table MP_Material
insert MP_Material(
FItemID,
FNumber,
FName,
MaterialType,
UnitName,
StoreSafeQty,
StandardQty,
SaveQty,
BoardBoxQty,
PiecePer,
StoreCode,
FNote
)
Select ROW_NUMBER() over (order by material) as FID,
Material,
Description,
MaterialType,
Unit,
LowTotal,
HighTotal,
Standand,
BoardBoxQty,
PiecePer,
StoreCode,
Note
from WiseNew.dbo.MP_Material
where not Description is null and not Material is null
-- 更新下一个内码
Update CodeRule set FNextID = (Select Max(FItemID) + 1 from MP_Material) Where TabName = 'MP_Material'
-- ================================
--工程定额
-- ================================
truncate Table ENG_FixedWorkHourList
delete from ENG_FixedWorkHour
--匯入工程定額主表 (要先匯入物料)
Insert ENG_FixedWorkHour(FInterID, FBillNo, FDate, FItemID, FVersion,BomBaseQty,
TransactStatus, FNote, MemberID, TabDate, AuditMemberID, AuditDate)
Select Oid, 'EQ'+ Right('000000'+cast(Oid as Varchar),6) as FBillNo,
case when EngDate is null then CreateOn else EngDate end as EndDate, b.FItemID, [version], BomBaseQty,
case when CheckStatus = 'E1' then 1 --初稿
when CheckStatus = 'E2' then 2 --工程審核
when CheckStatus = 'E3' then 3 --生產審核
when CheckStatus = 'E4' then 4 --歷史定額
End as TransactStatus,Note,CreateBy, CreateOn, EngCheck, CreateOn
from WiseNew.dbo.ENG_FixedWorkHourHeader a left Join MP_Material b
on a.Material collate Database_Default = b.FNumber
--匯入工程客額子表
Declare @FID int
Declare mycursor cursor for Select FInterID from ENG_FixedWorkHour where FInterID!= 0
open mycursor
fetch mycursor into @FID
while @@FETCH_STATUS = 0
begin
Insert ENG_FixedWorkHourList(FInterID, RowID,FDetailID, FMachine, FMold,
FMQty,
FEQty,
Efficiency, ProcName, UseQty, BomWorkHour,
ProdEfficiency, LittleModelMachine,LittleModelMachine2,LittleModelMachine3,ProcUnit,
FixedWorkHour,
FNote)
--(老系统定额人数与定额数量数据错乱，故有关栏位交换了一个位置
Select FXHID, ROW_NUMBER() over (order by oid) as RowID,
Cast(FXHID as Varchar) + '-'+Cast(ROW_NUMBER() over (order by oid) as varchar),
Machine, Mold,
FEQty,
FMQty,
case when FEQty!=0 then FMQty/FEQty else 0 End as Efficiency,
ProcessDescription, UseQty, BomWorkHour,
ProdEfficiency,LittleModelMachine,LittleModelMachine2, LittleModelMachine3, ProcUnit,
Case when FEQty!= 0 and FMQty!= 0 then UseQty/(FMQty / FEQty) else 0 End, Note
from WiseNew.dbo.ENG_FixedWorkHourDetail
Where FXHID = @FID
order by Oid
fetch mycursor into @FID
end
close mycursor
deallocate mycursor
--進行相關計算
--Nzp折扣
Update a set
BOMDiscount = case When isNull(a.FitemID,0) = 0 or b.FName is Null then 1
else Case when b.FName like '%(p)' then 0.75 else 0.85 End End
from ENG_FixedWorkHour a left join MP_Material b
on a.FItemID = b.FItemID
--產品效率
Update a set
ProdEfficiency = case when FixedWorkHour = 0 then 0 else 100* a.BomWorkHour * b.BOMDiscount / a.FixedWorkHour end
from ENG_FixedWorkHourList a, ENG_FixedWorkHour b
where a.FInterID = b.FInterID
--注意：記得更新單據編碼規則中的下一編號
Update CodeRule set FNextID = (select max(finterid)+1 from ENG_FixedWorkHour) where TabName = 'ENG_FixedWorkHour'
-- ================================
--工程机器
-- ================================
truncate table Eng_Machine
--汇入工程机器
Insert Eng_Machine(FID, FBillNo, FNumber, TypeName, ArtNo, StandardCapacity, MoldCavity, FNote)
Select oid, N'EM'+right( '000' + Cast( ROW_NUMBER() over (Order by MachineType) as varchar),4) as fFbillNo,
MachineType, MachineCategory, SchickMachineType, StandardMachineNumber, MoldCavity, note
from wisenew.dbo.Eng_Machine
--另，汇入完成后要更新CodeRule中的FNextID
-- ===============================
--生产派料
-- ===============================
truncate table MP_MRFList
delete from MP_MRF
;with tab as
(
Select distinct OrderNo,Material
from WiseNew.dbo.MP_MRF
Where CreateOn > '2017-12-31'
)
Insert MP_MRF(FInterID, OrderNo, ProdNo)
Select ROW_NUMBER() over (order by OrderNo) + 5 as FInterID, a.*
from tab a
Update a set
Plnt = b.Plnt,
ProdName = b.Description,
FDate = b.CreateOn,
OrderQty = b.Order_Qty,
UnitName = b.Unit,
StartDate = b.Start_Date,
EndDate = b.Finish_Date,
MemberID = b.CreateBy,
TabMan = b.CreateBy,
TabDate = b.CreateOn
from MP_MRF a, WiseNew.dbo.MP_MRF b
where a.OrderNo collate Database_Default = b.OrderNo
--更新下一个内码
Update CodeRule set FNextID = (Select max(FInterID) + 1 from MP_MRF) Where TabName = 'MP_MRF'
--select * from MP_MRF
--插入子表数据（加行号）
Declare @OrderNo Varchar(30)
Declare mycur cursor for
Select distinct OrderNo from WiseNew.dbo.MP_MRF Where CreateOn > '2017-12-31'
open mycur
fetch mycur into @OrderNo
while @@FETCH_STATUS = 0
begin
Insert MP_MRFList
(FInterID, RowID, BoxQty, MatNo ,MatName ,Quantity,LossRate,UnitCode,FWorkLocation)
Select b.FInterID, ROW_NUMBER() over (order by COMPONENT) as rowID,
QtyOfOuter, COMPONENT ,COMPONENT_DESCRIPTION ,COMPONENT_QTY,COMPONENT_SCRAP,COMPONT_UNIT,Location
from WiseNew.dbo.MP_MRF a, MP_MRF b
Where a.OrderNO collate Database_Default = b.OrderNo and a.OrderNO collate Database_Default = @OrderNo
fetch mycur into @OrderNo
end
close mycur
deallocate mycur
-- ===========================
--附属比例(計劃周）
-- ===========================
truncate Table MP_SupportProportion
Insert MP_SupportProportion(FID, FNumber, ProdDate, PlanWeek, PeopleQty, OperatorQty, NewProportion,
MaterialGroup, RepairGroup, MachineGroup, IsActive, MemberID, TabMan, TabDate)
Select OID, right('000000' + cast(oid as varchar), 6), MakeDate, [Week], Sticker, OperatorQty, NewProportion,
MaterialGroup, RepairMachine, Leader, isnull(Ok,0), CreateBy, CreateBy, CreateOn
From WiseNew.dbo.MP_SupportProportion
where not [Week] is null
-- ====================================
-- 生产计划
-- ====================================
truncate table MP_ProductionPlan
Insert MP_ProductionPlan(OrderNo,MatNo,OrderQty, PlanWeek, FinishDate, InQty, OweInQty)
select OrderNo,Material,OrderQty, WeekOfPlan, FinishDate, FinishQty, OweQty
from WiseNew.dbo.mp_productionplan
where FinishDate> '2018-12-31'
-- ====================================
-- 工厂日历：假期设定
-- ====================================
truncate table Res_Holidays
insert Res_Holidays(fdate,ftypeid,FDeleted,FNote)
select HolidayDate,HolidayCategory,Case When IsActive = 1 then 0 else 1 End,Note
from WiseNew.dbo.Res_Holidays
where not HolidayDate is null and HolidayDate not in (Select HolidayDate from Res_Holidays)
-- ====================================
-- 资产登记
-- ====================================
truncate table FA_FixedAsset
--从旧系统引入数据
Insert FA_FixedAsset(
FID, FNumber,FName,Seq, DepartName, FCategoryName, Model, FDate, SavePosi
,FQty, UnitName, FUserName, BodyCode, AssetTracking, FChangeMethod,FChangeDate
,FNote,CreateMan, CreateDate, MemberID, TabMan, TabDate)
Select ROW_NUMBER() over (order by fa_code), FA_Code,FA_Name,Seq, Department, Category, Specifications, RegistrationDate, [Address]
,Qty, Unit, [User], BodyCode,AssetTracking,Change,ChangeDate
,Note,CreateBy, CreateOn, CreateBy, WriteBy, WriteOn
from WiseNew.dbo.FA_FixedAsset
--编号有重复的不能汇入，通知用户自己处理
where FA_Code not in(
select FA_Code
from WiseNew.dbo.FA_FixedAsset
group by FA_Code
having count(fa_code) > 1
)
--同步内码
update CodeRule set FNextID=(select max(fid) from FA_FixedAsset) + 1 where TabName = 'FA_FixedAsset'
-- ===================================
-- 领用物料
-- ====================================
truncate table HR_Employee_RevMaterial
Insert HR_Employee_RevMaterial(FItemID, RowID, FCategoryCode, FDescription,FRevDate, FNote, MemberID)
Select a.Employee_Id, 0, Item, Description, RevDate, a.Note, a.CreateBy
from WiseNew.dbo.HR_Employee_RevMaterial a left join WiseNew.dbo.HR_Employee b
on a.Employee_Id = b.Oid
left join HR_Employee c
on c.FJobNo collate Database_Default = b.Job_Number
where not c.FItemID is null and not RevDate is null
and a.Employee_Id in (select FItemID from HR_Employee)
-- =======================================
-- 请假记录
-- =======================================
truncate table HR_EmployeeHoliday
Insert HR_EmployeeHoliday(FBillNo, EmployeeID, FVersion, FCategoryCode, FStartDate,
FEndDate, FReason,FNote, IsActive)
Select HolidayNO, c.FItemID, EmployeeVersion, Category, StartDate,
EndDate, Reason,a.Note, IsActive
from WiseNew.dbo.HR_EmployeeHoliday a left join WiseNew.dbo.HR_Employee b
on a.EmployeeEmployeeID = b.Oid
left join HR_Employee c
on b.Job_Number collate Database_Default= c.FJobNo
Where not c.FItemID is null
and CONVERT(varchar(6), startdate, 112) >= '201802'
-- ==================================
--工装领用
-- ===================================
truncate table HR_WorkClothesList
delete from HR_WorkClothes
--汇入工装主表
Insert HR_WorkClothes(FID, FBillNo, IsBlueWord, FNote, MemberID,TabMan, TabDate)
select oid,'WSWL' + Right( '0000' + Cast( ROW_NUMBER() over (order by oid) as varchar),4) as FBillNo,
case when OperateCategory = 'ST-21' then 1 else 0 end as isblueword,Note, CreateBy,Createby, CreateOn
from WiseNew.dbo.HR_WorkClothesHeader
--不能汇入重复的
--where ShipNo not in(
--select ShipNo
--from WiseNew.dbo.HR_WorkClothesHeader
--group by shipNo
--having count(shipno) > 1
--)
--汇入工装子表
declare @i int
declare mycursor cursor for
select FID from HR_WorkClothes
set @i = 1
open mycursor
fetch mycursor into @i
while @@FETCH_STATUS = 0
Begin
Insert HR_WorkClothesList(FID,RowID, FNumber,FQty,FJobNo,FDate,FExpenseState,FNote,IsOK)
Select a.Header, ROW_NUMBER() over (order by a.Oid) as RowID,
b.Material, a.IssueQty * (-1) as FQty, c.FJobNo, a.RecipientDate, a.PaymentState, a.Note, a.Ok
from WiseNew.dbo.HR_WorkClothesDetail a left join WiseNew.dbo.HR_WorkClothesLedger b
on a.WorkClothes = b.OID
left join HR_Employee c
on a.Recipients = c.FItemID
where not b.Material is null and a.Header = @i and not c.FJobNo is null
fetch mycursor into @i
end
close mycursor
deallocate mycursor
--汇入工装台账
delete from HR_WorkClothesLedger
declare @FID int
Select @FID = FNextID from CodeRule Where TabName = 'HR_WorkClothesLedger'
--;with tab as
--(
--select Material, count(*) as Qty
--from WiseNew.dbo.HR_WorkClothesLedger
--Group by Material
--) ,
--tab2 as
--(
--Select oid from WiseNew.dbo.HR_WorkClothesLedger
--Where Material in (Select Material from tab where Qty > 1) and not GCRecord is null
--)
Insert HR_WorkClothesLedger(fid, fnumber, fcategorycode, saveposi, fstartDate, startQty, fnote)
select ROW_NUMBER() over (order by Material) + @FID - 1 as FID,
Material, ClothesCategory, Location, BeginDate,BeginQty,Note
from WiseNew.dbo.HR_WorkClothesLedger a
Where Oid = (select min(oid) from WiseNew.dbo.HR_WorkClothesLedger where Material = a.Material)
Update CodeRule set FNextID = (Select IsNull(Max(FID),0) + 1 from HR_WorkClothesLedger) Where TabName = 'HR_WorkClothesLedger'
-- 工衣領用日期所在的周期
Update HR_WorkClothesList set
FCycle = Case When DATEDIFF(day,b.FStartDate, a.FDate) = 0 then 1 else
Ceiling( DATEDIFF(day,b.FStartDate, a.FDate) / (365.0 * Case When c.FCategoryCode = 'GY01' then 2
When c.FCategoryCode = 'GY02' then 4
When c.FCategoryCode = 'GY03' then 2 else 2 End)
) End
from HR_WorkClothesList a, HR_Employee b, HR_WorkClothesLedger c
Where a.FJobNo = b.FJobNo
and a.FNumber = c.FNumber
-- ================================================
-- 清空业务表数据 （特别注意：以下只有在初始化时才可能需要使用）
-- ================================================
--员工考勤
Truncate Table HR_EmployeeAttendance
--Truncate Table HR_EmployeeHoliday
--生产考勤
Truncate Table WT_WorkAttendanceList
delete from WT_WorkAttendance
--工时登记
Truncate Table WT_ProductedOrderDetail
Truncate Table WT_ExceptionWH
Truncate Table WT_WorkRecordDetail
delete from WT_WorkRecord
--工时记录
Truncate Table WT_WorkHour
--库存
Truncate Table WS_BeginInventory
Truncate TABLE WS_ProduceOutList
delete from WS_ProduceOut
Truncate Table WS_ProduceInList
delete from WS_ProduceIn
--生產員工
truncate table WT_Employee
--刷新所有单据内码
Exec SE_RefreshBillID
-- ================================================
-- 全部引入操作完成后马上要进行的事宜
-- ================================================
1) 记得同步生产员工
2) 记得运行工装扣款的更新功能
Update DefaultItemList set ItemText=N'包裝工程師(停用)' where FItemID = 123 and RowID = 64
网友评论