ALTER PROCEDURE [dbo].[usp_ea_Copy]
(
@eaId int,
@createdBy varchar(50),
@newEaId int output
)
AS
declare @count as int
declare @newId as int
select @count=count(*) from Ea where id=@eaId;
if @count<1
begin
RAISERROR ('eaid is not existed ', 16, 1)
return -1
end
--unit_titlecode,ReceivedTime,ReceivedletterNumber(申报文号、接收时间、接收文号不copy)
--createdtime设置为GetDate(),Status为未提交状态，即'0',createdBy设置为当前用户id
set transaction isolation level serializable
begin transaction
insert into ea(unit_code,unit_title, comptroller, group_leader, abroad_mission, arrange_unit,
foreign_unit, city_start, send_num, send_time, stay_days, mission_type,AbroadProjectNo,
cost_source,DoForeignEx, RouteLine, Cost_add, Memo, Memo2, Principle, phone, CellPhone,fax, Email,
Invitation, ScanFile, ScanFile2, createdtime, CreatedBy,status )
select unit_code,unit_title, comptroller, group_leader, abroad_mission, arrange_unit,
foreign_unit, city_start, send_num, send_time, stay_days, mission_type,AbroadProjectNo,
cost_source,DoForeignEx, RouteLine, Cost_add, Memo, Memo2, Principle, phone, CellPhone,fax, Email,
Invitation, ScanFile, ScanFile2, GetDate(), @createdBy ,'0' from Ea where id=@eaId
select @newEaId=max(id) from Ea where unit_titlecode is null
commit transaction
set transaction isolation level read committed
insert into EaUnit(eaid,unit_code,send_num,memo,sequence)
select @newEaId,unit_code,send_num,memo,sequence from EaUnit where eaid=@eaId
insert into EaRoute(eaid, city, country_code, stay_days, mission, is_trans, Sequence)
select @newEaId, city, country_code, stay_days, mission, is_trans, Sequence from EaRoute where eaid=@eaId
insert into EaPerson(eaid, xmbm, name, idcode, unit_code, subUnitCode, subUnitName, Job, kshlx, wychj, tlchj, flchj,
kh, position, professionLevel, HealthStatus, ExaminationScan, Sequence, PositionLevel,feeGrade)
select @newEaId,xmbm, name, idcode, unit_code, subUnitCode, subUnitName, Job, kshlx, wychj, tlchj, flchj,
kh, position, professionLevel, HealthStatus, ExaminationScan, Sequence, PositionLevel,feeGrade
from EaPerson where eaid=@eaId
insert into EaHighGradePerson(eaid,Name, Xmbm, PositionName, PositionLevel, Mission, LastAbroadTime, LastAbroadCountrys,
LastAbroadDays, LastAbroadMission, Sequence)
select @newEaId,Name, Xmbm, PositionName, PositionLevel, Mission, LastAbroadTime,
LastAbroadCountrys, LastAbroadDays, LastAbroadMission, Sequence from EaHighGradePerson where eaid=@eaId
insert into EaRetiredPerson(EaId, xmbm, Name, Age, Helth, Job, Sequence)
select @newEaId, xmbm, Name, Age, Helth, Job, Sequence from EaRetiredPerson where eaid=@eaId
RETURN
