create or replace procedure save_namic(dydispatchids in varchar2,
businessRegistrationname in varchar2,
state in varchar2,
certstatusoperator1 in varchar2,
types in varchar2,
dept in varchar2,
province1 in varchar2,
city1 in varchar2,
area1 in varchar2,
certttype in varchar2,
bfb in int
) is
cunt number (20):=0;
dydisType varchar2(20):='';
issuedType varchar2(40):='';
barcode varchar2(50):='';
msg varchar2(20);
busType varchar2(20) :='01';
cursor baseinfo is
select * from (select * from
(
select tn.id,
tn.name,
tn.REGIST_PROVINCE,
tn.REGIST_CITY,
tn.REGIST_AREA,
tn.REGIST_ROAD,
tn.REGIST_VILLAGE,
tn.REGIST_POSTCODE,
tn.PROVINCE,
tn.CITY,
tn.AREA,
tn.ROAD,
tn.VILLAGE,
tn.POSTCODE,
tn.ECONOMIC_TYPE as BusLicence,
tn.TYPE,
tn.gmt_setup,
tn.phone,
tn.tax,
tn.site_url,
tn.email,
tn.legal_person,
tn.position,
tn.title,
tn.manager,
tn.manager_position,
tn.manager_title,
tn.tech_director,
tn.tech_position,
tn.tech_title,
tn.id as user_id,
tn.certstatusoperator,
tn.certtype,
tn.certid,
tn.SUPERIOR_DEPT as superiordept
from (select n.*, tb.enterpriseid, t.cert_status as certstatus, t.cert_status_operator as certstatusoperator,t.cert_type as certtype,t.id as certid
from t_en_arc_baseinfo@bmrpdblink n
left join t_en_arc_qualificationcert@bmrpdblink t on t.enterprise_id = n.id
left join t_gov_bmmd_dy_enlist tb on tb.enterpriseid = n.id
and tb.dydispatchid =
dydispatchids) tn
where tn.enterpriseid is null
AND tn.name like '%' || businessRegistrationname || '%'
AND tn.certstatus = state
AND ((certstatusoperator1 is null) or (certstatusoperator1 is not null and tn.certstatusoperator = certstatusoperator1))
and ((types='19'and tn.regtype='outer') or (types<>'19'and tn.regType is null))
and ((types='01'and tn.certtype like certttype||'%' and tn.certtype<>'01-03') or (types<>'01'and tn.certtype like certttype ||'%'))
and ((dept is null) or (dept is not null and tn.SUPERIOR_DEPT=dept))
and ((province1 is null) or (province1 is not null and tn.regist_province=province1))
and ((city1 is null) or (city1 is not null and tn.regist_city=city1))
and ((area1 is null) or (area1 is not null and tn.regist_area=area1))
)
order by dbms_random.value)
where ((bfb is null) or (bfb is not null and rownum<round(bfb))) ;
sys_id varchar2(50);
begin
select h.type into dydisType from t_gov_bmmd_dy_dispatch h where h.id=dydispatchids;
for cur_1 in baseinfo loop
select sys_guid() into sys_id from dual;
select create_barcode(busType) into barcode from dual;
--插入申请表信息
Insert into t_en_app_dy_application (
id,enid,enname,status,barcode
) values (sys_id,cur_1.id,cur_1.name,'01',barcode);
--插入基本信息
insert into t_en_app_dy_baseinfo (
id,
name,
REGIST_PROVINCE,
REGIST_CITY,
REGIST_AREA,
REGIST_ROAD,
REGIST_VILLAGE,
REGIST_POSTCODE,
PROVINCE,
CITY,
AREA,
ROAD,
VILLAGE,
POSTCODE,
BusLicence,
TYPE,
gmt_setup,
phone,
tax,
site_url,
email,
legal_person,
position,
title,
manager,
manager_position,
manager_title,
tech_director,
tech_position,
tech_title,
superiordept,
appid
)values(
sys_guid(),
cur_1.name,
cur_1.REGIST_PROVINCE,
cur_1.REGIST_CITY,
cur_1.REGIST_AREA,
cur_1.REGIST_ROAD,
cur_1.REGIST_VILLAGE,
cur_1.REGIST_POSTCODE,
cur_1.PROVINCE,
cur_1.CITY,
cur_1.AREA,
cur_1.ROAD,
cur_1.VILLAGE,
cur_1.POSTCODE,
cur_1.BusLicence,
cur_1.TYPE,
cur_1.gmt_setup,
cur_1.phone,
cur_1.tax,
cur_1.site_url,
cur_1.email,
cur_1.legal_person,
cur_1.position,
cur_1.title,
cur_1.manager,
cur_1.manager_position,
cur_1.manager_title,
cur_1.tech_director,
cur_1.tech_position,
cur_1.tech_title,
cur_1.superiordept,
sys_id
);
--动态下发表信息
insert into t_gov_bmmd_dy_enlist (
id,dydispatchid,appdylicationid,enterpriseid,entname,enterprisetype,type,issuedtype,superiordept,state
)values(sys_guid(),dydispatchids,sys_id,cur_1.id,cur_1.name,cur_1.type,dydisType,cur_1.certtype,cur_1.superiordept,'01');
--插入证书和资质信息
save_certr(cur_1.certid,sys_id);
end loop;
commit;
null;
end;
businessRegistrationname in varchar2,
state in varchar2,
certstatusoperator1 in varchar2,
types in varchar2,
dept in varchar2,
province1 in varchar2,
city1 in varchar2,
area1 in varchar2,
certttype in varchar2,
bfb in int
) is
cunt number (20):=0;
dydisType varchar2(20):='';
issuedType varchar2(40):='';
barcode varchar2(50):='';
msg varchar2(20);
busType varchar2(20) :='01';
cursor baseinfo is
select * from (select * from
(
select tn.id,
tn.name,
tn.REGIST_PROVINCE,
tn.REGIST_CITY,
tn.REGIST_AREA,
tn.REGIST_ROAD,
tn.REGIST_VILLAGE,
tn.REGIST_POSTCODE,
tn.PROVINCE,
tn.CITY,
tn.AREA,
tn.ROAD,
tn.VILLAGE,
tn.POSTCODE,
tn.ECONOMIC_TYPE as BusLicence,
tn.TYPE,
tn.gmt_setup,
tn.phone,
tn.tax,
tn.site_url,
tn.email,
tn.legal_person,
tn.position,
tn.title,
tn.manager,
tn.manager_position,
tn.manager_title,
tn.tech_director,
tn.tech_position,
tn.tech_title,
tn.id as user_id,
tn.certstatusoperator,
tn.certtype,
tn.certid,
tn.SUPERIOR_DEPT as superiordept
from (select n.*, tb.enterpriseid, t.cert_status as certstatus, t.cert_status_operator as certstatusoperator,t.cert_type as certtype,t.id as certid
from t_en_arc_baseinfo@bmrpdblink n
left join t_en_arc_qualificationcert@bmrpdblink t on t.enterprise_id = n.id
left join t_gov_bmmd_dy_enlist tb on tb.enterpriseid = n.id
and tb.dydispatchid =
dydispatchids) tn
where tn.enterpriseid is null
AND tn.name like '%' || businessRegistrationname || '%'
AND tn.certstatus = state
AND ((certstatusoperator1 is null) or (certstatusoperator1 is not null and tn.certstatusoperator = certstatusoperator1))
and ((types='19'and tn.regtype='outer') or (types<>'19'and tn.regType is null))
and ((types='01'and tn.certtype like certttype||'%' and tn.certtype<>'01-03') or (types<>'01'and tn.certtype like certttype ||'%'))
and ((dept is null) or (dept is not null and tn.SUPERIOR_DEPT=dept))
and ((province1 is null) or (province1 is not null and tn.regist_province=province1))
and ((city1 is null) or (city1 is not null and tn.regist_city=city1))
and ((area1 is null) or (area1 is not null and tn.regist_area=area1))
)
order by dbms_random.value)
where ((bfb is null) or (bfb is not null and rownum<round(bfb))) ;
sys_id varchar2(50);
begin
select h.type into dydisType from t_gov_bmmd_dy_dispatch h where h.id=dydispatchids;
for cur_1 in baseinfo loop
select sys_guid() into sys_id from dual;
select create_barcode(busType) into barcode from dual;
--插入申请表信息
Insert into t_en_app_dy_application (
id,enid,enname,status,barcode
) values (sys_id,cur_1.id,cur_1.name,'01',barcode);
--插入基本信息
insert into t_en_app_dy_baseinfo (
id,
name,
REGIST_PROVINCE,
REGIST_CITY,
REGIST_AREA,
REGIST_ROAD,
REGIST_VILLAGE,
REGIST_POSTCODE,
PROVINCE,
CITY,
AREA,
ROAD,
VILLAGE,
POSTCODE,
BusLicence,
TYPE,
gmt_setup,
phone,
tax,
site_url,
email,
legal_person,
position,
title,
manager,
manager_position,
manager_title,
tech_director,
tech_position,
tech_title,
superiordept,
appid
)values(
sys_guid(),
cur_1.name,
cur_1.REGIST_PROVINCE,
cur_1.REGIST_CITY,
cur_1.REGIST_AREA,
cur_1.REGIST_ROAD,
cur_1.REGIST_VILLAGE,
cur_1.REGIST_POSTCODE,
cur_1.PROVINCE,
cur_1.CITY,
cur_1.AREA,
cur_1.ROAD,
cur_1.VILLAGE,
cur_1.POSTCODE,
cur_1.BusLicence,
cur_1.TYPE,
cur_1.gmt_setup,
cur_1.phone,
cur_1.tax,
cur_1.site_url,
cur_1.email,
cur_1.legal_person,
cur_1.position,
cur_1.title,
cur_1.manager,
cur_1.manager_position,
cur_1.manager_title,
cur_1.tech_director,
cur_1.tech_position,
cur_1.tech_title,
cur_1.superiordept,
sys_id
);
--动态下发表信息
insert into t_gov_bmmd_dy_enlist (
id,dydispatchid,appdylicationid,enterpriseid,entname,enterprisetype,type,issuedtype,superiordept,state
)values(sys_guid(),dydispatchids,sys_id,cur_1.id,cur_1.name,cur_1.type,dydisType,cur_1.certtype,cur_1.superiordept,'01');
--插入证书和资质信息
save_certr(cur_1.certid,sys_id);
end loop;
commit;
null;
end;