这是出错的存储过程create or replace procedure sp_in_duo_info ( duono in varchar2 ,arr_no out varchar2 , returnValue out integer ) As --PLC上报0019命令后调用 --返回值(returnValue) 1 正常校验 -- 2 插队 -- 3 合格空箱 -- 4 不合格空箱 -- 5 回库 currenttaskno Varchar2(32); rowcnt integer; v_pile_no varchar(256); v_mid_cnt integer; v_sms_cnt integer; v_mpac_mode varchar(8); begin returnValue:=5; rowcnt:=-1; arr_no := ''; select count(*) into rowcnt from para_info where modecode='mainframe' and namecode='mpacmode'; if rowcnt<1 then v_mpac_mode:='立库'; else select showstring into v_mpac_mode from para_info where modecode='mainframe' and namecode='mpacmode'; end if;if v_mpac_mode='平库' then setequipdetmanu('',duono,rowcnt); end if; select count(*) into rowcnt from mid_meter_info where boxno=duono and flag=-1; if rowcnt<1 then select count(*) into rowcnt from box_info where box_no=duono; if rowcnt<1 then sp_insert_error_info('sp_in_duo_info',duono,'box_info中没有该垛的信息'); else returnValue:=1; end if; else select pileno,taskno into v_pile_no,currenttaskno from mid_meter_info where boxno=duono and flag=-1 and rownum=1; select count(*) into v_sms_cnt from mid_meter_info where pileno=v_pile_no and taskno=currenttaskno and flag=-1; Select Count(*) Into v_mid_cnt From MT_DETECT_OUT_EQUIP@Linktompac Where Detect_Task_No=currenttaskno And PILE_NO=v_pile_no; if v_sms_cnt<>v_mid_cnt then sp_insert_error_info('sp_in_duo_info',duono,'jk_mid_chk_work_equip中没有该垛的信息'); else --更新任务数量 select count(*) into rowcnt from mid_meter_info where taskno=currenttaskno; update Task_Cnt_info set in_line_cnt=rowcnt where TaskID=currenttaskno; update app_no set havenum=havenum+1 where app_no=currenttaskno; arr_no:=currenttaskno; returnValue:=1; if v_mpac_mode='平库' then applyequipsucess(currenttaskno,1,rowcnt); end if; end if; end if; return; --以下是异常处理 Exception When Dup_Val_on_index then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'违反了唯一性限制'); return; end; When Timeout_on_resource then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'在等待资源时发生超时'); return; end; When Invalid_CURSOR then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'试图使用一个无效的游标'); return; end; When Not_logged_on then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'没有连接到ORACLE'); return; end; When Login_denied then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'无效的用户名/口令'); return; end; When No_data_found then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'SELECT INTO没有找到数据'); return; end; When Too_many_rows then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'SELECT INTO 返回多行'); return; end; When Zero_divide then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'试图被零除'); return; end; When Invalid_NUMBER then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'转换一个数字失败'); return; end; When Storage_error then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'内存不够引发的内部错误'); return; end; When Program_error then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'内部错误'); return; end; When Value_error then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'转换或截断错误'); return; end; When Rowtype_mismatch then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'宿主游标变量与 PL/SQL变量有不兼容行类型'); return; end; When CURSOR_already_OPEN then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'试图打开一个已处于打开状态的游标'); return; end; When Access_INTO_null then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'试图为null 对象的属性赋值'); return; end; When Collection_is_null then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'试图将Exists 以外的集合( collection)方法应用于一个null pl/sql表上或varray上'); return; end; When Subscript_outside_limit then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'对嵌套或varray索引得引用超出声明范围以外'); return; end; When Subscript_beyond_count then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'对嵌套或varray 索引得引用大于集合中元素的个数'); return; end; When others then begin sp_insert_error_info('sp_in_duo_info','错误代码:'||'错误代码:'||SQLCODE,'上传数据出现异常'); return; end; end;
这是中间被调用的存储过程create or replace procedure setEquipDETManu --手动下载表明细 ( v_Detect_Task_No In Varchar2 ,v_box_no in VARCHAR2 , V_returnValue Out Number ) as V_Count number(8,0); v_count_tmp number(8,0); v_pile_no varchar2(256); v_con_code varchar2(8); v_task_no varchar2(32); Begin v_count:=0; V_Returnvalue:=0; Select Count(*) Into V_Count From MT_DETECT_OUT_EQUIP@Linktompac Where box_bar_code=v_box_no; If V_Count>0 THEN V_Count:=0; Select PILE_NO,detect_task_no Into V_PILE_NO,v_task_no From MT_DETECT_OUT_EQUIP@Linktompac Where box_bar_code=v_box_no and rownum=1; select count(*) into v_count from m_mid_tasks where detect_task_no=v_task_no; if v_count<1 then sp_insert_error_info ('setEquipDETManu',v_box_no,'该垛表无检定任务'); return; end if; delete from mid_meter_info where taskno=v_Detect_Task_No and pileno=V_PILE_NO and flag=-1; INSERT INTO Mid_Meter_Info ( MeterId, TaskNo, MeterNo, PileNo, BoxNo, Bindtime, Flag) Select Concat(To_Char(Sysdate, 'yyyymmddhh24mmss'), Substr(Bar_Code,length(Bar_Code)-17,18)), Detect_Task_No, Bar_Code, Pile_No, Box_Bar_Code, Sysdate, -1 From MT_DETECT_OUT_EQUIP@Linktompac Where pile_no=V_PILE_NO;IF SQL%Rowcount<1 THEN V_Count:=-1; END IF; select count(*) into v_count_tmp from meter_param where taskno=v_Detect_Task_No; if v_count_tmp<1 then INSERT INTO METER_PARAM ( Meterid ,Taskno ,GROUPID ,METERKIND --电表类型 1机械表/2电子表/3电子表多功能表 ,DIRECTCONNECT--是否直接接入 1/是直接接入 ,METERCONVERT --有无止逆器 1/有 ,TRIPHASE --单三相 1单相 2三相四线 3三相三线 ,POWERKIND --有无功 1有功 2无功 3有无功一体 ,CONNECTIONTYPE ,METERTYPE --电表型号 ,METERTYPENAME ,MANUFACTORY ,VOLTAGE ,BASECURRENT ,INCURRENT ,MAXCURRENT ,FREQUENCY ,CLASSGRADE ,CLASSGRADEREACTIVE ,PERIODOFVALIDITY ,CHECKREGULATION ,CHECKERNAME ,AUDITERNAME ,MANAGERNAME ,Mulmeterpulsepolarity ,MULMETERPROTOCOL ,MULMETERLOGINNAME ,MULMETERWRITEPASSWORD ,Mulmeterdemandpassword ,MULMETERFREQ ,Mulmeterdemandcyc ,Mulmeterdemandslide ,CARRIERWAVETYPE ,MULBAUDRATE ,MULPARITY ,MULDATABITS ,MULSTOPBITS ,Metercard ,Verfied ,Contactbreaker ,Contactbreakertpye ,GENERALNUMBER ,MANUFACTORYNUMBER ,MANUFACTORYDATE ,CONSTUNIT ,CONSTUNITREACTIVE ,MULMETERADDRESS ) SELECT m.detect_task_no, m.detect_task_no, T.Arrive_Batch_No,--到货批次号 3,-- T.Type_Code ,--01:三相智能电能表、02:三相费控智能表(无线)、03:三相费控智能表(载波)、04:单相本地费控表、05:单相远程费控表、06:01干式、07:浇注式、08:油浸式、… CASE t.CON_MODE WHEN '01' THEN 1 ELSE 0 END,--01:直接接入、02:经互感器接入 CASE t.CC_PREVENT_FLAG WHEN '1' THEN 1 ELSE 0 END,--有无止逆器 1/有 CASE t.WIRING_MODE WHEN '1' THEN 1 WHEN '2' THEN 3 WHEN '3' THEN 2 ELSE 1 END ,--电能表的接线方式引用国家电网公司营销管理代码类集:5110.84电能表接线方式分类与代码CASE t.READING_TYPE_CODE WHEN '06' THEN 1 WHEN '04' THEN 1 WHEN '07' THEN 2 ELSE 3 END ,--01:最大需量+正向有功及分时+反向有功及分时+无功四象限、02:最大需量+正向有功及分时+无功感容、03:正向有功及分时+无功感容、04:正向有功及分时、05:正向有功+无功感容、06:有功、07:无功 CASE t.WIRING_MODE WHEN '01' THEN 1 WHEN '02' THEN 6 WHEN '03' THEN 3 WHEN '04' THEN 9 ELSE 1 END,--接线方式meterWireType 1单相有功 2单相无功 3三相四线有功 4三相四线无功 5三相四线90度无功 6三相三线有功 7三相三线无功 8三相三线无功方式2 9三相三线60度无功 10三相三线90度无功 Case T.MODEL_CODE When '' Then 'DTSD' Else(Select Name From MT_P_Code@Linktompac Where Code_Type='meterModelNo' And Value=T.MODEL_CODE) End, ----设'备型号 meterModelNo '',--电表全 Case T.MANUFACTURER When '' Then 'sms' Else(Select Name From MT_P_Code@Linktompac Where Code_Type='meterFacturer' And Value=T.MANUFACTURER) End, -----生产厂家 CASE t.VOLT_CODE WHEN '01' THEN 220 --01:220V、02:3x380、03:3x380/220V、04:3x100V、05:3x57.7/100V WHEN '02' THEN 380 WHEN '03' THEN 220 WHEN '04' THEN 100 WHEN '05' THEN 57.7 Else 57.7 End, CASE t.RATED_CURRENT WHEN '9984' THEN 1 WHEN '9985' THEN 1 WHEN '9986' THEN 2 WHEN '9987' THEN 2.5 WHEN '9989' THEN 0.3 WHEN '9990' THEN 15 WHEN '9991' THEN 30 WHEN '9992' THEN 40 WHEN '9993' THEN 40 WHEN '9994' THEN 50 WHEN '9995' THEN 80 WHEN '9996' THEN 15 WHEN '9997' THEN 1 WHEN '9998' THEN 3 WHEN '9999' THEN 4 Else 1 End, --基本电流 meterRcSort CASE t.RATED_CURRENT WHEN '9984' THEN 1 WHEN '9985' THEN 1 WHEN '9986' THEN 2 WHEN '9987' THEN 2.5 WHEN '9989' THEN 0.3 WHEN '9990' THEN 15 WHEN '9991' THEN 30 WHEN '9992' THEN 40 WHEN '9993' THEN 40 WHEN '9994' THEN 50 WHEN '9995' THEN 80 WHEN '9996' THEN 15 WHEN '9997' THEN 1 WHEN '9998' THEN 3 WHEN '9999' THEN 4 ELSE 1 END, --标定电流 CASE t.RATED_CURRENT WHEN '9984' THEN 1 WHEN '9985' THEN 2 WHEN '9986' THEN 4 WHEN '9987' THEN 15 WHEN '9989' THEN 1.2 WHEN '9990' THEN 60 WHEN '9991' THEN 100 WHEN '9992' THEN 80 WHEN '9993' THEN 100 WHEN '9994' THEN 100 WHEN '9995' THEN 120 WHEN '9996' THEN 100 WHEN '9997' THEN 6 WHEN '9998' THEN 5 WHEN '9999' THEN 6 ELSE 1 END,--最大电流 t.FREQ_CODE, Case T.AP_PRE_LEVEL_CODE When '' Then '1' Else(Select Name From MT_P_Code@Linktompac Where Code_Type='stddevPreCode' And Value=T.AP_PRE_LEVEL_CODE) End, --有功精度 stddevPreCodeCase T.RP_PRE_LEVEL_CODE When '' Then '1' Else(Select Name From MT_P_Code@Linktompac Where Code_Type='stddevPreCode' And Value=T.RP_PRE_LEVEL_CODE) End, ----无功精度 stddevPreCode'',--电表有效期 '',--校验依据 '',--校验人员 '',--审核人员 '',--主管人员 '',--多功能表脉冲极性选择 0共阴 1 共阳 CASE t.COMM_PROT_CODE WHEN '01' THEN 1 WHEN '02' THEN 2 ELSE 1 END,--多功能表协议 01:645-07规约、02:97规约 '',--多功能表登陆名 '',--多功能表写表密码 '',--多功能表清需量密码 '',--多功能表时钟频率 '',--多功能表需量周期(分) '',--多功能表最大需量滑差时间(分 case t.chip_manufacturer when '04' then 3 when '01' then 2 when '03' then 1 when '02' then 4 else 5 end,--载波通讯转接器厂家 1 东软 2晓程 3鼎信 4瑞斯康 5力合微 6弥亚微 7埃软 8埃施朗 CASE t.BAUDRATE_CODE WHEN '' THEN '1200' ELSE (Select NAME From MT_P_Code@Linktompac Where Code_Type='BAUDRATE' And Value=t.BAUDRATE_CODE) END,--多功能通讯波特率 '',--多功能奇偶校验位 8,--t.METER_DIGITS,--多功能数据位 '',--多功能数据停止位 '',--是否为卡表 '1',--检定类型 1首检 2周检 3抽检 4二次抽检 5复合性检定 '',--电流断触器 1为内置 2为外置 CASE t.RELAY_JOINT WHEN '01' THEN 1 WHEN '02' THEN 2 Else 3 End,--电流断触器接入点类型 1为常闭 ,2为常开称 01:常开、 02:常闭、03:常开+常闭 '',--局编号 t.MADE_NO, T.Made_Date, Case T.CONST_CODE When '' Then '400' Else(Select Name From MT_P_Code@Linktompac Where Code_Type='meterConstCode' And Value=T.CONST_CODE) End, ----无功精度 stddevPreCode Case T.RP_CONSTANT When '' Then '400' Else(Select Name From MT_P_Code@Linktompac Where Code_Type='meterConstCode' And Value=T.RP_CONSTANT) End, ----无功精度 stddevPreCode '' From MT_METER@Linktompac t,m_mid_tasks m Where t.arrive_batch_no=m.arrive_batch_no and m.detect_task_no=v_task_no and rownum=1; end if; --插入箱信息select case DIRECTCONNECT when 1 then '01' else '02' end into v_con_code from meter_param where taskno=v_task_no and rownum=1; if nvl(v_con_code,'9999')='9999' then sp_insert_error_info ('setEquipDETManu',v_task_no,'meter_param无该任务的参数信息'); end if; delete from box_info where box_no in (select boxno from mid_meter_info where taskno=v_task_no and pileno=V_PILE_NO and flag=-1); insert into box_info (box_no,box_type,box_model,box_status,subtime) select boxno,'01',v_con_code,1,sysdate from mid_meter_info where taskno=v_task_no and pileno=V_PILE_NO and flag=-1 group by boxno; IF SQL%Rowcount<1 THEN V_Count:=-3; sp_insert_error_info ('setEquipDETManu',V_PILE_NO,'插入数据到表box_info失败'); end if;IF V_Count<0 THEN sp_insert_error_info ('setEquipDETManu',V_Count,V_Count); END IF;Else V_returnValue :=-3 ;--表资产下载失败 sp_insert_error_info ('setEquipDETManu',V_Count,'-3 表资产下载失败'); end if; Exception when others then sp_insert_error_info ('setEquipDETManu','others','others'); End;
(
duono in varchar2
,arr_no out varchar2
, returnValue out integer
) As
--PLC上报0019命令后调用
--返回值(returnValue) 1 正常校验
-- 2 插队
-- 3 合格空箱
-- 4 不合格空箱
-- 5 回库
currenttaskno Varchar2(32);
rowcnt integer;
v_pile_no varchar(256);
v_mid_cnt integer;
v_sms_cnt integer;
v_mpac_mode varchar(8);
begin
returnValue:=5;
rowcnt:=-1;
arr_no := '';
select count(*) into rowcnt from para_info where modecode='mainframe' and namecode='mpacmode';
if rowcnt<1 then
v_mpac_mode:='立库';
else
select showstring into v_mpac_mode from para_info where modecode='mainframe' and namecode='mpacmode';
end if;if v_mpac_mode='平库' then
setequipdetmanu('',duono,rowcnt);
end if;
select count(*) into rowcnt from mid_meter_info where boxno=duono and flag=-1;
if rowcnt<1 then
select count(*) into rowcnt from box_info where box_no=duono;
if rowcnt<1 then
sp_insert_error_info('sp_in_duo_info',duono,'box_info中没有该垛的信息');
else
returnValue:=1;
end if;
else
select pileno,taskno into v_pile_no,currenttaskno from mid_meter_info where boxno=duono and flag=-1 and rownum=1;
select count(*) into v_sms_cnt from mid_meter_info where pileno=v_pile_no and taskno=currenttaskno and flag=-1;
Select Count(*) Into v_mid_cnt From MT_DETECT_OUT_EQUIP@Linktompac Where Detect_Task_No=currenttaskno And PILE_NO=v_pile_no;
if v_sms_cnt<>v_mid_cnt then
sp_insert_error_info('sp_in_duo_info',duono,'jk_mid_chk_work_equip中没有该垛的信息');
else
--更新任务数量
select count(*) into rowcnt from mid_meter_info where taskno=currenttaskno;
update Task_Cnt_info set in_line_cnt=rowcnt where TaskID=currenttaskno;
update app_no set havenum=havenum+1 where app_no=currenttaskno;
arr_no:=currenttaskno;
returnValue:=1;
if v_mpac_mode='平库' then
applyequipsucess(currenttaskno,1,rowcnt);
end if;
end if;
end if;
return;
--以下是异常处理
Exception
When Dup_Val_on_index then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'违反了唯一性限制');
return;
end;
When Timeout_on_resource then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'在等待资源时发生超时');
return;
end;
When Invalid_CURSOR then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'试图使用一个无效的游标');
return;
end;
When Not_logged_on then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'没有连接到ORACLE');
return;
end;
When Login_denied then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'无效的用户名/口令');
return;
end;
When No_data_found then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'SELECT INTO没有找到数据');
return;
end;
When Too_many_rows then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'SELECT INTO 返回多行');
return;
end;
When Zero_divide then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'试图被零除');
return;
end;
When Invalid_NUMBER then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'转换一个数字失败');
return;
end;
When Storage_error then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'内存不够引发的内部错误');
return;
end;
When Program_error then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'内部错误');
return;
end;
When Value_error then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'转换或截断错误');
return;
end;
When Rowtype_mismatch then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'宿主游标变量与 PL/SQL变量有不兼容行类型');
return;
end;
When CURSOR_already_OPEN then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'试图打开一个已处于打开状态的游标');
return;
end;
When Access_INTO_null then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'试图为null 对象的属性赋值');
return;
end;
When Collection_is_null then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'试图将Exists 以外的集合( collection)方法应用于一个null pl/sql表上或varray上');
return;
end;
When Subscript_outside_limit then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'对嵌套或varray索引得引用超出声明范围以外');
return;
end;
When Subscript_beyond_count then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||SQLCODE,'对嵌套或varray 索引得引用大于集合中元素的个数');
return;
end;
When others then
begin
sp_insert_error_info('sp_in_duo_info','错误代码:'||'错误代码:'||SQLCODE,'上传数据出现异常');
return;
end;
end;
--手动下载表明细
(
v_Detect_Task_No In Varchar2
,v_box_no in VARCHAR2
, V_returnValue Out Number
) as
V_Count number(8,0);
v_count_tmp number(8,0);
v_pile_no varchar2(256);
v_con_code varchar2(8);
v_task_no varchar2(32);
Begin
v_count:=0;
V_Returnvalue:=0;
Select Count(*) Into V_Count From MT_DETECT_OUT_EQUIP@Linktompac Where box_bar_code=v_box_no;
If V_Count>0 THEN
V_Count:=0;
Select PILE_NO,detect_task_no Into V_PILE_NO,v_task_no From MT_DETECT_OUT_EQUIP@Linktompac Where box_bar_code=v_box_no and rownum=1;
select count(*) into v_count from m_mid_tasks where detect_task_no=v_task_no;
if v_count<1 then
sp_insert_error_info ('setEquipDETManu',v_box_no,'该垛表无检定任务');
return;
end if;
delete from mid_meter_info where taskno=v_Detect_Task_No and pileno=V_PILE_NO and flag=-1;
INSERT INTO Mid_Meter_Info (
MeterId,
TaskNo,
MeterNo,
PileNo,
BoxNo,
Bindtime,
Flag)
Select Concat(To_Char(Sysdate, 'yyyymmddhh24mmss'), Substr(Bar_Code,length(Bar_Code)-17,18)),
Detect_Task_No,
Bar_Code,
Pile_No,
Box_Bar_Code,
Sysdate,
-1
From MT_DETECT_OUT_EQUIP@Linktompac
Where pile_no=V_PILE_NO;IF SQL%Rowcount<1 THEN
V_Count:=-1;
END IF;
select count(*) into v_count_tmp from meter_param where taskno=v_Detect_Task_No;
if v_count_tmp<1 then
INSERT INTO METER_PARAM
(
Meterid
,Taskno
,GROUPID
,METERKIND --电表类型 1机械表/2电子表/3电子表多功能表
,DIRECTCONNECT--是否直接接入 1/是直接接入
,METERCONVERT --有无止逆器 1/有
,TRIPHASE --单三相 1单相 2三相四线 3三相三线
,POWERKIND --有无功 1有功 2无功 3有无功一体
,CONNECTIONTYPE
,METERTYPE --电表型号
,METERTYPENAME
,MANUFACTORY
,VOLTAGE
,BASECURRENT
,INCURRENT
,MAXCURRENT
,FREQUENCY
,CLASSGRADE
,CLASSGRADEREACTIVE
,PERIODOFVALIDITY
,CHECKREGULATION
,CHECKERNAME
,AUDITERNAME
,MANAGERNAME
,Mulmeterpulsepolarity
,MULMETERPROTOCOL
,MULMETERLOGINNAME
,MULMETERWRITEPASSWORD
,Mulmeterdemandpassword
,MULMETERFREQ
,Mulmeterdemandcyc
,Mulmeterdemandslide
,CARRIERWAVETYPE
,MULBAUDRATE
,MULPARITY
,MULDATABITS
,MULSTOPBITS
,Metercard
,Verfied
,Contactbreaker
,Contactbreakertpye
,GENERALNUMBER
,MANUFACTORYNUMBER
,MANUFACTORYDATE
,CONSTUNIT
,CONSTUNITREACTIVE
,MULMETERADDRESS
)
SELECT
m.detect_task_no,
m.detect_task_no,
T.Arrive_Batch_No,--到货批次号
3,-- T.Type_Code ,--01:三相智能电能表、02:三相费控智能表(无线)、03:三相费控智能表(载波)、04:单相本地费控表、05:单相远程费控表、06:01干式、07:浇注式、08:油浸式、…
CASE t.CON_MODE WHEN '01' THEN 1 ELSE 0 END,--01:直接接入、02:经互感器接入
CASE t.CC_PREVENT_FLAG WHEN '1' THEN 1
ELSE 0 END,--有无止逆器 1/有
CASE t.WIRING_MODE WHEN '1' THEN 1
WHEN '2' THEN 3
WHEN '3' THEN 2
ELSE 1 END ,--电能表的接线方式引用国家电网公司营销管理代码类集:5110.84电能表接线方式分类与代码CASE t.READING_TYPE_CODE WHEN '06' THEN 1
WHEN '04' THEN 1
WHEN '07' THEN 2
ELSE 3 END ,--01:最大需量+正向有功及分时+反向有功及分时+无功四象限、02:最大需量+正向有功及分时+无功感容、03:正向有功及分时+无功感容、04:正向有功及分时、05:正向有功+无功感容、06:有功、07:无功
CASE t.WIRING_MODE WHEN '01' THEN 1
WHEN '02' THEN 6
WHEN '03' THEN 3
WHEN '04' THEN 9
ELSE 1 END,--接线方式meterWireType 1单相有功 2单相无功 3三相四线有功 4三相四线无功 5三相四线90度无功 6三相三线有功 7三相三线无功 8三相三线无功方式2 9三相三线60度无功 10三相三线90度无功
Case T.MODEL_CODE When '' Then 'DTSD' Else(Select Name From MT_P_Code@Linktompac Where Code_Type='meterModelNo' And Value=T.MODEL_CODE) End, ----设'备型号 meterModelNo
'',--电表全
Case T.MANUFACTURER When '' Then 'sms' Else(Select Name From MT_P_Code@Linktompac Where Code_Type='meterFacturer' And Value=T.MANUFACTURER) End, -----生产厂家
CASE t.VOLT_CODE WHEN '01' THEN 220 --01:220V、02:3x380、03:3x380/220V、04:3x100V、05:3x57.7/100V
WHEN '02' THEN 380
WHEN '03' THEN 220
WHEN '04' THEN 100
WHEN '05' THEN 57.7
Else 57.7 End,
CASE t.RATED_CURRENT WHEN '9984' THEN 1
WHEN '9985' THEN 1
WHEN '9986' THEN 2
WHEN '9987' THEN 2.5
WHEN '9989' THEN 0.3
WHEN '9990' THEN 15
WHEN '9991' THEN 30
WHEN '9992' THEN 40
WHEN '9993' THEN 40
WHEN '9994' THEN 50
WHEN '9995' THEN 80
WHEN '9996' THEN 15
WHEN '9997' THEN 1
WHEN '9998' THEN 3
WHEN '9999' THEN 4
Else 1 End, --基本电流 meterRcSort
CASE t.RATED_CURRENT WHEN '9984' THEN 1
WHEN '9985' THEN 1
WHEN '9986' THEN 2
WHEN '9987' THEN 2.5
WHEN '9989' THEN 0.3
WHEN '9990' THEN 15
WHEN '9991' THEN 30
WHEN '9992' THEN 40
WHEN '9993' THEN 40
WHEN '9994' THEN 50
WHEN '9995' THEN 80
WHEN '9996' THEN 15
WHEN '9997' THEN 1
WHEN '9998' THEN 3
WHEN '9999' THEN 4
ELSE 1 END, --标定电流
CASE t.RATED_CURRENT WHEN '9984' THEN 1
WHEN '9985' THEN 2
WHEN '9986' THEN 4
WHEN '9987' THEN 15
WHEN '9989' THEN 1.2
WHEN '9990' THEN 60
WHEN '9991' THEN 100
WHEN '9992' THEN 80
WHEN '9993' THEN 100
WHEN '9994' THEN 100
WHEN '9995' THEN 120
WHEN '9996' THEN 100
WHEN '9997' THEN 6
WHEN '9998' THEN 5
WHEN '9999' THEN 6
ELSE 1 END,--最大电流
t.FREQ_CODE,
Case T.AP_PRE_LEVEL_CODE When '' Then '1' Else(Select Name From MT_P_Code@Linktompac Where Code_Type='stddevPreCode' And Value=T.AP_PRE_LEVEL_CODE) End, --有功精度 stddevPreCodeCase T.RP_PRE_LEVEL_CODE When '' Then '1' Else(Select Name From MT_P_Code@Linktompac Where Code_Type='stddevPreCode' And Value=T.RP_PRE_LEVEL_CODE) End, ----无功精度 stddevPreCode'',--电表有效期
'',--校验依据
'',--校验人员
'',--审核人员
'',--主管人员
'',--多功能表脉冲极性选择 0共阴 1 共阳
CASE t.COMM_PROT_CODE WHEN '01' THEN 1
WHEN '02' THEN 2
ELSE 1 END,--多功能表协议 01:645-07规约、02:97规约
'',--多功能表登陆名
'',--多功能表写表密码
'',--多功能表清需量密码
'',--多功能表时钟频率
'',--多功能表需量周期(分)
'',--多功能表最大需量滑差时间(分
case t.chip_manufacturer when '04' then 3
when '01' then 2
when '03' then 1
when '02' then 4
else 5 end,--载波通讯转接器厂家 1 东软 2晓程 3鼎信 4瑞斯康 5力合微 6弥亚微 7埃软 8埃施朗
CASE t.BAUDRATE_CODE WHEN '' THEN '1200' ELSE (Select NAME From MT_P_Code@Linktompac Where Code_Type='BAUDRATE' And Value=t.BAUDRATE_CODE) END,--多功能通讯波特率
'',--多功能奇偶校验位
8,--t.METER_DIGITS,--多功能数据位
'',--多功能数据停止位
'',--是否为卡表
'1',--检定类型 1首检 2周检 3抽检 4二次抽检 5复合性检定
'',--电流断触器 1为内置 2为外置
CASE t.RELAY_JOINT WHEN '01' THEN 1
WHEN '02' THEN 2
Else 3 End,--电流断触器接入点类型 1为常闭 ,2为常开称 01:常开、 02:常闭、03:常开+常闭
'',--局编号
t.MADE_NO,
T.Made_Date,
Case T.CONST_CODE When '' Then '400' Else(Select Name From MT_P_Code@Linktompac Where Code_Type='meterConstCode' And Value=T.CONST_CODE) End, ----无功精度 stddevPreCode
Case T.RP_CONSTANT When '' Then '400' Else(Select Name From MT_P_Code@Linktompac Where Code_Type='meterConstCode' And Value=T.RP_CONSTANT) End, ----无功精度 stddevPreCode
''
From MT_METER@Linktompac t,m_mid_tasks m
Where t.arrive_batch_no=m.arrive_batch_no and m.detect_task_no=v_task_no and rownum=1;
end if;
--插入箱信息select case DIRECTCONNECT when 1 then '01' else '02' end into v_con_code from meter_param where taskno=v_task_no and rownum=1;
if nvl(v_con_code,'9999')='9999' then
sp_insert_error_info ('setEquipDETManu',v_task_no,'meter_param无该任务的参数信息');
end if;
delete from box_info where box_no in (select boxno from mid_meter_info where taskno=v_task_no and pileno=V_PILE_NO and flag=-1);
insert into box_info (box_no,box_type,box_model,box_status,subtime) select boxno,'01',v_con_code,1,sysdate from mid_meter_info where taskno=v_task_no and pileno=V_PILE_NO and flag=-1 group by boxno;
IF SQL%Rowcount<1 THEN
V_Count:=-3;
sp_insert_error_info ('setEquipDETManu',V_PILE_NO,'插入数据到表box_info失败');
end if;IF V_Count<0 THEN
sp_insert_error_info ('setEquipDETManu',V_Count,V_Count);
END IF;Else
V_returnValue :=-3 ;--表资产下载失败
sp_insert_error_info ('setEquipDETManu',V_Count,'-3 表资产下载失败');
end if;
Exception
when others then
sp_insert_error_info ('setEquipDETManu','others','others');
End;