mes_cx_rz_zzpnew_hand.md 24 KB
mes_cx_rz_zzpnew_hand 存储过程（什么什么热轧）
模板下载
用{0}表达 当前月份 替换成02
用{1}表达 下个月份 替换成03
用{2}表达 下个月份 替换成01
用{3}表达 当前月份 替换成2026-02
原始脚本内容
CREATE OR REPLACE procedure mes_cx_rz_zzpnew_hand is
a number(4);
b varchar(4000);
-- 为cxdata定义显式变量而不是使用%rowtype
cxdata_contractno_num varchar2(100);
cxdata_prod_name varchar2(200);
cxdata_sg_sign varchar2(100);
cxdata_sg_grade varchar2(100);
cxdata_outer_diam varchar2(50);
cxdata_wall_thickness varchar2(50);
cxdata_material_code varchar2(100);
cxdata_material_name varchar2(500); -- 特别关注这个字段
cxdata orderdetail_v@th_cx_link%rowtype;
--加工
cursor oporder_cursor_jg is
SELECT J.*,REPLACE(GET_CP_ORDER(J.Judge_Stove_No), '/', '') ORDERID,(select decode(max(s.order_typ),
'120108',
'双经销',
'120109',
'定销订购',
'120107',
'来料加工',
'自用')
from cxuser.slm_order_head s
where s.order_no = regexp_replace(REPLACE(GET_CP_ORDER(J.Judge_Stove_No), '/', ''), '/.*', '')) contract_type,(case
when j.judge_stove_no like 'W%' then
'是'
else
'否'
end) resource_type,null high_tech_code, null high_tech_name,
0 high_prod_weight, 0 high_mate_weight, 0 estimated_unit_price,
0 estimated_cost, '2026-02' acct_period,
get_pl_code((select max(GRADENAME) from PLN_ZY_ZG_m p where p.FEED_HEAT_NO = j.judge_stove_no),
(select max(DIAMETER) from PLN_ZY_ZG_m p where p.FEED_HEAT_NO = j.judge_stove_no) --规格
) billet_code
FROM (select aa.JUDGE_STOVE_NO,
aa.PLINE_NAME,
sum(aa.act_COUNT) act_COUNT,
sum(aa.WEIGHT) WEIGHT
from ( --期初
SELECT T.JUDGE_STOVE_NO,
T.PLINE_NAME,
SUM(NVL(T.ACT_COUNT, 0)) act_COUNT,
SUM(NVL(T.ACT_WEIGHT, 0)) WEIGHT
FROM YDM_ZC_INITIAL_ZG T
WHERE 1 = 1
AND t.bal_year_month = '202602'
group by t.JUDGE_STOVE_NO, t.PLINE_NAME
UNION ALL
--入库
select t.JUDGE_STOVE_NO,
t.PLINE_NAME,
nvl((case
when t.BAL_YEAR_MONTH = '202602' then
nvl(t.count, 0)
end),
0) act_COUNT,
nvl((case
when t.BAL_YEAR_MONTH = '202602' then
nvl(t.weight, 0)
end),
0) WEIGHT
from (SELECT REGEXP_REPLACE(T.JUDGE_STOVE_NO, '-.*', '') JUDGE_STOVE_NO,
(CEIL(T.ACT_COUNT / S.SAW_NUM)) AS count,
t.weight,
t.BAL_YEAR_MONTH,
t.PLINE_NAME
FROM (SELECT T1.JUDGE_STOVE_NO,
T1.BAL_YEAR_MONTH,
(SELECT C.PLINE_NAME
FROM COM_BASE_PLINE C
WHERE C.PLINE_CODE = T1.PLINE_CODE) PLINE_NAME,
SUM(T1.ACT_COUNT) ACT_COUNT,
SUM(T1.INPUT_WEIGHT) weight
FROM PORT_MIL_BATCH_SAMPLE_RESULT T1
WHERE T1.JUDGE_STOVE_NO =
REGEXP_REPLACE(T1.JUDGE_STOVE_NO,
'-.*',
'') || '-01'
AND T1.BAL_YEAR_MONTH = '202602'
GROUP BY T1.JUDGE_STOVE_NO,
T1.BAL_YEAR_MONTH,
T1.PLINE_CODE) T,
MIL_PLAN S
WHERE T.JUDGE_STOVE_NO = S.JUDGE_STOVE_NO
AND S.BACK_PLAN_FLAG = '0'
AND S.BACK_SLAB_FLAG = '0') t
union all
--出库
select t.JUDGE_STOVE_NO,
t.PLINE_NAME,
-nvl((case
when t.BAL_YEAR_MONTH = '202602' then
nvl(t.count, 0)
end),
0) act_COUNT,
-nvl((case
when t.BAL_YEAR_MONTH = '202602' then
nvl(t.weight, 0)
end),
0) WEIGHT
from (SELECT JUDGE_STOVE_NO,
BAL_YEAR_MONTH,
PLINE_NAME,
SUM(COUNT) COUNT,
SUM(WEIGHT) WEIGHT
FROM (SELECT T1.JUDGE_STOVE_NO,
T1.BAL_YEAR_MONTH,
(SELECT C.PLINE_NAME
FROM COM_BASE_PLINE C
WHERE C.PLINE_CODE = T1.PLINE_CODE) PLINE_NAME,
SUM(T1.OUTPUT_COUNT) AS COUNT,
SUM(T1.OUTPUT_WEIGHT) AS WEIGHT
FROM QCM_ZG_JUGDE_APPLY T1, MIL_PLAN S5
WHERE T1.JUDGE_STOVE_NO || '-01' =
S5.JUDGE_STOVE_NO
AND S5.BACK_PLAN_FLAG = '0'
AND S5.BACK_SLAB_FLAG = '0'
AND T1.PROCESS_CODE = 'D'
AND T1.BAL_YEAR_MONTH = '202602'
AND ((T1.VALIDFLAG = '20' AND
T1.TARGET <> 'A' AND
T1.JUDGE_RESULT_CODE IN
('40740701', '40740705', '40740709')) OR
(T1.VALIDFLAG = '20' AND T1.TARGET = 'A' AND
T1.JUDGE_TOLRESULT_CODE IN
('40740701', '40740705', '40740709')) OR
(T1.VALIDFLAG = '20' AND T1.TARGET = 'A' AND
T1.IS_ALL_SCRAP = '1') OR
T1.INSTANCY_FLAG = '1')
--AND T1.OUTPUT_COUNT > 0
AND T1.PLINE_CODE NOT IN ('C065', 'C066')
GROUP BY T1.JUDGE_STOVE_NO,
T1.BAL_YEAR_MONTH,
T1.PLINE_CODE
UNION ALL
SELECT REGEXP_REPLACE(F.JUDGE_STOVE_NO,
'-.*',
'') JUDGE_STOVE_NO,
F.BAL_YEAR_MONTH,
S1.PLINE_NAME,
COUNT(1) COUNT,
SUM(S1.INPUT_WEIGHT) WEIGHT
FROM (SELECT COUNT(1) ACOUNT,
T3.JUDGE_STOVE_NO,
T3.NEW_HEAT_NO,
T3.BAL_YEAR_MONTH
FROM (SELECT G2.*, T1.BAL_YEAR_MONTH
FROM MIL_SLAB_SCRAP G2,
QCM_ZG_JUGDE_APPLY T1
WHERE REGEXP_REPLACE(G2.NEW_HEAT_NO,
'-.*',
'') =
T1.JUDGE_STOVE_NO
AND (G2.REMARK <> '无' OR
G2.REMARK IS NULL)
AND ((t1.validflag = '20' and
t1.JUDGE_RESULT_CODE in
('40740701',
'40740705',
'40740709')) OR
T1.INSTANCY_FLAG = '1')
AND T1.IS_FLAG = '0'
AND T1.PROCESS_CODE = 'D'
AND T1.BAL_YEAR_MONTH =
'202602'
) T3
WHERE T3.PROCESS_NO <= 6
AND T3.NEW_HEAT_NO IS NOT NULL
GROUP BY T3.JUDGE_STOVE_NO,
T3.NEW_HEAT_NO,
T3.BAL_YEAR_MONTH) F,
(SELECT SUM(S.INPUT_WEIGHT) INPUT_WEIGHT,
S.JUDGE_STOVE_NO,
S.M_MAT_NO,
(SELECT C.PLINE_NAME
FROM COM_BASE_PLINE C
WHERE C.PLINE_CODE = S.PLINE_CODE) PLINE_NAME,
ROW_NUMBER() OVER(PARTITION BY JUDGE_STOVE_NO ORDER BY JUDGE_STOVE_NO) RNUM
FROM PORT_MIL_BATCH_SAMPLE_RESULT S
WHERE
--S.OP_PROCESS IN('2','3','4')
--AND S.GROUP_FLAG = '1'
--AND
S.TR_FLAG = '0'
GROUP BY S.M_MAT_NO,
S.JUDGE_STOVE_NO,
S.PLINE_CODE) S1
WHERE F.JUDGE_STOVE_NO = S1.JUDGE_STOVE_NO
AND RNUM <= F.ACOUNT
GROUP BY F.JUDGE_STOVE_NO,
F.BAL_YEAR_MONTH,
S1.PLINE_NAME
UNION ALL
SELECT REGEXP_REPLACE(S.JUDGE_STOVE_NO,
'-.*',
'') JUDGE_STOVE_NO,
T.BAL_YEAR_MONTH,
S.PLINE_NAME,
COUNT(1) COUNT,
SUM(S.INPUT_WEIGHT) WEIGHT
FROM (SELECT G1.*, T1.BAL_YEAR_MONTH
FROM MIL_FURNACES_RESLUT_DETIA G1,
QCM_ZG_JUGDE_APPLY T1
WHERE REGEXP_REPLACE(G1.ZP_JUDGE_STOVE_NO,
'-.*',
'') =
T1.JUDGE_STOVE_NO
AND (G1.REMARK <> '无' OR
G1.REMARK IS NULL)
AND ((t1.validflag = '20' and
t1.JUDGE_RESULT_CODE in
('40740701',
'40740705',
'40740709')) OR
T1.INSTANCY_FLAG = '1')
AND T1.IS_FLAG = '0'
AND T1.PROCESS_CODE = 'D'
AND T1.BAL_YEAR_MONTH = '202602'
) T,
(SELECT SUM(S1.INPUT_WEIGHT) INPUT_WEIGHT,
S1.M_MAT_NO,
S1.JUDGE_STOVE_NO,
(SELECT C.PLINE_NAME
FROM COM_BASE_PLINE C
WHERE C.PLINE_CODE =
S1.PLINE_CODE) PLINE_NAME,
ROW_NUMBER() OVER(PARTITION BY JUDGE_STOVE_NO ORDER BY JUDGE_STOVE_NO) RNUM
FROM PORT_MIL_BATCH_SAMPLE_RESULT S1
WHERE
--S1.OP_PROCESS = '5'
--AND S1.GROUP_FLAG = '1'
--AND
S1.TR_FLAG = '0'
GROUP BY S1.JUDGE_STOVE_NO,
S1.M_MAT_NO,
S1.PLINE_CODE) S
WHERE T.JUDGE_STOVE_NO = S.JUDGE_STOVE_NO
AND RNUM <= T.GROUP_NUM
GROUP BY S.JUDGE_STOVE_NO,
S.PLINE_NAME,
T.BAL_YEAR_MONTH
UNION ALL
SELECT REGEXP_REPLACE(T8.JUDGE_STOVE_NO,
'-.*',
'') JUDGE_STOVE_NO,
T7.BAL_YEAR_MONTH,
T8.PLINE_NAME,
COUNT(1) COUNT,
SUM(INPUT_WEIGHT) WEIGHT
FROM (SELECT T.OLD_ZP_JUDGE_STOVE_NO,
CEIL(SUM(T.ZP_COUNT) / S.SAW_NUM) AS RN,
T1.BAL_YEAR_MONTH
FROM MIL_OFFLINE_GROUP T,
MIL_PLAN S,
QCM_ZG_JUGDE_APPLY T1
WHERE T.Old_Zp_Judge_Stove_No =
S.JUDGE_STOVE_NO
AND (T.REMARK <> '无' OR
T.REMARK IS NULL)
AND S.BACK_SLAB_FLAG <> '2'
AND REGEXP_REPLACE(T.ZP_JUDGE_STOVE_NO,
'-.*',
'') =
T1.Judge_Stove_No
AND ((t1.validflag = '20' and
t1.JUDGE_RESULT_CODE in
('40740701',
'40740705',
'40740709')) OR
T1.INSTANCY_FLAG = '1')
AND T1.IS_FLAG = '0'
AND T1.PROCESS_CODE = 'D'
AND T1.BAL_YEAR_MONTH = '202602'
GROUP BY T.OLD_ZP_JUDGE_STOVE_NO,
S.SAW_NUM,
T1.BAL_YEAR_MONTH) T7,
(SELECT SUM(S2.INPUT_WEIGHT) INPUT_WEIGHT,
S2.JUDGE_STOVE_NO,
S2.M_MAT_NO,
(SELECT C.PLINE_NAME
FROM COM_BASE_PLINE C
WHERE C.PLINE_CODE =
S2.PLINE_CODE) PLINE_NAME,
ROW_NUMBER() OVER(PARTITION BY JUDGE_STOVE_NO ORDER BY JUDGE_STOVE_NO) RNUM
FROM PORT_MIL_BATCH_SAMPLE_RESULT S2
WHERE
--S2.OP_PROCESS IN('10','11')
--AND S2.GROUP_FLAG = '1'
S2.TR_FLAG = '0'
GROUP BY S2.M_MAT_NO,
S2.JUDGE_STOVE_NO,
S2.PLINE_CODE) T8
WHERE REGEXP_REPLACE(T7.OLD_ZP_JUDGE_STOVE_NO,
'-.*',
'') || '-01' =
T8.JUDGE_STOVE_NO
AND RNUM <= RN
GROUP BY T8.JUDGE_STOVE_NO,
T7.BAL_YEAR_MONTH,
T8.PLINE_NAME)
GROUP BY JUDGE_STOVE_NO, BAL_YEAR_MONTH, PLINE_NAME) t) aa
group by aa.JUDGE_STOVE_NO, aa.PLINE_NAME) J
WHERE act_COUNT > 0;
curoporder_jg oporder_cursor_jg%rowtype;
begin
--先删除
/* delete from hot_production_mes_rz
where acct_period in ('2025-01')
and prod_type = '在产品'
and flag = '0';*/
open oporder_cursor_jg;
loop
fetch oporder_cursor_jg into curoporder_jg;
exit when oporder_cursor_jg%notfound;
begin -- 添加内部begin块用于异常处理
select count(*)
into a
from orderdetail_v@th_cx_link t
where t.contractno_num = curoporder_jg.orderid
and material_code is not null;
if a > 0 then
-- 使用显式字段选择而不是select *
select contractno_num, prod_name, sg_sign, sg_grade,
outer_diam, wall_thickness, material_code, material_name
into cxdata_contractno_num, cxdata_prod_name, cxdata_sg_sign,
cxdata_sg_grade, cxdata_outer_diam, cxdata_wall_thickness,
cxdata_material_code, cxdata_material_name
from orderdetail_v@th_cx_link t
where t.contractno_num = curoporder_jg.orderid
and material_code is not null
and rownum = 1;
elsif substr(curoporder_jg.orderid, 1, 2) in ('TH', 'XS') then
select count(*)
into a
from orderdetail_v@th_cx_link t
where substr(t.contractno_num, 3) = substr(curoporder_jg.orderid, 3)
and material_code is not null
and rownum = 1;
if a > 0 then
select contractno_num, prod_name, sg_sign, sg_grade,
outer_diam, wall_thickness, material_code, material_name
into cxdata_contractno_num, cxdata_prod_name, cxdata_sg_sign,
cxdata_sg_grade, cxdata_outer_diam, cxdata_wall_thickness,
cxdata_material_code, cxdata_material_name
from orderdetail_v@th_cx_link t
where substr(t.contractno_num, 3) = substr(curoporder_jg.orderid, 3)
and material_code is not null
and rownum = 1;
end if;
end if;
if INSTR(curoporder_jg.orderid, 'ZZBY') <= 0 then
if a > 0 then
insert into hot_production_mes_rz
(cx_no, contract_type, prod_variety, steel_level, steel_no, spec,
prod_type, prod_weight, mate_weight, steel_waste, process,
high_tech_code, high_tech_name, high_prod_weight, high_mate_weight,
estimated_unit_price, estimated_cost, acct_period, mate_code,
resource_type, billet_code, memo, flag, go_there, mate_name,
orderid,judge_stove_no,PRE_PROCESS,DELIVERY_STATUS,INSERT_TIME)
values
(seq_mes_cx.nextval, curoporder_jg.contract_type, cxdata_prod_name,
cxdata_sg_sign, cxdata_sg_grade,
cxdata_outer_diam || '*' || cxdata_wall_thickness, '在产品', null,
curoporder_jg.WEIGHT, null, '热轧',
curoporder_jg.high_tech_code, curoporder_jg.high_tech_name,
curoporder_jg.high_prod_weight, curoporder_jg.high_mate_weight,
curoporder_jg.estimated_unit_price, curoporder_jg.estimated_cost,
curoporder_jg.acct_period, cxdata_material_code,
curoporder_jg.resource_type, curoporder_jg.billet_code, '', '0',
(case
when cxdata_material_code like '%B' then '加工'
when cxdata_material_code like '%C' then '热处理'
else ''
end), cxdata_material_name, curoporder_jg.orderid,
curoporder_jg.judge_stove_no, null,
SUBSTR(cxdata_material_name,
INSTR(cxdata_material_name, ',', 1, 1) + 1,
INSTR(cxdata_material_name, ',', 1, 2) - INSTR(cxdata_material_name, ',', 1, 1) - 1),
sysdate);
else
-- 处理没有找到数据的情况
insert into HOT_PRODUCTION_MES_RZ
(cx_no, contract_type, prod_variety, steel_level, steel_no, spec,
prod_type, prod_weight, mate_weight, steel_waste, process,
high_tech_code, high_tech_name, high_prod_weight, high_mate_weight,
estimated_unit_price, estimated_cost, acct_period, mate_code,
resource_type, billet_code, memo, flag, go_there, mate_name,
orderid,judge_stove_no)
values
(seq_mes_cx.nextval, curoporder_jg.contract_type,
null, null, null, null, '在产品', null,
curoporder_jg.WEIGHT, null, '热轧', curoporder_jg.high_tech_code,
curoporder_jg.high_tech_name, curoporder_jg.high_prod_weight,
curoporder_jg.high_mate_weight, curoporder_jg.estimated_unit_price,
curoporder_jg.estimated_cost, curoporder_jg.acct_period, null,
curoporder_jg.resource_type, curoporder_jg.billet_code, '', '0',
null, null, curoporder_jg.orderid, curoporder_jg.judge_stove_no);
end if;
end if;
exception
when value_error then -- 处理值错误
-- 记录错误信息，但继续处理下一条记录
dbms_output.put_line('数据截断错误 for orderid: ' || curoporder_jg.orderid);
continue;
when others then
-- 处理其他异常
dbms_output.put_line('其他错误 for orderid: ' || curoporder_jg.orderid || ' - ' || sqlerrm);
continue;
end;
end loop;
close oporder_cursor_jg;
commit;
end mes_cx_rz_zzpnew_hand;