mes_cx_rcl_zxnew_hand.md 11 KB
mes_cx_rcl_zxnew_hand 存储过程（什么什么热处理）
模板下载
用{0}表达 当前月份 替换成02
用{1}表达 下个月份 替换成03
用{2}表达 下个月份 替换成01
原始脚本内容
CREATE OR REPLACE procedure MES_CX_RCL_zxNEW_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 (select decode(max(s.order_typ),
'120108',
'双经销',
'120109',
'定销订购',
'120107',
'来料加工',
'自用')
from cxuser.slm_order_head s
where s.order_no = regexp_replace(t.pro_order_no, '/.*', '')) contract_type,
--合同类型
t.producname prod_variety,
replace(t.PRO_ORDER_NO, '/', '') orderid,
--品名、品种
t.steelname steel_level,
--钢级
t.gradename steel_no,
--钢号
t.spec_name spec,
--规格
(case
when t.target = 'A' then
'产成品'
else
'半成品'
end) prod_type,
--产量类型
(case
when t.target = 'A' then
(sum(decode(t.act_weight,
null,
t.act_theory_weight,
t.act_weight)) -
nvl((select SUM(nvl(T2.act_weight, 0))
from ydm_zc_stocktakinglist t2
where t2.judge_stove_no = t.judge_stove_no
and t2.STOCKTAKING_TYPE_CODE = '800603'
and t2.BAL_YEAR_MONTH = '202602'),
0))
else
sum(decode(t.act_weight,
null,
t.act_theory_weight,
t.act_weight))
end) prod_weight,
--成品重量
nvl((select SUM(nvl(T2.act_weight, 0))
from ydm_zc_stocktakinglist t2
where t2.judge_stove_no = t.judge_stove_no
and t2.STOCKTAKING_TYPE_CODE = '800603'
and t2.BAL_YEAR_MONTH = '202602'),
0) LC_WEIGHT,
--量差
sum(decode(t.act_theory_weight,
null,
t.act_weight,
t.act_theory_weight)) mate_weight,
--来料重量
(case
when sum(input_weight) <> 0 then
round(sum(decode(t.act_weight,
null,
t.act_theory_weight,
t.act_weight)) / sum(input_weight),
4) * 100
else
0
end) steel_waste,
--消耗占比
'热处理' process,
--工序
null high_tech_code,
null high_tech_name,
0 high_prod_weight,
0 high_mate_weight,
0 estimated_unit_price,
0 estimated_cost,
substr(bal_year_month, 0, 4) || '-' ||
substr(bal_year_month, 5, 2) acct_period,
--GET_CP_CODE4(t.pro_order_no) MATE_CODE,--物料编码
--换成取产销提供的视图 tzh 24.12.4
get_cp_code6(t.pro_order_no) mate_code,
--物料编码
getislljg(t.judge_stove_no, replace(t.PRO_ORDER_NO, '/', '')) resource_type,
t.judge_stove_no,
--是否来料加工
null billet_code --钢坯编码,
from cxuser.qcm_zg_jugde_apply t
where t.is_flag = '0'
and t.is_all_scrap in ('0', '2')
and ((t.validflag = '20' and t.target <> 'A' and
t.judge_result_code in ('40740701', '40740705', '40740709')) or
(t.validflag = '20' and t.target = 'A' and
t.judge_tolresult_code in ('40740701', '40740705', '40740709')) or
t.instancy_flag = '1')
and t.process_code = 'F'
and t.bal_year_month in ('202602')
--AND T.BAL_YEAR_MONTH = to_char(sysdate,'yyyymm')
AND t.PRO_ORDER_NO NOT LIKE 'ZZBY%'
--AND T.judge_stove_no in ('A25060356','F25070004')
-- and t.pline_code in ('C091','C105')
group by t.producname,
t.steelname,
--钢级
t.gradename,
--钢号
t.spec_name,
--规格
t.target,
bal_year_month,
pro_order_no,
judge_stove_no;
curoporder_rcl oporder_cursor_rcl%rowtype;
begin
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,
mate_name,
orderid,
GO_THERE,
judge_stove_no,
PRE_PROCESS,
DELIVERY_STATUS,
INSERT_TIME,
LC_WEIGHT)
values
(seq_mes_cx.nextval,
curoporder_rcl.contract_type,
prod_name_cx,
sg_sign_cx,
sg_grade_cx,
outer_diam_cx || '*' || wall_thickness_cx,
curoporder_rcl.prod_type,
curoporder_rcl.prod_weight,
curoporder_rcl.mate_weight,
curoporder_rcl.steel_waste,
curoporder_rcl.process,
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,
--curOPOrder_rcl.BILLET_CODE,
curoporder_rcl.mate_code || '1',
'',
'0',
MATERIAL_NAME_cx,
curoporder_rcl.orderid,
(case
when material_code_cx like '%B' then
'加工'
when material_code_cx like '%C' then
'热处理'
else
''
end),
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,
curoporder_rcl.Lc_Weight);
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,
mate_name,
orderid,
GO_THERE,
judge_stove_no,
lc_weight)
values
(seq_mes_cx.nextval,
curoporder_rcl.contract_type,
curoporder_rcl.prod_variety,
curoporder_rcl.steel_level,
curoporder_rcl.steel_no,
curoporder_rcl.spec,
curoporder_rcl.prod_type,
curoporder_rcl.prod_weight,
curoporder_rcl.mate_weight,
curoporder_rcl.steel_waste,
curoporder_rcl.process,
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,
curoporder_rcl.mate_code,
curoporder_rcl.resource_type,
--curOPOrder_rcl.BILLET_CODE,
curoporder_rcl.mate_code || '1',
'',
'0',
null,
curoporder_rcl.orderid,
(case when cxdata.material_code like '%B' then '加工' when
cxdata.material_code like '%C' then '热处理' else '' end),
curoporder_rcl.judge_stove_no,
curoporder_rcl.lc_weight);
end if;
end loop;
close oporder_cursor_rcl;
commit;
/*
exception
when others then
null;*/
end mes_cx_rcl_zxnew_HAND;