自己曾经写的同步数据的存储过程

-----------------------------------------------------
spool pkg_syn_to_siss.log

prompt
prompt Creating package PKG_SYN_TO_SISS
prompt ================================
prompt
create or replace package Pkg_SYN_TO_SISS is

  -- Author  : LONGZHUN
  -- Created : 2010-9-28 15:33:56
  -- Purpose :

  -- Public type declarations
--同步idecl通用平台数据：按单采集、非按单采集、激活信息、计费方案
PROCEDURE PRO_TY_FEE_BILL;
PROCEDURE PRO_TY_FEE_BILL_ALLDECLARE;
PROCEDURE PRO_TY_IDECL_ACTIVITY;
PROCEDURE PRO_TY_FEE_SCHEME_MAIN;
PROCEDURE PRO_TY_FEE_CORP_PROP;
--同步idecl东莞平台数据：按单采集、非按单采集、激活信息、计费方案
PROCEDURE PRO_DG_FEE_BILL;
PROCEDURE PRO_DG_FEE_BILL_ALLDECLARE;
PROCEDURE PRO_DG_IDECL_ACTIVITY;
PROCEDURE PRO_DG_FEE_SCHEME_MAIN;
PROCEDURE PRO_DG_FEE_CORP_PROP;
--模拟发单
procedure pro_postbill;
end Pkg_SYN_TO_SISS;
/

prompt
prompt Creating package body PKG_SYN_TO_SISS
prompt =====================================
prompt
create or replace package body Pkg_SYN_TO_SISS is

---------------------------------------
--通用版
---------------------------------------

---------------------------------------
--同步按单计费 FEEBILL
---------------------------------------
PROCEDURE PRO_TY_FEE_BILL
is
v_hcode_begin number;--记录要处理数据的开始HCODE
v_hcode_end   number;--记录要处理数据的结束HCODE
begin

--提取要处理的数据的开始HCODE
select max_hcode into v_hcode_begin
from t_logs_fee t
where t.table_name='fee_bill'
and   t.system_code='1' ;
--提取要处理的数据的结束HCODE
select max(hcode) into v_hcode_end from fee_bill@link_ideclgen t;

loop

if v_hcode_begin <= v_hcode_end
then
--处理数据
insert into fee_bill_middle
( seq_num,hcode,corp_code,password,module_id,bill_id,bill_no,receive_code,send_state,fee_date,print_state,
  vanish_state,balance,monthcount,check_orgcode,sendermail,reg_cn,reg_no,reg_date,contract_no,charge_flag)
select seq_fee_bill_middle.nextval as seq_num,
       t1.hcode,t1.corp_code,t2.password,t1.module_id,t1.bill_id,t1.bill_no,t1.receive_code,t1.send_state,t1.fee_date,t1.print_state,
       t1.vanish_state,t1.balance,t1.monthcount,t1.check_orgcode,t1.sendermail,t1.reg_cn,t1.reg_no,t1.reg_date,t1.contract_no,t1.charge_flag
from fee_bill@link_ideclgen           t1,
     idecl_reg_license@link_ideclgen  t2
where t1.corp_code=t2.corp_code
and   t1.hcode >  v_hcode_begin
and   t1.hcode <= v_hcode_begin +2000
and   t1.hcode <= v_hcode_end;

--标识已经处理的数据
if v_hcode_begin+2000 <v_hcode_end
then
update t_logs_fee t set t.max_hcode=v_hcode_begin+2000 where t.table_name='fee_bill' and t.system_code='1';
else
update t_logs_fee t set t.max_hcode=v_hcode_end where t.table_name='fee_bill' and t.system_code='1';
end if;

delete from  t_logs_sys_fee t where t.tablename='fee_bill';

commit;

    v_hcode_begin:=v_hcode_begin+2000;
else
     exit;
end if;
end loop;

end PRO_TY_FEE_BILL;

---------------------------------------
--同步普通计费 FEE_BILL_ALLDECLARE
---------------------------------------
PROCEDURE PRO_TY_FEE_BILL_ALLDECLARE
is
v_hcode_begin number;--记录要处理数据的开始HCODE
v_hcode_end   number;--记录要处理数据的结束HCODE
begin

--提取要处理的数据的开始HCODE
select max_hcode into v_hcode_begin
from t_logs_fee t
where t.table_name='fee_bill_alldeclare'
and   t.system_code='1';
--提取要处理的数据的结束HCODE
select max(hcode) into v_hcode_end from fee_bill_alldeclare@link_ideclgen ;

loop

if v_hcode_begin <= v_hcode_end
then
--处理数据
insert into fee_bill_alldeclare t
( t.hcode,t.corp_code,t.module_id,t.bill_id,t.bill_no,t.receive_code,t.send_state,
  t.fee_date,t.print_state,t.vanish_state,t.balance,t.monthcount,t.check_orgcode,
  t.sendermail,t.reg_cn,t.reg_no,t.reg_date,t.contract_no,t.charge_flag)
select SEQ_FEE_BILL_ALLDECLARE.nextval as hcode,t1.corp_code,t1.module_id,t1.bill_id,t1.bill_no,
       t1.receive_code,t1.send_state,t1.fee_date,t1.print_state,t1.vanish_state,t1.balance,t1.monthcount,
       t1.check_orgcode,t1.sendermail,t1.reg_cn,t1.reg_no,t1.reg_date,t1.contract_no,t1.charge_flag
from fee_bill_alldeclare@link_ideclgen t1
where t1.hcode >  v_hcode_begin
and   t1.hcode <= v_hcode_begin+2000
and   t1.hcode <= v_hcode_end;

--标识已经处理的数据
if v_hcode_begin+2000 <v_hcode_end
then
update t_logs_fee t set t.max_hcode=v_hcode_begin+2000 where t.table_name='fee_bill_alldeclare' and t.system_code='1';
else
update t_logs_fee t set t.max_hcode=v_hcode_end where t.table_name='fee_bill_alldeclare' and t.system_code='1';
end if;

commit;

    v_hcode_begin:=v_hcode_begin+2000;
else
     exit;
end if;
end loop;

end PRO_TY_FEE_BILL_ALLDECLARE;

---------------------------------------
--同步激活明细 IDECL_ACTIVITY
---------------------------------------

PROCEDURE PRO_TY_IDECL_ACTIVITY IS
v_count              number;
v_num                number;
v_corp_code          varchar2(50);
v_hardware_serial_no varchar2(300);
v_hardware_no        varchar2(100);
v_dm_type            varchar2(10);
type TYPE_CUR is ref cursor;
v_cursor TYPE_CUR ;
BEGIN

--删除激活明细记录表中已同步记录 T_LOGS_ACTIVITY
delete from t_logs_activity@link_ideclgen t where t.syn_status='1';
commit;

select count(*) into v_num from idecl_activity_middle where system_code ='1';

if v_num=0 then

--提取数据到本地中间表
insert into idecl_activity_middle
( corp_code,hardware_serial_no,latest_conn_time,latest_bulletin_time,hostname,os,osversion,
  ieversion,ip,cpu_no,hardware_no,mac_no,app_ptch,dm_type,system_code)
select distinct
       t1.corp_code,t1.hardware_serial_no,t1.latest_conn_time,t1.latest_bulletin_time,t1.hostname,t1.os,t1.osversion,
       t1.ieversion,t1.ip,t1.cpu_no,t1.hardware_no,t1.mac_no,t1.app_ptch,t2.dm_type,'1' as system_code
from idecl_activity@link_ideclgen   t1,
     t_logs_activity@link_ideclgen  t2
where t1.corp_code          = t2.corp_code
and   t1.hardware_serial_no = t2.hardware_serial_no
and   t1.hardware_no        = t2.hardware_no
and   t2.syn_status='0';

update t_logs_activity@link_ideclgen t
set t.syn_status = '1',t.syn_date=sysdate
where  t.syn_status='0';

commit;
end if;

open v_cursor for
select corp_code,hardware_serial_no,hardware_no,dm_type
from idecl_activity_middle t
where t.system_code='1';--1代表idecl通用

loop
fetch v_cursor into v_corp_code,v_hardware_serial_no,v_hardware_no,v_dm_type;

exit when v_cursor%notfound;

if v_dm_type='delete'
then  delete from idecl_activity t
      where t.corp_code          = V_CORP_CODE
      and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
      and   t.hardware_no        = V_HARDWARE_NO ;
     
delete idecl_activity_middle t
where t.corp_code          = V_CORP_CODE
and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
and   t.hardware_no        = V_HARDWARE_NO
and   t.system_code        = '1'
and   t.dm_type            ='delete';
commit;
end if;

if v_dm_type<>'delete'
then  select count(*) into v_count
      from idecl_activity t
      where t.corp_code          = V_CORP_CODE
      and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
      and   t.hardware_no        = V_HARDWARE_NO ;
     
      if v_count =0
      then insert into idecl_activity
           ( corp_code,hardware_serial_no,latest_conn_time,latest_bulletin_time,
             hostname,os,osversion,ieversion,ip,cpu_no,hardware_no,mac_no,app_ptch)
           select corp_code,hardware_serial_no,latest_conn_time,latest_bulletin_time,
                  hostname,os,osversion,ieversion,ip,cpu_no,hardware_no,mac_no,app_ptch
           from idecl_activity_middle t
           where t.corp_code          = V_CORP_CODE
           and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
           and   t.hardware_no        = V_HARDWARE_NO
           and   t.system_code        = '1'
           and   t.dm_type           <>'delete' ;
          
      else update idecl_activity t1
           set (t1.latest_conn_time,t1.latest_bulletin_time,t1.hostname,t1.os,t1.osversion,
                t1.ieversion,t1.ip,t1.cpu_no,t1.hardware_no,t1.mac_no,t1.app_ptch) =
               ( select t2.latest_conn_time,t2.latest_bulletin_time,t2.hostname,t2.os,t2.osversion,
                        t2.ieversion,t2.ip,t2.cpu_no,t2.hardware_no,t2.mac_no,t2.app_ptch
                 from  idecl_activity_middle t2
                 where t1.corp_code          = t2.corp_code
                 and   t1.hardware_serial_no = t2.hardware_serial_no
                 and   t1.hardware_no        = t2.hardware_no
                 and   t2.system_code        = '1'
                 and   t2.dm_type            <>'delete' )
            where t1.corp_code          = V_CORP_CODE
            and   t1.hardware_serial_no = V_HARDWARE_SERIAL_NO
            and   t1.hardware_no        = V_HARDWARE_NO ;
           
       end if;
      
delete idecl_activity_middle t
where t.corp_code          = V_CORP_CODE
and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
and   t.hardware_no        = V_HARDWARE_NO
and   t.system_code        = '1'
and   t.dm_type            <>'delete';
commit;

end if;

end loop;        
     
close v_cursor;

 

END PRO_TY_IDECL_ACTIVITY;

---------------------------------------
--同步计费方案 FEE_SCHEME_MAIN
---------------------------------------
PROCEDURE PRO_TY_FEE_SCHEME_MAIN is
v_count           number;
v_schemeid        number;
v_scheme_newid    number;
v_scheme_oldid    number;
v_scheme_name     varchar2(200);
v_office          varchar2(20);
v_dm_type         varchar2(10);
p_productCode     varchar2(10) default 'IDECL1001';
type TYPE_CUR is ref cursor;
v_cursor TYPE_CUR ;
begin

delete from t_logs_activity_fee@link_ideclgen t where t.syn_status='1' and t.table_name='fee_scheme_main';
commit;

open v_cursor for select t1.scheme_id,t1.cname,t1.office,t2.dm_type
                  from t_logs_activity_fee@link_ideclgen t2,
                       fee_scheme_main@link_ideclgen     t1
                  where t2.table_name='fee_scheme_main'
                  and   t2.syn_status ='0'
                  and   t1.scheme_id=t2.corp_code;

loop
fetch v_cursor into v_scheme_oldid,v_scheme_name,v_office,v_dm_type;

exit when v_cursor% notfound;
--判断fee_scheme_main是否有为空，若为空，则v_schemeid为1，否则v_schemeid为最大的scheme_id＋1
select max(scheme_id) into v_schemeid from fee_scheme_main;
if v_schemeid is null
then v_schemeid := 1;
else v_schemeid := v_schemeid + 1;
end if;
--如果不是delete操作，则判断中间表中是否有新旧scheme对应记录
if v_dm_type<>'delete'
then select count(*) into v_count
     from fee_scheme_middle t
     where t.scheme_oldid=v_scheme_oldid
     and t.system_code='1' ; --1代表idecl通用
    
     if v_count=0 --如果中间表中没有新旧scheme对应记录，则插入一条新记录，并插入其相关表
     then insert into fee_scheme_middle(scheme_oldid,scheme_newid,system_code)
          values(v_scheme_oldid,v_schemeid,'1');
         
          insert into fee_scheme_main(scheme_id,cname,office)
          values (v_schemeid,v_scheme_name,v_office);
         
          insert into fee_scheme_product(scheme_id,product_code)
          values(v_schemeid,p_productCode);
         
          insert into fee_scheme_person
          (scheme_id,audit_flag,audit_per_name,audit_time,audit_remark,audit_per_id,add_per_id,add_per_name,add_time)
          values(v_schemeid,'0','',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'','','','',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));

          insert into fee_scheme(scheme_id,module_id,utile,mcname)
          select v_schemeid,f.module_id,f.utile,f.mcname
          from fee_scheme@link_ideclgen f
          where f.scheme_id=v_scheme_oldid;

          update t_logs_activity_fee@link_ideclgen t2
          set t2.syn_status='1' ,t2.syn_date=sysdate
          where t2.syn_status='0'
          and   t2.table_name='fee_scheme_main'
          and   t2.corp_code=v_scheme_oldid;

          commit;

     end if;
     if v_count>0 --如果中间表中有新旧scheme对应记录，则用中间表中旧schemeid对应的新schemeid更新其相关表
     then  select max(t.scheme_newid) into v_scheme_newid
           from fee_scheme_middle t
           where t.scheme_oldid=v_scheme_oldid
           and   t.system_code='1' ;--1代表通用
         
          
           update fee_scheme_main set cname=v_scheme_name,office=v_office where scheme_id=v_scheme_newid;
           update fee_scheme_product set product_code=p_productCode where scheme_id=v_scheme_newid;

           --update fee_scheme f2
           --set (f2.module_id,f2.utile,f2.mcname)=( select f1.module_id,f1.utile,f1.mcname
            --                                       from fee_scheme@link_ideclgen  f1,
             --                                           fee_scheme_middle         f3
            --                                       where f1.scheme_id=v_scheme_oldid
             --                                      and   f3.scheme_oldid=f1.scheme_id
             --                                      and   f3.scheme_newid=f2.scheme_id
             --                                      and   f1.module_id=f2.module_id )
            --where f2.scheme_id=v_scheme_newid;
            merge into fee_scheme                            t1
            using ( select tb.scheme_newid as scheme_id,
                           ta.module_id,ta.utile,ta.mcname
                    from fee_scheme@link_ideclgen  ta,
                         fee_scheme_middle         tb
                    where tb.scheme_oldid=ta.scheme_id
                    and   ta.scheme_id=v_scheme_oldid
                    and   tb.system_code ='1'         )      t2
            on (    t1.scheme_id = t2.scheme_id
                and t1.module_id = t2.module_id
                )
            when matched then
            update set t1.utile     = t2.utile,
                       t1.mcname    = t2.mcname
            when not matched then
            insert (scheme_id,module_id,utile,mcname)
            values(t2.scheme_id,t2.module_id,t2.utile,t2.mcname);

           
           
            update t_logs_activity_fee@link_ideclgen t2
            set t2.syn_status='1' ,t2.syn_date=sysdate
            where t2.syn_status='0'
            and   t2.table_name='fee_scheme_main'
            and   t2.corp_code=v_scheme_oldid;
            commit;
        end if;
end if;

if v_dm_type='delete'
then select count(*) into v_count from fee_scheme_middle t where t.scheme_oldid=v_scheme_oldid and t.system_code='1';
     if v_count>0
     then select max(t.scheme_newid) into v_scheme_newid
          from fee_scheme_middle t
          where t.scheme_oldid=v_scheme_oldid
          and t.system_code='1';
         
          delete from fee_scheme_middle where scheme_oldid=v_scheme_oldid and system_code='1';
          delete from fee_scheme_main where scheme_id=v_scheme_newid;
          delete from fee_scheme_product where scheme_id=v_scheme_newid;
          delete from fee_scheme_person where scheme_id=v_scheme_newid;
          delete from fee_scheme where scheme_id=v_scheme_newid;

          update t_logs_activity_fee@link_ideclgen t2
          set t2.syn_status='1' ,t2.syn_date=sysdate
          where t2.syn_status='0'
          and   t2.table_name='fee_scheme_main'
          and   t2.corp_code=v_scheme_oldid;
          commit;
      end if;
end if;

end loop;
close v_cursor;

end PRO_TY_FEE_SCHEME_MAIN;
---------------------------------------
--同步 FEE_CORP_PROP
---------------------------------------

PROCEDURE PRO_TY_FEE_CORP_PROP is
v_scheme_newid       varchar2(10);
v_count              number;
v_num                number;
v_corp_code          fee_corp_prop_middle.corp_code%type;
v_dm_type            fee_corp_prop_middle.dm_type%type;
v_charge_type        fee_corp_prop_middle.charge_type%type;
type TYPE_CUR is ref cursor;
v_cursor TYPE_CUR ;
begin

--删除已处理过的记录
delete from t_logs_activity_fee@link_ideclgen t where t.syn_status='1' and t.table_name='fee_corp_prop';
commit;

select count(*) into v_num from fee_corp_prop_middle where system_code ='1';

if v_num=0 then 
     
     
--提取数据到本地中间表
insert into fee_corp_prop_middle
( corp_code,soft_charge,charge_type,credit,balance,monthstatus,
  creditdays,balancebase,maxhcode,manage_type,dm_type,system_code)
select t1.corp_code,t1.soft_charge,t1.charge_type,t1.credit,t1.balance,t1.monthstatus,
       t1.creditdays,t1.balancebase,t1.maxhcode,'0',t2.dm_type,'1' as system_code
from fee_corp_prop@link_ideclgen                t1,
     t_logs_activity_fee@link_ideclgen          t2
where t1.corp_code     = t2.corp_code
and   t2.table_name    = 'fee_corp_prop'
and   t2.syn_status    = '0';

update t_logs_activity_fee@link_ideclgen t
set t.syn_status = '1',t.syn_date=sysdate
where t.table_name='fee_corp_prop'
and   t.syn_status='0';

commit;
end if;
     
open v_cursor for select t.corp_code,t.charge_type,t.dm_type
                  from fee_corp_prop_middle t
                  where t.system_code='1';
loop
fetch v_cursor into v_corp_code,v_charge_type,v_dm_type;

exit when v_cursor% notfound;
--如果不是delete操作，则判断fee_corp_prop中是否有对应记录
if v_dm_type<>'delete'
then select count(*) into v_count from fee_corp_prop t where t.corp_code=v_corp_code;
         
     select max(t.scheme_newid) into v_scheme_newid
     from fee_scheme_middle t
     where t.scheme_oldid    = v_charge_type
     and   t.system_code     = '1' ;
         
     if v_count=0
     then insert into fee_corp_prop
                (corp_code,soft_charge,charge_type,credit,balance,monthstatus,creditdays,balancebase,maxhcode)
          select t.corp_code,t.soft_charge,v_scheme_newid,t.credit,t.balance,t.monthstatus,t.creditdays,t.balancebase,0 as maxhcode
          from fee_corp_prop_middle            t
          where t.corp_code     = v_corp_code
          and   t.system_code   = '1'
          and   t.dm_type       <>'delete';
           
     else --update fee_corp_prop f2
          --set (f2.soft_charge,f2.charge_type,f2.credit,f2.monthstatus,f2.creditdays,f2.balancebase)
           --           =(select t.soft_charge,v_scheme_newid,t.credit,t.monthstatus,t.creditdays,t.balancebase
           --             from fee_corp_prop_middle t
           --             where t.corp_code   = v_corp_code
           --             and   t.system_code = '1'
           --             and   t.dm_type <>'delete')    
          --where f2.corp_code=v_corp_code;
      
         update fee_corp_prop f2
         set f2.charge_type = v_scheme_newid
         where f2.corp_code=v_corp_code;

      
      end if;
--删除临时表对应记录
delete from fee_corp_prop_middle t
where t.corp_code    = v_corp_code
and   t.system_code  = '1'
and   t.dm_type      <>'delete';
commit;

end if;
if v_dm_type='delete'
--如果是delete操作，则删除fee_corp_prop中对应记录
then  select count(*) into v_count from fee_corp_prop t where t.corp_code=v_corp_code;
      if v_count>0
      then delete from fee_corp_prop t where t.corp_code   = v_corp_code;
      end if; 
end if;
--删除临时表对应记录
delete from fee_corp_prop_middle t
where t.corp_code    = v_corp_code
and   t.system_code  = '1'
and   t.dm_type      ='delete';
commit;
     
end loop;
close v_cursor;

end PRO_TY_FEE_CORP_PROP;

 

---------------------------------------
--东莞版
---------------------------------------

---------------------------------------
--同步按单计费 FEEBILL
---------------------------------------
PROCEDURE PRO_DG_FEE_BILL
is
v_hcode_begin number;--记录要处理数据的开始HCODE
v_hcode_end   number;--记录要处理数据的结束HCODE
begin

--提取要处理的数据的开始HCODE
select max_hcode into v_hcode_begin
from t_logs_fee t
where t.table_name='fee_bill'
and   t.system_code='2' ;
--提取要处理的数据的结束HCODE
select max(hcode) into v_hcode_end from fee_bill@link_eport112 t;

loop

if v_hcode_begin <= v_hcode_end
then
--处理数据
insert into fee_bill_middle
( seq_num,hcode,corp_code,password,module_id,bill_id,bill_no,receive_code,send_state,fee_date,print_state,
  vanish_state,balance,monthcount,check_orgcode,sendermail,reg_cn,reg_no,reg_date,contract_no,charge_flag)
select seq_fee_bill_middle.nextval as seq_num,
       t1.hcode,t1.corp_code,t2.password,t1.module_id,t1.bill_id,t1.bill_no,t1.receive_code,t1.send_state,t1.fee_date,t1.print_state,
       t1.vanish_state,t1.balance,t1.monthcount,t1.check_orgcode,t1.sendermail,t1.reg_cn,t1.reg_no,t1.reg_date,t1.contract_no,t1.charge_flag
from fee_bill@link_eport112           t1,
     idecl_reg_license@link_eport112  t2
where t1.corp_code=t2.corp_code
and   t1.hcode >  v_hcode_begin
and   t1.hcode <= v_hcode_begin +2000
and   t1.hcode <= v_hcode_end;

--标识已经处理的数据
if v_hcode_begin+2000 <v_hcode_end
then
update t_logs_fee t set t.max_hcode=v_hcode_begin+2000 where t.table_name='fee_bill' and t.system_code='2';
else
update t_logs_fee t set t.max_hcode=v_hcode_end where t.table_name='fee_bill' and t.system_code='2';
end if;

delete from  t_logs_sys_fee t where t.tablename='fee_bill';

commit;

    v_hcode_begin:=v_hcode_begin+2000;
else
     exit;
end if;
end loop;

end PRO_DG_FEE_BILL;

---------------------------------------
--同步普通计费 FEE_BILL_ALLDECLARE
---------------------------------------
PROCEDURE PRO_DG_FEE_BILL_ALLDECLARE
is
v_hcode_begin number;--记录要处理数据的开始HCODE
v_hcode_end   number;--记录要处理数据的结束HCODE
begin

--提取要处理的数据的开始HCODE
select max_hcode into v_hcode_begin
from t_logs_fee t
where t.table_name='fee_bill_alldeclare'
and   t.system_code='2' ;
--提取要处理的数据的结束HCODE
select max(hcode) into v_hcode_end from fee_bill_alldeclare@link_eport112 ;
loop

if v_hcode_begin <= v_hcode_end
then

insert into fee_bill_alldeclare t
( t.hcode,t.corp_code,t.module_id,t.bill_id,t.bill_no,t.receive_code,t.send_state,
  t.fee_date,t.print_state,t.vanish_state,t.balance,t.monthcount,t.check_orgcode,
  t.sendermail,t.reg_cn,t.reg_no,t.reg_date,t.contract_no,t.charge_flag)
select SEQ_FEE_BILL_ALLDECLARE.nextval as hcode,t1.corp_code,t1.module_id,t1.bill_id,t1.bill_no,
       t1.receive_code,t1.send_state,t1.fee_date,t1.print_state,t1.vanish_state,t1.balance,t1.monthcount,
       t1.check_orgcode,t1.sendermail,t1.reg_cn,t1.reg_no,t1.reg_date,t1.contract_no,t1.charge_flag
from fee_bill_alldeclare@link_eport112 t1
where t1.hcode >  v_hcode_begin
and   t1.hcode <= v_hcode_begin+2000
and   t1.hcode <= v_hcode_end;

--标识已经处理的数据
if v_hcode_begin+2000 <v_hcode_end
then
update t_logs_fee t set t.max_hcode=v_hcode_begin+2000 where t.table_name='fee_bill_alldeclare' and t.system_code='2';
else
update t_logs_fee t set t.max_hcode=v_hcode_end where t.table_name='fee_bill_alldeclare' and t.system_code='2';
end if;

--delete from  t_logs_sys_fee t where t.tablename='fee_bill_alldeclare';

commit;

    v_hcode_begin:=v_hcode_begin+2000;
    else
     exit;
    end if;
end loop;

end PRO_DG_FEE_BILL_ALLDECLARE;

---------------------------------------
--同步激活明细 IDECL_ACTIVITY
---------------------------------------

PROCEDURE PRO_DG_IDECL_ACTIVITY IS
v_count              number;
v_num                number;
v_corp_code          varchar2(50);
v_hardware_serial_no varchar2(300);
v_hardware_no        varchar2(100);
v_dm_type            varchar2(10);
type TYPE_CUR is ref cursor;
v_cursor TYPE_CUR ;
BEGIN

--删除激活明细记录表中已同步记录 T_LOGS_ACTIVITY
delete from t_logs_activity@link_eport112 t where t.syn_status='1';
commit;

select count(*) into v_num from idecl_activity_middle where system_code ='2';
commit;
if v_num=0 then

--提取数据到本地中间表
insert into idecl_activity_middle
( corp_code,hardware_serial_no,latest_conn_time,latest_bulletin_time,hostname,os,osversion,
  ieversion,ip,cpu_no,hardware_no,mac_no,app_ptch,dm_type,system_code)
select distinct
       t1.corp_code,t1.hardware_serial_no,t1.latest_conn_time,t1.latest_bulletin_time,t1.hostname,t1.os,t1.osversion,
       t1.ieversion,t1.ip,t1.cpu_no,t1.hardware_no,t1.mac_no,t1.app_ptch,t2.dm_type,'2' as system_code
from idecl_activity@link_eport112   t1,
     t_logs_activity@link_eport112  t2
where t1.corp_code          = t2.corp_code
and   t1.hardware_serial_no = t2.hardware_serial_no
and   t1.hardware_no        = t2.hardware_no
and   t2.syn_status='0';

update t_logs_activity@link_eport112 t
set t.syn_status = '1',t.syn_date=sysdate
where  t.syn_status='0';

commit;
end if;

open v_cursor for
select corp_code,hardware_serial_no,hardware_no,dm_type
from idecl_activity_middle t
where t.system_code='2';--2代表idecl东莞

loop
fetch v_cursor into v_corp_code,v_hardware_serial_no,v_hardware_no,v_dm_type;

exit when v_cursor%notfound;

if v_dm_type='delete'
then  delete from idecl_activity t
      where t.corp_code          = V_CORP_CODE
      and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
      and   t.hardware_no        = V_HARDWARE_NO ;
     
delete idecl_activity_middle t
where t.corp_code          = V_CORP_CODE
and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
and   t.hardware_no        = V_HARDWARE_NO
and   t.system_code        = '2'
and   t.dm_type            ='delete';   
commit;
end if;

if v_dm_type<>'delete'   
then  select count(*) into v_count
      from idecl_activity t
      where t.corp_code          = V_CORP_CODE
      and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
      and   t.hardware_no        = V_HARDWARE_NO ;
     
      if v_count =0
      then insert into idecl_activity
           ( corp_code,hardware_serial_no,latest_conn_time,latest_bulletin_time,
             hostname,os,osversion,ieversion,ip,cpu_no,hardware_no,mac_no,app_ptch)
           select corp_code,hardware_serial_no,latest_conn_time,latest_bulletin_time,
                  hostname,os,osversion,ieversion,ip,cpu_no,hardware_no,mac_no,app_ptch
           from idecl_activity_middle t
           where t.corp_code          = V_CORP_CODE
           and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
           and   t.hardware_no        = V_HARDWARE_NO
           and   t.system_code        = '2'
           and   t.dm_type           <>'delete' ;
          
      else update idecl_activity t1
           set (t1.latest_conn_time,t1.latest_bulletin_time,t1.hostname,t1.os,t1.osversion,
                t1.ieversion,t1.ip,t1.cpu_no,t1.hardware_no,t1.mac_no,t1.app_ptch) =
               ( select t2.latest_conn_time,t2.latest_bulletin_time,t2.hostname,t2.os,t2.osversion,
                        t2.ieversion,t2.ip,t2.cpu_no,t2.hardware_no,t2.mac_no,t2.app_ptch
                 from  idecl_activity_middle t2
                 where t1.corp_code          = t2.corp_code
                 and   t1.hardware_serial_no = t2.hardware_serial_no
                 and   t1.hardware_no        = t2.hardware_no
                 and   t2.system_code        = '2'
                 and   t2.dm_type            <>'delete' )
            where t1.corp_code          = V_CORP_CODE
            and   t1.hardware_serial_no = V_HARDWARE_SERIAL_NO
            and   t1.hardware_no        = V_HARDWARE_NO ;
       end if;
      
delete idecl_activity_middle t
where t.corp_code          = V_CORP_CODE
and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
and   t.hardware_no        = V_HARDWARE_NO
and   t.system_code        = '2'
and   t.dm_type            <>'delete';
commit;

end if;

end loop;        
     
close v_cursor;

 

END PRO_DG_IDECL_ACTIVITY;

---------------------------------------
--同步计费方案 FEE_SCHEME_MAIN
---------------------------------------
PROCEDURE PRO_DG_FEE_SCHEME_MAIN is
v_count           number;
v_schemeid        number;
v_scheme_newid    number;
v_scheme_oldid    number;
v_scheme_name     varchar2(200);
v_office          varchar2(20);
v_dm_type         varchar2(10);
p_productCode     varchar2(10) default 'IDECL1001';
type TYPE_CUR is ref cursor;
v_cursor TYPE_CUR ;
begin

delete from t_logs_activity_fee@link_eport112 t where t.syn_status='1' and t.table_name='fee_scheme_main';
commit;

open v_cursor for select t1.scheme_id,t1.cname,t1.office,t2.dm_type
                  from t_logs_activity_fee@link_eport112 t2,
                       fee_scheme_main@link_eport112     t1
                  where t2.table_name='fee_scheme_main'
                  and   t2.syn_status ='0'
                  and   t1.scheme_id=t2.corp_code;

loop
fetch v_cursor into v_scheme_oldid,v_scheme_name,v_office,v_dm_type;

exit when v_cursor% notfound;
--判断fee_scheme_main是否有为空，若为空，则v_schemeid为1，否则v_schemeid为最大的scheme_id＋1
select max(scheme_id) into v_schemeid from fee_scheme_main;
if v_schemeid is null
then v_schemeid := 1;
else v_schemeid := v_schemeid + 1;
end if;
--如果不是delete操作，则判断中间表中是否有新旧scheme对应记录
if v_dm_type<>'delete'
then select count(*) into v_count
     from fee_scheme_middle t
     where t.scheme_oldid=v_scheme_oldid
     and t.system_code='2' ; --2代表idecl东莞
    
     if v_count=0 --如果中间表中没有新旧scheme对应记录，则插入一条新记录，并插入其相关表
     then insert into fee_scheme_middle(scheme_oldid,scheme_newid,system_code)
          values(v_scheme_oldid,v_schemeid,'2');
         
          insert into fee_scheme_main(scheme_id,cname,office)
          values (v_schemeid,v_scheme_name,v_office);
         
          insert into fee_scheme_product(scheme_id,product_code)
          values(v_schemeid,p_productCode);
         
          insert into fee_scheme_person
          (scheme_id,audit_flag,audit_per_name,audit_time,audit_remark,audit_per_id,add_per_id,add_per_name,add_time)
          values(v_schemeid,'0','',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'','','','',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));

          insert into fee_scheme(scheme_id,module_id,utile,mcname)
          select v_schemeid,f.module_id,f.utile,f.mcname
          from fee_scheme@link_eport112 f
          where f.scheme_id=v_scheme_oldid;

          update t_logs_activity_fee@link_eport112 t2
          set t2.syn_status='1' ,t2.syn_date=sysdate
          where t2.syn_status='0'
          and   t2.table_name='fee_scheme_main'
          and   t2.corp_code=v_scheme_oldid;

          commit;

     end if;
     if v_count>0 --如果中间表中有新旧scheme对应记录，则用中间表中旧schemeid对应的新schemeid更新其相关表
     then  select max(t.scheme_newid) into v_scheme_newid
           from fee_scheme_middle t
           where t.scheme_oldid=v_scheme_oldid
           and   t.system_code='2' ;--2代表东莞
         
          
           update fee_scheme_main set cname=v_scheme_name,office=v_office where scheme_id=v_scheme_newid;
           update fee_scheme_product set product_code=p_productCode where scheme_id=v_scheme_newid;

           --update fee_scheme f2
           --set (f2.module_id,f2.utile,f2.mcname)=( select f1.module_id,f1.utile,f1.mcname
           --                                        from fee_scheme@link_eport112  f1,
           --                                             fee_scheme_middle         f3
           --                                        where f1.scheme_id=v_scheme_oldid
           --                                        and   f3.scheme_oldid=f1.scheme_id
           --                                        and   f3.scheme_newid=f2.scheme_id
           --                                        and   f1.module_id=f2.module_id )
           -- where f2.scheme_id=v_scheme_newid;
           
            merge into fee_scheme                            t1
            using ( select tb.scheme_newid as scheme_id,
                           ta.module_id,ta.utile,ta.mcname
                    from fee_scheme@link_ideclgen  ta,
                         fee_scheme_middle         tb
                    where tb.scheme_oldid=ta.scheme_id
                    and   ta.scheme_id=v_scheme_oldid
                    and   tb.system_code ='2'         )      t2
            on (    t1.scheme_id = t2.scheme_id
                and t1.module_id = t2.module_id
                )
            when matched then
            update set t1.utile     = t2.utile,
                       t1.mcname    = t2.mcname
            when not matched then
            insert (scheme_id,module_id,utile,mcname)
            values(t2.scheme_id,t2.module_id,t2.utile,t2.mcname);
           
           
           
            update t_logs_activity_fee@link_eport112 t2
            set t2.syn_status='1' ,t2.syn_date=sysdate
            where t2.syn_status='0'
            and   t2.table_name='fee_scheme_main'
            and   t2.corp_code=v_scheme_oldid;
            commit;
        end if;
end if;

if v_dm_type='delete'
then select count(*) into v_count from fee_scheme_middle t where t.scheme_oldid=v_scheme_oldid and t.system_code='0';
     if v_count>0
     then select max(t.scheme_newid) into v_scheme_newid
          from fee_scheme_middle t
          where t.scheme_oldid=v_scheme_oldid
          and t.system_code='2';
         
          delete from fee_scheme_middle where scheme_oldid=v_scheme_oldid and system_code='2';
          delete from fee_scheme_main where scheme_id=v_scheme_newid;
          delete from fee_scheme_product where scheme_id=v_scheme_newid;
          delete from fee_scheme_person where scheme_id=v_scheme_newid;
          delete from fee_scheme where scheme_id=v_scheme_newid;

          update t_logs_activity_fee@link_eport112 t2
          set t2.syn_status='1' ,t2.syn_date=sysdate
          where t2.syn_status='0'
          and   t2.table_name='fee_scheme_main'
          and   t2.corp_code=v_scheme_oldid;
          commit;
        end if;
end if;

end loop;
close v_cursor;

end PRO_DG_FEE_SCHEME_MAIN;
---------------------------------------
--同步 FEE_CORP_PROP
---------------------------------------

PROCEDURE PRO_DG_FEE_CORP_PROP is
v_scheme_newid       varchar2(10);
v_count              number;
v_num                number;
v_corp_code          fee_corp_prop_middle.corp_code%type;
v_dm_type            fee_corp_prop_middle.dm_type%type;
v_charge_type        fee_corp_prop_middle.charge_type%type;
type TYPE_CUR is ref cursor;
v_cursor TYPE_CUR ;
begin

--删除已处理过的记录
delete from t_logs_activity_fee@link_eport112 t where t.syn_status='1' and t.table_name='fee_corp_prop';
commit;

select count(*) into v_num from fee_corp_prop_middle where system_code ='2';
commit;
if v_num=0 then 
     
     
--提取数据到本地中间表
insert into fee_corp_prop_middle
( corp_code,soft_charge,charge_type,credit,balance,monthstatus,
  creditdays,balancebase,maxhcode,manage_type,dm_type,system_code)
select t1.corp_code,t1.soft_charge,t1.charge_type,t1.credit,t1.balance,t1.monthstatus,
       t1.creditdays,t1.balancebase,t1.maxhcode,'0',t2.dm_type,'2' as system_code
from fee_corp_prop@link_eport112                t1,
     t_logs_activity_fee@link_eport112          t2
where t1.corp_code     = t2.corp_code
and   t2.table_name    = 'fee_corp_prop'
and   t2.syn_status    = '0';

update t_logs_activity_fee@link_eport112 t
set t.syn_status = '1',t.syn_date=sysdate
where t.table_name='fee_corp_prop'
and   t.syn_status='0';

commit;
end if;
     
open v_cursor for select t.corp_code,t.charge_type,t.dm_type
                  from fee_corp_prop_middle t
                  where t.system_code='2';
loop
fetch v_cursor into v_corp_code,v_charge_type,v_dm_type;
exit when v_cursor% notfound;
      --如果不是delete操作，则判断fee_corp_prop中是否有对应记录
if v_dm_type<>'delete'
then select count(*) into v_count from fee_corp_prop t where t.corp_code=v_corp_code;
     select max(t.scheme_newid) into v_scheme_newid
     from fee_scheme_middle t
     where t.scheme_oldid    = v_charge_type
     and   t.system_code     = '2' ;
         
     if v_count=0
     then insert into fee_corp_prop
             (corp_code,soft_charge,charge_type,credit,balance,monthstatus,creditdays,balancebase,maxhcode)
          select t.corp_code,t.soft_charge,v_scheme_newid,t.credit,t.balance,t.monthstatus,t.creditdays,t.balancebase,0
          from fee_corp_prop_middle            t
          where t.corp_code     = v_corp_code
          and   t.system_code   = '2'
          and   t.dm_type <>'delete';
           
     else --update fee_corp_prop f2
          --set (f2.soft_charge,f2.charge_type,f2.credit,f2.monthstatus,f2.creditdays,f2.balancebase)
          --       =(select t.soft_charge,v_scheme_newid,t.credit,t.monthstatus,t.creditdays,t.balancebase
          --         from fee_corp_prop_middle t
           --        where t.corp_code   = v_corp_code
           --        and   t.system_code = '2'
           --        and   t.dm_type <>'delete')    
          --where f2.corp_code=v_corp_code;
         
         update fee_corp_prop f2
         set f2.charge_type = v_scheme_newid
         where f2.corp_code=v_corp_code;
         
     end if;
--删除临时表对应记录   
delete from fee_corp_prop_middle t
where t.corp_code    = v_corp_code
and   t.system_code  = '2'
and   t.dm_type      <>'delete';
commit;
end if;
       --如果是delete操作，则删除fee_corp_prop中对应记录
if v_dm_type='delete' then
         select count(*) into v_count from fee_corp_prop t where t.corp_code=v_corp_code;
         if v_count>0 then
            delete from fee_corp_prop t
            where t.corp_code   = v_corp_code;
         end if; 
 end if;
--删除临时表对应记录
delete from fee_corp_prop_middle t
where t.corp_code    = v_corp_code
and   t.system_code  = '2'
and   t.dm_type      ='delete';
commit;
     
end loop;
close v_cursor;

end PRO_DG_FEE_CORP_PROP;

--模拟发单
procedure pro_postbill
is
v_num_begin number;--记录要处理数据的开始HCODE
v_num_end   number;--记录要处理数据的结束HCODE
v_seq_num   number;
V_CORP_CODE         VARCHAR2(50);
V_PASSWORD          VARCHAR2(50);
V_MODULE_ID         VARCHAR2(50);
V_BILL_ID           VARCHAR2(100);
V_BILL_NO           VARCHAR2(30);
V_RECEIVE_CODE      VARCHAR2(50);
V_SEND_STATE        VARCHAR2(1);
V_SENDERMAIL        VARCHAR2(100);
V_CHECK_ORGCODE     VARCHAR2(30);
V_REG_CN            VARCHAR2(100);
V_REG_NO            VARCHAR2(100);
V_REG_DATE          VARCHAR2(30);
V_CONTRACT_NO       VARCHAR2(100);
type TYPE_CUR is ref cursor;
v_cursor TYPE_CUR ;
v_msg               varchar2(500);
begin

select min(seq_num) into v_num_begin from fee_bill_middle;
select max(seq_num) into v_num_end from fee_bill_middle;

loop

if v_num_begin <= v_num_end
then open v_cursor for
     select seq_num,corp_code,password,module_id,bill_id,bill_no,receive_code,send_state,
            sendermail,check_orgcode,reg_cn,reg_no,reg_date,contract_no
     from fee_bill_middle t
     where t.seq_num >=  v_num_begin
     and   t.seq_num < v_num_begin+100
     and   t.seq_num <= v_num_end;

     loop
     fetch v_cursor into v_seq_num,V_CORP_CODE,V_PASSWORD,V_MODULE_ID,V_BILL_ID,V_BILL_NO,V_RECEIVE_CODE,
                         V_SEND_STATE,V_SENDERMAIL,V_CHECK_ORGCODE,V_REG_CN,V_REG_NO,V_REG_DATE,V_CONTRACT_NO;

     exit when v_cursor%notfound;

     select POSTBILLDATAV3(V_CORP_CODE,V_PASSWORD,V_MODULE_ID,V_BILL_ID,V_BILL_NO,V_RECEIVE_CODE,V_SEND_STATE,
                           V_SENDERMAIL,V_CHECK_ORGCODE,V_REG_CN,V_REG_NO,V_REG_DATE,V_CONTRACT_NO)
     into v_msg from dual;
    
     delete from fee_bill_middle where seq_num=v_seq_num;
     commit;
    
     end loop;
     close v_cursor;
    
     v_num_begin:=v_num_begin+100;
else
     exit;
end if;
end loop;
 
end pro_postbill;

end Pkg_SYN_TO_SISS;
/

spool off