create or replace trigger addCust
before insert on bd_cubasdoc
for each row
declare
-- local variables here
pk_bas char(20);
corp char(4);
/*查询目标公司目录*/
cursor bdcorp is
select pk_corp
from ncv5.bd_corp /*目标数据库表*/
where nvl(dr, 0) = 0
and pk_corp <> '0001';
/*查询目标公司是否存在基本档案*/
cursor basjob(id char) is
select pk_cubasdoc
from ncv5.bd_cubasdoc
where pk_cubasdoc = id
and nvl(dr, 0) = 0;
begin
/*判断目标公司数据是否存在*/
/* dbms_output.put_line('aaa');
select pk_cubasdoc into pk_bas from bd_cubasdoc1\*目标数据库表*\ where pk_cubasdoc=:new.pk_cubasdoc and nvl(dr,0)=0;*/
open basjob(:new.pk_cubasdoc);
loop
fetch basjob
into pk_bas;
exit when basjob%notfound;
end loop;
close basjob;
-- dbms_output.put_line('ddd');
/*插入目标基本档案数据*/
if pk_bas is null then
begin
/*--目标基本档案表/*目标数据库表*/
insert into ncv5.bd_cubasdoc
(pk_cubasdoc,
pk_corp,
custcode,
custname,
custshortname,
engname,
mnecode,
trade,
freecustflag,
drpnodeflag,
isconnflag,
pk_cubasdoc1,
custprop,
pk_areacl,
pk_corp1,
taxpayerid,
legalbody,
creditmny,
ecotypesincevfive,
saleaddr,
conaddr,
zipcode,
phone1,
phone2,
phone3,
fax1,
fax2,
linkman1,
linkman2,
linkman3,
bp1,
bp2,
bp3,
mobilephone1,
mobilephone2,
mobilephone3,
email,
url,
def1,
def2,
def3,
def4,
def5,
def6,
def7,
def8,
def9,
def10,
def11,
def12,
def13,
def14,
def15,
def16,
def17,
def18,
def19,
def20,
registerfund,
sealflag,
memo,
pk_pricegroup,
correspondunit,
ts,
dr)
values
(:new.pk_cubasdoc,
:new.pk_corp,
:new.custcode,
:new.custname,
:new.custshortname,
:new.engname,
:new.mnecode,
:new.trade,
:new.freecustflag,
:new.drpnodeflag,
:new.isconnflag,
:new.pk_cubasdoc1,
:new.custprop,
:new.pk_areacl,
:new.pk_corp1,
:new.taxpayerid,
:new.legalbody,
:new.creditmny,
:new.ecotypesincevfive,
:new.saleaddr,
:new.conaddr,
:new.zipcode,
:new.phone1,
:new.phone2,
:new.phone3,
:new.fax1,
:new.fax2,
:new.linkman1,
:new.linkman2,
:new.linkman3,
:new.bp1,
:new.bp2,
:new.bp3,
:new.mobilephone1,
:new.mobilephone2,
:new.mobilephone3,
:new.email,
:new.url,
:new.def1,
:new.def2,
:new.def3,
:new.def4,
:new.def5,
:new.def6,
:new.def7,
:new.def8,
:new.def9,
:new.def10,
:new.def11,
:new.def12,
:new.def13,
:new.def14,
:new.def15,
:new.def16,
:new.def17,
:new.def18,
:new.def19,
:new.def20,
:new.registerfund,
:new.sealflag,
:new.memo,
:new.pk_pricegroup,
:new.correspondunit,
:new.ts,
:new.dr);
--dbms_output.put_line('ccc');
/*插入目标管理档案数据*/
open bdcorp;
loop
fetch bdcorp
into corp;
exit when bdcorp%notfound;
-- dbms_output.put_line(:new.pk_cubasdoc);
/*管理档案有两条数据，插入两次*/
insert into ncv5.bd_cumandoc /*目标数据库表*/
(pk_cumandoc,
pk_corp,
pk_cubasdoc,
custflag,
linkman,
bp,
mobilephone,
pk_defbusitype,
frozenflag,
frozendate,
discountrate,
creditlevel,
creditmny,
creditlimitnum,
accawmny,
busawmny,
ordawmny,
pk_respdept1,
pk_resppsn1,
diffcurrflag,
developdate,
credlimitflag,
pk_currtype1,
pk_cusmandoc2,
pk_cusmandoc3,
pk_sendtype,
pk_stordoc2,
def1,
def2,
def3,
def4,
def5,
def6,
def7,
def8,
def9,
def10,
def11,
def12,
def13,
def14,
def15,
def16,
def17,
def18,
def19,
def20,
def21,
def22,
def23,
def24,
def25,
def26,
def27,
def28,
def29,
def30,
memo,
pk_payterm,
cooperateflag,
creditmoney,
testsalemoney,
pk_salestru,
pk_calbody,
iounit,
ratifydate,
sealflag,
custstate,
pk_pricegroupcorp,
freeofcremnycheck,
freeofacclmtcheck,
balancemny,
acclimit,
acclmtbegindate,
cmnecode,
grade,
cooperatingdayfrom,
cooperatingdayto,
correspsettleunit,
pk_settleunit,
innerctldays,
ispromtesettlement,
stockpriceratio,
ts,
dr)
values
('A100' ||corp|| substr(:new.pk_cubasdoc,9, 20),
corp /*公司*/,
:new.pk_cubasdoc,
'2',
null,
null,
null,
null,
'N',
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
'N',
null,
0,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
'N',
null,
null,
null,
null,
'N',
null,
null,
0,
null,
'N',
'N',
null,
30,
null,
null,
0,
null,
null,
null,
null,
null,
'Y',
100,
sysdate,
0);
insert into ncv5.bd_cumandoc /*目标数据库表*/
(pk_cumandoc,
pk_corp,
pk_cubasdoc,
custflag,
linkman,
bp,
mobilephone,
pk_defbusitype,
frozenflag,
frozendate,
discountrate,
creditlevel,
creditmny,
creditlimitnum,
accawmny,
busawmny,
ordawmny,
pk_respdept1,
pk_resppsn1,
diffcurrflag,
developdate,
credlimitflag,
pk_currtype1,
pk_cusmandoc2,
pk_cusmandoc3,
pk_sendtype,
pk_stordoc2,
def1,
def2,
def3,
def4,
def5,
def6,
def7,
def8,
def9,
def10,
def11,
def12,
def13,
def14,
def15,
def16,
def17,
def18,
def19,
def20,
def21,
def22,
def23,
def24,
def25,
def26,
def27,
def28,
def29,
def30,
memo,
pk_payterm,
cooperateflag,
creditmoney,
testsalemoney,
pk_salestru,
pk_calbody,
iounit,
ratifydate,
sealflag,
custstate,
pk_pricegroupcorp,
freeofcremnycheck,
freeofacclmtcheck,
balancemny,
acclimit,
acclmtbegindate,
cmnecode,
grade,
cooperatingdayfrom,
cooperatingdayto,
correspsettleunit,
pk_settleunit,
innerctldays,
ispromtesettlement,
stockpriceratio,
ts,
dr)
values
('A200' ||corp|| substr(:new.pk_cubasdoc,9,20),
corp /*公司*/,
:new.pk_cubasdoc,
'3',
null,
null,
null,
null,
'N',
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
'N',
null,
0,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
'N',
null,
null,
null,
null,
'N',
null,
null,
0,
null,
'N',
'N',
null,
30,
null,
:new.mnecode,
0,
null,
null,
null,
null,
null,
'Y',
100,
sysdate,
0);
end loop;
close bdcorp;
end;
end if;
end addcust;
浙公网安备 33010602011771号