-----------------------------------------------------
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
扫一扫
666
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
