CREATE OR REPLACE PROCEDURE VOCATIONRECORD_Proc --同步HR休假记录表
(passnum in varchar2, --日志批号
hardwareoid in number) --JOBS的作业ID
as
Beg_Date varchar2(20); --同步开始时间
End_Date varchar2(20); --同步结束时间
iCount number(8); --修改标识
jCount number(8); --汇总计数
record_count NUMBER; --计数
begin
beg_date := to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
jCount := 0;
declare
cursor c_job is
select * from TB_TMG_VOCATIONRECORD@HRMIDEA where C_SUCCESSFLAG = 0 ; --查找HR中没有同步的记录;
-- c_row c_job%rowtype;
begin
record_count := 0;
FOR c_row IN c_job LOOP
-- open c_job;
-- loop
-- fetch c_job
-- into c_row;
-- exit when c_job%notfound;
begin
record_count := record_count + 1;
if c_row.C_MODIFYFLAG = 0 then
--判断新增修改标识
begin
insert into TB_GJ_VOCATIONRECORD
(C_OID_VOCATIONRECORD,
C_EMPOID,
C_UNITID,
C_ATTITEMOID,
C_YEAR,
C_BEGINTIME,
C_ENDTIME,
C_DAYS,
C_REASON,
C_PRINCIPAL,
C_LINKMAN,
C_REMARK,
C_ADDBYRECORD,
C_STATUS,
C_OPERATOR,
C_OPERATETIME,
C_APPLYOID,
C_CALCULATION,
C_VALUE)
values
(c_row.C_OID_VOCATIONRECORD,
c_row.C_EMPOID,
c_row.C_UNITID,
c_row.C_ATTITEMOID,
c_row.C_YEAR,
c_row.C_BEGINTIME,
c_row.C_ENDTIME,
c_row.C_DAYS,
c_row.C_REASON,
c_row.C_PRINCIPAL,
c_row.C_LINKMAN,
c_row.C_REMARK,
c_row.C_ADDBYRECORD,
c_row.C_STATUS,
c_row.C_OPERATOR,
c_row.C_OPERATETIME,
c_row.C_APPLYOID,
c_row.C_CALCULATION,
c_row.C_VALUE);
update TB_TMG_VOCATIONRECORD@HRMIDEA
set C_SUCCESSFLAG = 1, C_SUCCESSDATE = sysdate
where C_OID_VOCATIONRECORD = c_row.C_OID_VOCATIONRECORD
and C_SUCCESSFLAG = 0;
jCount := jCount + 1;
end;
else
begin
select count(*)
into iCount
from TB_GJ_VOCATIONRECORD
where C_OID_VOCATIONRECORD = c_row.C_OID_VOCATIONRECORD;
if iCount = 0 then--判断是否在归集平台中存在该记录
begin
insert into TB_GJ_VOCATIONRECORD
(C_OID_VOCATIONRECORD,
C_EMPOID,
C_UNITID,
C_ATTITEMOID,
C_YEAR,
C_BEGINTIME,
C_ENDTIME,
C_DAYS,
C_REASON,
C_PRINCIPAL,
C_LINKMAN,
C_REMARK,
C_ADDBYRECORD,
C_STATUS,
C_OPERATOR,
C_OPERATETIME,
C_APPLYOID,
C_VALUE,
C_CALCULATION)
values
(c_row.C_OID_VOCATIONRECORD,
c_row.C_EMPOID,
c_row.C_UNITID,
c_row.C_ATTITEMOID,
c_row.C_YEAR,
c_row.C_BEGINTIME,
c_row.C_ENDTIME,
c_row.C_DAYS,
c_row.C_REASON,
c_row.C_PRINCIPAL,
c_row.C_LINKMAN,
c_row.C_REMARK,
c_row.C_ADDBYRECORD,
c_row.C_STATUS,
c_row.C_OPERATOR,
c_row.C_OPERATETIME,
c_row.C_APPLYOID,
c_row.C_VALUE,
c_row.C_CALCULATION);
update TB_TMG_VOCATIONRECORD@HRMIDEA
set C_SUCCESSFLAG = 1, C_SUCCESSDATE = sysdate
where C_OID_VOCATIONRECORD = c_row.C_OID_VOCATIONRECORD
and C_SUCCESSFLAG = 0;
end;
jCount := jCount + 1;
else
begin
update TB_GJ_VOCATIONRECORD
set C_EMPOID = c_row.C_EMPOID,
C_UNITID = c_row.C_UNITID,
C_ATTITEMOID = c_row.C_ATTITEMOID,
C_YEAR = c_row.C_YEAR,
C_BEGINTIME = c_row.C_BEGINTIME,
C_ENDTIME = c_row.C_ENDTIME,
C_DAYS = c_row.C_DAYS,
C_REASON = c_row.C_REASON,
C_PRINCIPAL = c_row.C_PRINCIPAL,
C_LINKMAN = c_row.C_LINKMAN,
C_REMARK = c_row.C_REMARK,
C_ADDBYRECORD = c_row.C_ADDBYRECORD,
C_STATUS = c_row.C_STATUS,
C_OPERATOR = c_row.C_OPERATOR,
C_OPERATETIME = c_row.C_OPERATETIME,
C_APPLYOID = c_row.C_APPLYOID,
C_VALUE =c_row.C_VALUE,
C_CALCULATION =c_row.C_CALCULATION
where C_OID_VOCATIONRECORD = c_row.C_OID_VOCATIONRECORD;
update TB_TMG_VOCATIONRECORD@HRMIDEA
set C_SUCCESSFLAG = 1, C_SUCCESSDATE = sysdate
where C_OID_VOCATIONRECORD = c_row.C_OID_VOCATIONRECORD
and C_SUCCESSFLAG = 0;
end;
jCount := jCount + 1;
end if;
end;
end if;
IF MOD(record_count, 500) = 0 THEN --每500条提交一次
BEGIN
COMMIT;
END;
END IF;
end;
end loop;
--close c_job; --关闭游标
COMMIT;
End_Date := to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
insert into tb_gj_synchrolog
(c_flag,
c_message,
c_total,
c_passnum,
c_hardwareoid,
c_begindate,
c_enddate,
c_operationtime,
c_operator,
c_type,
c_over)
values
('1',
'成功',
jcount,
passnum,
hardwareoid,
to_date(Beg_Date, 'yyyy-mm-dd hh24:mi:ss'),
to_date(End_Date, 'yyyy-mm-dd hh24:mi:ss'),
sysdate,
'admin',
'HR休假记录表',
'是');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; -- 回滚事务
select count(*) --汇总HR中没有同步的记录;
into jcount
from TB_TMG_VOCATIONRECORD@HRMIDEA
where C_SUCCESSFLAG = 0;
insert into tb_gj_synchrolog
(c_flag,
c_message,
c_total,
c_passnum,
c_hardwareoid,
c_begindate,
c_enddate,
c_operationtime,
c_operator,
c_type,
c_over)
values
('0',
'失败',
jcount,
passnum,
hardwareoid,
to_date(Beg_Date, 'yyyy-mm-dd hh24:mi:ss'),
to_date(End_Date, 'yyyy-mm-dd hh24:mi:ss'),
sysdate,
'admin',
'HR休假记录表',
'否');
COMMIT;
end;
end VOCATIONRECORD_Proc;