mes_cx_rcl_zzpnew_hand.md 8.7 KB
mes_cx_rcl_zzpnew_hand 存储过程（什么什么热轧）
模板下载
用{0}表达 当前月份 替换成202602
用{1}表达 下个月份 替换成202603
用{2}表达 下个月份 替换成202601
用{3}表达 当前月份 替换成2026-02
原始脚本内容
CREATE OR REPLACE procedure mes_cx_rcl_zzpnew_hand is
a number(4);
b varchar(200);
cxdata orderdetail_v@th_cx_link%rowtype;
prod_name_cx varchar(200);
sg_grade_cx varchar(200);
sg_sign_cx varchar(200);
wall_thickness_cx varchar(200);
material_code_cx varchar(200);
MATERIAL_NAME_cx varchar(200);
outer_diam_cx varchar(200);
--热处理
cursor oporder_cursor_rcl is
SELECT *
FROM (select t.judge_stove_no,
(case
when t.judge_stove_no like 'W%' then
'是'
else
'否'
end) resource_type,
t.ZY_BATCH_ID 生产批号,
T.STOVE_NO,
'2026-02' acct_period,
null high_tech_code, null high_tech_name, 0 high_prod_weight,
0 high_mate_weight, 0 estimated_unit_price, 0 estimated_cost,
T.PRODUCNAME,
t.gradename steel_no,
T.DECISION,
T.OUTDIAMETER || '*' || T.WALLTHICK spec,
T.ORDER_NO || T.ORDER_SEQ orderid,
(SELECT DECODE(MAX(S.ORDER_TYP),
'120108',
'双经销',
'120109',
'定销订购',
'120107',
'来料加工',
'自用')
FROM SLM_ORDER_HEAD S
WHERE S.ORDER_NO = REGEXP_REPLACE(T.ORDER_NO, '/.*', '')) contract_type,
MAX(T.STD_STYLE_DESC) 标准类别,
MAX(T.GOWHERE_NAME) 物流去向,
MAX(T.OUTPUT_STANDARD) 输出标准,
MAX(T.STEELNAME) steel_level,
MAX(T.MODEL_DESC) 扣型,
T.PLINE_CODE,
(SELECT C.PLINE_NAME
FROM COM_BASE_PLINE C
WHERE C.PLINE_CODE = T.PLINE_CODE) PLINE_NAME,
sum(a.act_count) act_count,
sum(a.act_weight) act_weight,
null billet_code
from PLN_ZY_RCL_M t,
(select t.judge_stove_no,
T.HEAT_PLAN_NO,
sum(t.act_count) act_count,
sum(decode(T.act_weight,
'',
t.act_theory_weight,
t.act_weight)) act_weight
from PORT_HTT_BATCH_SAMPLE_RESULT t
where (t.group_flag = 0 OR T.GROUP_FLAG IS NULL)
--AND T.INSTORE_FLAG = '0'
AND T.PROCESS_SEQ = '1'
AND T.MAT_STATUS IN ('00', '20')
and t.bal_year_month <= '202602'
and t.bal_year_month >= '202002'
group by t.judge_stove_no, T.HEAT_PLAN_NO
UNION ALL
select t.judge_stove_no,
T.HEAT_PLAN_NO,
-SUM(T.ACT_COUNT) act_count,
-sum(t.ACT_WEIGHT) act_weight
from QCM_ZG_JUGDE_APPLY t
where ((t.validflag = '20' and
t.JUDGE_RESULT_CODE in ('40740701', '40740709')) OR
T.INSTANCY_FLAG = '1')
and t.is_flag = 0
and t.PROCESS_CODE = 'F'
and t.bal_year_month <= '202602'
and t.bal_year_month >= '202002'
group by t.judge_stove_no, T.HEAT_PLAN_NO
) a
where a.judge_stove_no = t.judge_stove_no
AND T.HEAT_PLAN_NO = A.HEAT_PLAN_NO
group by t.judge_stove_no,
t.ZY_BATCH_ID,
T.STOVE_NO,
T.PRODUCNAME,
T.DECISION,
t.gradename,
T.OUTDIAMETER,
T.WALLTHICK,
T.PLINE_CODE,
T.ORDER_NO,
T.ORDER_SEQ
order by t.pline_code, t.judge_stove_no)
WHERE act_count > 0 and orderid not like '%ZZBY%';
curoporder_rcl oporder_cursor_rcl%rowtype;
begin
--先删除
/* delete from HOT_HEAT_PRODUCTION_MES_GX
where acct_period in ('2025-01')
and prod_type = '在产品'
and flag = '0';*/
open oporder_cursor_rcl;
loop
fetch oporder_cursor_rcl
into curoporder_rcl;
exit when oporder_cursor_rcl%notfound;
select count(*)
into a
from orderdetail_v@th_cx_link t
where t.contractno_num = curoporder_rcl.orderid
and material_code is not null;
if a > 0 then
select prod_name,sg_grade,sg_sign,outer_diam,wall_thickness,material_code,MATERIAL_NAME
into prod_name_cx,sg_grade_cx ,sg_sign_cx,outer_diam_cx ,wall_thickness_cx ,material_code_cx , MATERIAL_NAME_cx
from orderdetail_v@th_cx_link t
where t.contractno_num = curoporder_rcl.orderid
and material_code is not null
and rownum = 1;
elsif substr(curoporder_rcl.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_rcl.orderid, 3)
and material_code is not null
and rownum = 1;
if a > 0 then
select prod_name,sg_grade,sg_sign,outer_diam,wall_thickness,material_code,MATERIAL_NAME
into prod_name_cx,sg_grade_cx ,sg_sign_cx,outer_diam_cx ,wall_thickness_cx ,material_code_cx , MATERIAL_NAME_cx
from orderdetail_v@th_cx_link t
where substr(t.contractno_num, 3) =
substr(curoporder_rcl.orderid, 3)
and material_code is not null
and rownum = 1;
end if;
end if;
if a > 0 then
insert into HOT_HEAT_PRODUCTION_MES_GX
(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_rcl.contract_type, prod_name_cx,
sg_sign_cx, sg_grade_cx,
outer_diam_cx || '*' || wall_thickness_cx, '在产品', null,
curoporder_rcl.act_weight, null, '热处理',
curoporder_rcl.high_tech_code, curoporder_rcl.high_tech_name,
curoporder_rcl.high_prod_weight, curoporder_rcl.high_mate_weight,
curoporder_rcl.estimated_unit_price, curoporder_rcl.estimated_cost,
curoporder_rcl.acct_period, material_code_cx,
curoporder_rcl.resource_type, material_code_cx || '1', '', '0',
(case
when material_code_cx like '%B' then
'加工'
when material_code_cx like '%C' then
'热处理'
else
''
end), MATERIAL_NAME_cx, curoporder_rcl.orderid,curoporder_rcl.judge_stove_no,'热轧',SUBSTR(MATERIAL_NAME_cx,INSTR(MATERIAL_NAME_cx, ',', 1, 1) + 1, INSTR(MATERIAL_NAME_cx, ',', 1,2) - INSTR(MATERIAL_NAME_cx, ',', 1, 1) - 1),sysdate);
else
insert into HOT_HEAT_PRODUCTION_MES_GX
(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_rcl.contract_type,
null, null,
null, null, '在产品', null,
curoporder_rcl.act_weight, null, '热处理', curoporder_rcl.high_tech_code,
curoporder_rcl.high_tech_name, curoporder_rcl.high_prod_weight,
curoporder_rcl.high_mate_weight, curoporder_rcl.estimated_unit_price,
curoporder_rcl.estimated_cost, curoporder_rcl.acct_period, null,
curoporder_rcl.resource_type, null, '', '0',
(case when cxdata.material_code like '%B' then '加工' when
cxdata.material_code like '%C' then '热处理' else '' end), null,
curoporder_rcl.orderid,curoporder_rcl.judge_stove_no);
end if;
end loop;
close oporder_cursor_rcl;
commit;
/*
exception
when others then
null;*/
end mes_cx_rcl_zzpnew_hand;