mes_cx_JG_zzpnew_hand.md 37 KB
mes_cx_JG_zzpnew_hand 存储过程（什么什么热轧）
模板下载
用{0}表达 当前月份 替换成202602
用{1}表达 下个月份 替换成202603
用{2}表达 下个月份 替换成202601
用{3}表达 当前月份 替换成2026-02
原始脚本内容
CREATE OR REPLACE procedure mes_cx_JG_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_jg is
select j.*,get_cp_code6(j.orderid) mate_code,case j.pline_code when 'C108' then '车丝' else '加工' end as process,
(select decode(max(s.order_typ),
'120108',
'双经销',
'120109',
'定销订购',
'120107',
'来料加工',
'自用')
from cxuser.slm_order_head s
where s.order_no = regexp_replace(j.orderid, '/.*', '')) 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(j.steel_no,
(select xxx.dimater
from cxuser.ydm_gp_outlist xxx
where xxx.JUDGE_STOVE_NO = j.JUDGE_STOVE_NO
and rownum = 1) --规格
) billet_code
from (SELECT JUDGE_STOVE_NO,replace(PRO_ORDER_NO, '/', '') orderid,BATCH_NO,PRODUCNAME prod_variety,STEELNAME steel_level,GRADENAME steel_no,DIMATER,HEIGHT,DIMATER || '*' || HEIGHT spec,MODEL_DESC,PLINE_CODE,PLINE_NAME,sum(END_NUM) END_NUM,sum(END_WT) mate_weight
FROM (SELECT '202602' BAL_YEAR_MONTH,
PRODUCCODE,
PRODUCNAME,
STEELCODE,
STEELNAME,
GRADECODE,
GRADENAME,
DIMATER,
HEIGHT,
PRO_ORDER_NO,
MODEL_CODE,
MODEL_DESC,
MAX(WATING_DECISION_MEMO) WATING_DECISION_MEMO,
JUDGE_STOVE_NO,
BATCH_NO,
PLINE_CODE,
PLINE_NAME,
SUM(ACT_COUNT) ACT_COUNT,
SUM(ACT_WEIGHT) ACT_WEIGHT,
SUM(SOURCE_NUM_TOTAL) SOURCE_NUM_TOTAL,
SUM(SOURCE_WT_TOTAL) SOURCE_WT_TOTAL,
SUM(SOURCE_NUM_TOTAL) SOURCE_NUM_TOTAL2,
SUM(SOURCE_WT_TOTAL) SOURCE_WT_TOTAL2,
SUM(NVL(SOURCE_NUM0, 0)) SOURCE_NUM0,
SUM(NVL(SOURCE_WT0, 0)) SOURCE_WT0,
SUM(NVL(SOURCE_NUM1, 0)) SOURCE_NUM1,
SUM(NVL(SOURCE_WT1, 0)) SOURCE_WT1,
SUM(NVL(SOURCE_NUM2, 0)) SOURCE_NUM2,
SUM(NVL(SOURCE_WT2, 0)) SOURCE_WT2,
SUM(JUDGE_NUM) JUDGE_NUM,
SUM(JUDGE_WT) JUDGE_WT,
SUM(FAIL_NUM) FAIL_NUM,
SUM(FAIL_WT) FAIL_WT,
SUM(GROUP_NUM) GROUP_NUM,
SUM(GROUP_WT) GROUP_WT,
SUM(END_NUM) END_NUM,
SUM(END_WT) END_WT,
SUM(NVL(COUPLING_NUM, 0)) COUPLING_NUM,
SUM(NVL(COUPLING_WT, 0)) COUPLING_WT,
SUM(NVL(XS_ACT_COUNT,0)) XS_ACT_COUNT,
SUM(NVL(XS_ACT_WEIGHT,0)) XS_ACT_WEIGHT,
(SUM(JUDGE_NUM) - SUM(NVL(XS_ACT_COUNT,0))) CZ_NUM,
(SUM(JUDGE_WT) - SUM(NVL(XS_ACT_WEIGHT,0))) CZ_WEIGHT
FROM (SELECT ---期初
PRODUCCODE,
PRODUCNAME,
STEELCODE,
STEELNAME,
GRADECODE,
GRADENAME,
DIMATER,
HEIGHT,
PRO_ORDER_NO,
MODEL_CODE,
MODEL_DESC,
JUDGE_STOVE_NO,
BATCH_NO,
PLINE_CODE,
PLINE_NAME,
ACT_COUNT,
ACT_WEIGHT,
'' WATING_DECISION_MEMO,
0 SOURCE_NUM_TOTAL,
0 SOURCE_WT_TOTAL,
0 SOURCE_NUM0,
0 SOURCE_WT0,
0 SOURCE_NUM1,
0 SOURCE_WT1,
0 SOURCE_NUM2,
0 SOURCE_WT2,
0 JUDGE_NUM,
0 JUDGE_WT,
0 FAIL_NUM,
0 FAIL_WT,
0 GROUP_NUM,
0 GROUP_WT,
0 END_NUM,
0 END_WT,
0 COUPLING_NUM,
0 COUPLING_WT,
0 XS_ACT_COUNT,
0 XS_ACT_WEIGHT
FROM YDM_HTT_INITIAL T
WHERE T.BAL_YEAR_MONTH = '202602' and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100')
UNION ALL
SELECT -- 上料
PRODUCCODE,
PRODUCNAME,
STEELCODE,
STEELNAME,
GRADECODE,
GRADENAME,
M.OUTDIAMETER DIMATER,
M.WALLTHICK HEIGHT,
M.ORDER_NO || '/' || M.ORDER_SEQ PRO_ORDER_NO,
MODEL_CODE,
MODEL_DESC,
T.JUDGE_STOVE_NO,
T.BATCH_NO,
T.PLINE_CODE,
T.PLINE_NAME,
0 ACT_COUNT,
0 ACT_WEIGHT,
'' WATING_DECISION_MEMO,
T.PRO_NUM SOURCE_NUM_TOTAL,
T.PRO_WT SOURCE_WT_TOTAL,
DECODE(MAT_SOURCE, '0', PRO_NUM, 0) SOURCE_NUM0,
DECODE(MAT_SOURCE, '0', PRO_WT, 0) SOURCE_WT0,
DECODE(MAT_SOURCE, '1', PRO_NUM, 0) SOURCE_NUM1,
DECODE(MAT_SOURCE, '1', PRO_WT, 0) SOURCE_WT1,
DECODE(MAT_SOURCE, '2', PRO_NUM, 0) SOURCE_NUM2,
DECODE(MAT_SOURCE, '2', PRO_WT, 0) SOURCE_WT2,
0 JUDGE_NUM,
0 JUDGE_WT,
0 FAIL_NUM,
0 FAIL_WT,
0 GROUP_NUM,
0 GROUP_WT,
0 END_NUM,
0 END_WT,
0 COUPLING_NUM,
0 COUPLING_WT,
0 XS_ACT_COUNT,
0 XS_ACT_WEIGHT
FROM MCH_LOADING_RESULT T, PLN_ZY_JGX_M M
WHERE T.HEAT_PLAN_NO = M.HEAT_PLAN_NO
AND T.BAL_YEAR_MONTH = '202602' and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100')
UNION ALL
SELECT -- 成品红冲 利库短节备料 计入收料 缴库
PRODUCCODE,
PRODUCNAME,
STEELCODE,
STEELNAME,
GRADECODE,
GRADENAME,
T.ACT_DIMATER DIMATER,
T.ACT_HEIGHT HEIGHT,
T.PRO_ORDER_NO,
MODEL_CODE,
MODEL_DESC,
T.JUDGE_STOVE_NO,
T.BATCH_NO,
T.PLINE_CODE,
T.PLINE_NAME,
0 ACT_COUNT,
0 ACT_WEIGHT,
'' WATING_DECISION_MEMO,
T.ACT_COUNT SOURCE_NUM_TOTAL,
T.ACT_WEIGHT SOURCE_WT_TOTAL,
T.ACT_COUNT SOURCE_NUM0,
T.ACT_WEIGHT SOURCE_WT0,
0 SOURCE_NUM1,
0 SOURCE_WT1,
0 SOURCE_NUM2,
0 SOURCE_WT2,
ACT_COUNT JUDGE_NUM,
ACT_WEIGHT JUDGE_WT,
0 FAIL_NUM,
0 FAIL_WT,
0 GROUP_NUM,
0 GROUP_WT,
0 END_NUM,
0 END_WT,
0 COUPLING_NUM,
0 COUPLING_WT,
0 XS_ACT_COUNT,
0 XS_ACT_WEIGHT
FROM QCM_ZG_JUGDE_APPLY T
WHERE T.IS_ALL_SCRAP = '5'
AND T.BAL_YEAR_MONTH = '202602'
AND T.PROCESS_CODE = 'G'
AND (((JUDGE_RESULT_CODE = '40740701' OR JUDGE_RESULT_CODE = '40740709' OR
EXISTS (SELECT 1
FROM QCM_BC_PASS_APPLY
WHERE JUGDE_APPLY_CODE = T.JUGDE_APPLY_CODE
AND JUDGE_STOVE_NO = T.JUDGE_STOVE_NO
AND BATCH_NO = T.BATCH_NO
AND VALIDFLAG = '1'
AND TYPE = '1')) AND TARGET <> 'A') OR
(TARGET = 'A' AND (JUDGE_TOLRESULT_CODE = '40740701' OR
JUDGE_TOLRESULT_CODE = '40740709')))
AND T.IS_FLAG = '0'
and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100')
UNION ALL
SELECT ---- 当月审核的废品 和当月组炉
M.PRODUCCODE,
M.PRODUCNAME,
M.STEELCODE,
M.STEELNAME,
M.GRADECODE,
M.GRADENAME,
M.OUTDIAMETER DIMATER,
M.WALLTHICK HEIGHT,
M.ORDER_NO || '/' || M.ORDER_SEQ PRO_ORDER_NO,
M.MODEL_CODE,
M.MODEL_DESC,
T.JUDGE_STOVE_NO,
T.BATCH_NO,
T.PLINE_CODE,
T.PLINE_NAME,
0 ACT_COUNT,
0 ACT_WEIGHT,
'' WATING_DECISION_MEMO,
0 SOURCE_NUM_TOTAL,
0 SOURCE_WT_TOTAL,
0 SOURCE_NUM0,
0 SOURCE_WT0,
0 SOURCE_NUM1,
0 SOURCE_WT1,
0 SOURCE_NUM2,
0 SOURCE_WT2,
0 JUDGE_NUM,
0 JUDGE_WT,
DECODE(T.MAT_STATUS, '10', 1, '40', 1, 0) FAIL_NUM,
DECODE(T.MAT_STATUS,
'10',
T.ACT_THEORY_WEIGHT,
'40',
T.ACT_THEORY_WEIGHT,
0) FAIL_WT,
DECODE(T.MAT_STATUS, '30', 1, 0) GROUP_NUM,
DECODE(T.MAT_STATUS, '30', T.ACT_THEORY_WEIGHT, 0) GROUP_WT,
0 END_NUM,
0 END_WT,
0 COUPLING_NUM,
0 COUPLING_WT,
0 XS_ACT_COUNT,
0 XS_ACT_WEIGHT
FROM PORT_MCH_BATCH_SAMPLE_RESULT T, PLN_ZY_JGX_M M
WHERE T.HEAT_PLAN_NO = M.HEAT_PLAN_NO
AND T.VALIDFLAG = '1' and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100')
AND IS_FAIL_CHECK = '1'
AND BAL_YEAR_MONTH_FAIL IS NOT NULL
AND BAL_YEAR_MONTH_FAIL = '202602'
UNION ALL
SELECT --- 当月判定支
M.PRODUCCODE,
M.PRODUCNAME,
M.STEELCODE,
M.STEELNAME,
M.GRADECODE,
M.GRADENAME,
M.OUTDIAMETER DIMATER,
M.WALLTHICK HEIGHT,
M.ORDER_NO || '/' || M.ORDER_SEQ PRO_ORDER_NO,
M.MODEL_CODE,
M.MODEL_DESC,
T.JUDGE_STOVE_NO,
T.BATCH_NO,
T.PLINE_CODE,
T.PLINE_NAME,
0 ACT_COUNT,
0 ACT_WEIGHT,
'' WATING_DECISION_MEMO,
0 SOURCE_NUM_TOTAL,
0 SOURCE_WT_TOTAL,
0 SOURCE_NUM0,
0 SOURCE_WT0,
0 SOURCE_NUM1,
0 SOURCE_WT1,
0 SOURCE_NUM2,
0 SOURCE_WT2,
ACT_COUNT JUDGE_NUM,
ACT_WEIGHT JUDGE_WT,
0 FAIL_NUM,
0 FAIL_WT,
0 GROUP_NUM,
0 GROUP_WT,
0 END_NUM,
0 END_WT,
0 COUPLING_NUM,
0 COUPLING_WT,
0 XS_ACT_COUNT,
0 XS_ACT_WEIGHT
FROM QCM_ZG_JUGDE_APPLY T, PLN_ZY_JGX_M M
WHERE T.HEAT_PLAN_NO = M.HEAT_PLAN_NO
AND T.BAL_YEAR_MONTH = '202602'
AND T.PROCESS_CODE = 'G'
AND (((JUDGE_RESULT_CODE = '40740701' OR JUDGE_RESULT_CODE = '40740709' OR
EXISTS (SELECT 1
FROM QCM_BC_PASS_APPLY
WHERE JUGDE_APPLY_CODE = T.JUGDE_APPLY_CODE
AND JUDGE_STOVE_NO = T.JUDGE_STOVE_NO
AND BATCH_NO = T.BATCH_NO
AND VALIDFLAG = '1'
AND TYPE = '1')) AND TARGET <> 'A') OR
(TARGET = 'A' AND (JUDGE_TOLRESULT_CODE = '40740701' OR
JUDGE_TOLRESULT_CODE = '40740709')))
AND T.IS_FLAG = '0'
AND T.IS_ALL_SCRAP IN ('0', '2', '7') and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100')
UNION ALL
SELECT ---已结算的期末
PRODUCCODE,
PRODUCNAME,
STEELCODE,
STEELNAME,
GRADECODE,
GRADENAME,
DIMATER,
HEIGHT,
PRO_ORDER_NO,
MODEL_CODE,
MODEL_DESC,
JUDGE_STOVE_NO,
BATCH_NO,
PLINE_CODE,
PLINE_NAME,
0 ACT_COUNT,
0 ACT_WEIGHT,
MEMO WATING_DECISION_MEMO,
0 SOURCE_NUM_TOTAL,
0 SOURCE_WT_TOTAL,
0 SOURCE_NUM0,
0 SOURCE_WT0,
0 SOURCE_NUM1,
0 SOURCE_WT1,
0 SOURCE_NUM2,
0 SOURCE_WT2,
0 JUDGE_NUM,
0 JUDGE_WT,
0 FAIL_NUM,
0 FAIL_WT,
0 GROUP_NUM,
0 GROUP_WT,
ACT_COUNT END_NUM,
ACT_WEIGHT END_WT,
0 COUPLING_NUM,
0 COUPLING_WT,
0 XS_ACT_COUNT,
0 XS_ACT_WEIGHT
FROM YDM_HTT_INITIAL T
WHERE T.BAL_YEAR_MONTH = '202603' and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100')
UNION ALL
SELECT --- 未结算的期末
M.PRODUCCODE,
M.PRODUCNAME,
M.STEELCODE,
M.STEELNAME,
M.GRADECODE,
M.GRADENAME,
M.OUTDIAMETER DIMATER,
M.WALLTHICK HEIGHT,
M.ORDER_NO || '/' || M.ORDER_SEQ PRO_ORDER_NO,
M.MODEL_CODE,
M.MODEL_DESC,
M.JUDGE_STOVE_NO,
T.BATCH_NO,
T.PLINE_CODE,
T.PLINE_NAME,
0 ACT_COUNT,
0 ACT_WEIGHT,
M.WAITING_DECISION_MEMO WATING_DECISION_MEMO,
0 SOURCE_NUM_TOTAL,
0 SOURCE_WT_TOTAL,
0 SOURCE_NUM0,
0 SOURCE_WT0,
0 SOURCE_NUM1,
0 SOURCE_WT1,
0 SOURCE_NUM2,
0 SOURCE_WT2,
0 JUDGE_NUM,
0 JUDGE_WT,
0 FAIL_NUM,
0 FAIL_WT,
0 GROUP_NUM,
0 GROUP_WT,
T.END_NUM,
T.END_WT,
0 COUPLING_NUM,
0 COUPLING_WT,
0 XS_ACT_COUNT,
0 XS_ACT_WEIGHT
FROM (SELECT SUM(END_NUM) END_NUM,
SUM(END_WT) END_WT,
HEAT_PLAN_NO,
JUDGE_STOVE_NO,
BATCH_NO,
PLINE_CODE,
PLINE_NAME
FROM (SELECT T.HEAT_PLAN_NO,
T.JUDGE_STOVE_NO,
T.PHY_BATCH_NO BATCH_NO,
T.PLINE_CODE,
T.PLINE_NAME,
T.ACT_COUNT * T.OUTNUM_CUT END_NUM,
ACT_THEORY_WEIGHT END_WT
FROM PORT_MCH_BATCH_SAMPLE_RESULT T
WHERE T.BAL_YEAR_MONTH < '202603'
AND T.BAL_YEAR_MONTH > '201810'
AND T.VALIDFLAG = '1'
AND T.MAT_STATUS = '20'
UNION ALL
SELECT T.HEAT_PLAN_NO,
T.JUDGE_STOVE_NO,
T.PHY_BATCH_NO BATCH_NO,
T.PLINE_CODE,
T.PLINE_NAME,
T.ACT_COUNT * T.OUTNUM_CUT END_NUM,
ACT_THEORY_WEIGHT END_WT
FROM PORT_MCH_BATCH_SAMPLE_RESULT T
WHERE T.BAL_YEAR_MONTH < '202603'
AND T.BAL_YEAR_MONTH > '201810'
AND T.BAL_YEAR_MONTH_FAIL >=
'202603'
AND T.VALIDFLAG = '1'
AND T.MAT_STATUS = '30'
UNION ALL
SELECT T.HEAT_PLAN_NO,
T.JUDGE_STOVE_NO,
T.PHY_BATCH_NO BATCH_NO,
T.PLINE_CODE,
T.PLINE_NAME,
T.ACT_COUNT * T.OUTNUM_CUT END_NUM,
ACT_THEORY_WEIGHT END_WT
FROM PORT_MCH_BATCH_SAMPLE_RESULT T
WHERE T.BAL_YEAR_MONTH < '202603'
AND T.BAL_YEAR_MONTH > '201807'
AND T.VALIDFLAG = '1'
AND T.MAT_STATUS IN ('10', '40')
AND (T.IS_FAIL_CHECK = '0' OR
T.BAL_YEAR_MONTH_FAIL >=
'202603')
UNION ALL
SELECT T.HEAT_PLAN_NO,
T.JUDGE_STOVE_NO,
T.PHY_BATCH_NO BATCH_NO,
T.PLINE_CODE,
T.PLINE_NAME,
T.ACT_COUNT * T.OUTNUM_CUT END_NUM,
ACT_THEORY_WEIGHT END_WT
FROM PORT_MCH_BATCH_SAMPLE_RESULT T
WHERE T.BAL_YEAR_MONTH < '202603'
AND T.BAL_YEAR_MONTH > '201807'
AND T.VALIDFLAG = '1'
AND T.MAT_STATUS = '00'
AND NOT EXISTS
(SELECT 1
FROM QCM_ZG_JUGDE_APPLY QCM
WHERE 1 = 1
AND IS_FLAG = '0'
AND IS_ALL_SCRAP IN ('0', '2', '5', '7')
AND PROCESS_CODE = 'G'
AND (((JUDGE_RESULT_CODE = '40740701' OR JUDGE_RESULT_CODE = '40740709' OR
EXISTS (SELECT 1
FROM QCM_BC_PASS_APPLY
WHERE JUGDE_APPLY_CODE = QCM.JUGDE_APPLY_CODE
AND JUDGE_STOVE_NO = QCM.JUDGE_STOVE_NO
AND BATCH_NO = QCM.BATCH_NO
AND VALIDFLAG = '1'
AND TYPE = '1')) AND TARGET <> 'A') OR
(TARGET = 'A' AND (JUDGE_TOLRESULT_CODE = '40740701' OR
JUDGE_TOLRESULT_CODE = '40740709')))
AND BAL_YEAR_MONTH <
'202603'
AND JUGDE_APPLY_CODE =
NVL(T.JUGDE_APPLY_CODE, '/')
AND JUDGE_STOVE_NO = T.JUDGE_STOVE_NO
AND BATCH_NO = T.PHY_BATCH_NO))
GROUP BY HEAT_PLAN_NO,
JUDGE_STOVE_NO,
BATCH_NO,
PLINE_CODE,
PLINE_NAME) T,
PLN_ZY_JGX_M M
WHERE T.HEAT_PLAN_NO = M.HEAT_PLAN_NO
AND NOT EXISTS
(SELECT 1
FROM YDM_HTT_INITIAL
WHERE PLINE_CODE = T.PLINE_CODE
AND BAL_YEAR_MONTH = '202603'
AND ROWNUM = 1) and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100')
UNION ALL
SELECT --- 当月接箍个数
M.PRODUCCODE,
M.PRODUCNAME,
M.STEELCODE,
M.STEELNAME,
M.GRADECODE,
M.GRADENAME,
M.OUTDIAMETER DIMATER,
M.WALLTHICK HEIGHT,
M.ORDER_NO || '/' || M.ORDER_SEQ PRO_ORDER_NO,
M.MODEL_CODE,
M.MODEL_DESC,
T.JUDGE_STOVE_NO,
T.BATCH_NO,
T.PLINE_CODE,
T.PLINE_NAME,
0 ACT_COUNT,
0 ACT_WEIGHT,
'' WATING_DECISION_MEMO,
0 SOURCE_NUM_TOTAL,
0 SOURCE_WT_TOTAL,
0 SOURCE_NUM0,
0 SOURCE_WT0,
0 SOURCE_NUM1,
0 SOURCE_WT1,
0 SOURCE_NUM2,
0 SOURCE_WT2,
0 JUDGE_NUM,
0 JUDGE_WT,
0 FAIL_NUM,
0 FAIL_WT,
0 GROUP_NUM,
0 GROUP_WT,
0 END_NUM,
0 END_WT,
COUPLING_NUM COUPLING_NUM,
COUPLING_WT COUPLING_WT,
0 XS_ACT_COUNT,
0 XS_ACT_WEIGHT
FROM MCH_TWISTINGLY_RESULT T, PLN_ZY_JGX_M M
WHERE T.HEAT_PLAN_NO = M.HEAT_PLAN_NO
AND T.BAL_YEAR_MONTH = '202602'
and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100')
UNION ALL
SELECT -- 短节精整利库 或返线 的 加上料支数
M.PRODUCCODE,
M.PRODUCNAME,
M.STEELCODE,
M.STEELNAME,
M.GRADECODE,
M.GRADENAME,
M.OUTDIAMETER DIMATER,
M.WALLTHICK HEIGHT,
M.ORDER_NO || '/' || M.ORDER_SEQ PRO_ORDER_NO,
M.MODEL_CODE,
M.MODEL_DESC,
M.JUDGE_STOVE_NO,
(SELECT MAX(batch_no) FROM pln_zy_jgx_c WHERE heat_plan_no=M.heat_plan_no ) ,
T.PLINE_CODE,
T.PLINE_NAME,
0 ACT_COUNT,
0 ACT_WEIGHT,
'' WATING_DECISION_MEMO,
M.PLN_INSINGLE_NUM SOURCE_NUM_TOTAL,
M.PLN_INSINGLE_WT SOURCE_WT_TOTAL,
DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = M.HEAT_PLAN_NO_OLD),'0',M.PLN_INSINGLE_NUM,0)
SOURCE_NUM0,
DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = M.HEAT_PLAN_NO_OLD),'0',M.PLN_INSINGLE_WT,0)
SOURCE_WT0,
DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = M.HEAT_PLAN_NO_OLD),'1',M.PLN_INSINGLE_NUM,0)
SOURCE_NUM1,
DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = M.HEAT_PLAN_NO_OLD),'1',M.PLN_INSINGLE_WT,0)
SOURCE_WT1,
DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = M.HEAT_PLAN_NO_OLD),'2',M.PLN_INSINGLE_NUM,0)
SOURCE_NUM2,
DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = M.HEAT_PLAN_NO_OLD),'2',M.PLN_INSINGLE_WT,0)
SOURCE_WT2,
0 JUDGE_NUM,
0 JUDGE_WT,
0 FAIL_NUM,
0 FAIL_WT,
0 GROUP_NUM,
0 GROUP_WT,
0 END_NUM,
0 END_WT,
0 COUPLING_NUM ,
0 COUPLING_WT,
0 XS_ACT_COUNT,
0 XS_ACT_WEIGHT
FROM PLN_ZY_JGX_M T,PLN_ZY_JGX_M M
WHERE T.HEAT_PLAN_NO = M.HEAT_PLAN_NO_OLD
AND M.IF_MATCH_ODD ='6'
AND M.BAL_YEAR_MONTH = '202602'
AND EXISTS
(SELECT *
FROM COM_BASE_PRODUCT
WHERE PRODUCCODE = M.PRODUCCODE
AND PRODUC_STYLE = '405004')
AND EXISTS (SELECT 1 FROM MCH_LOADING_RESULT L
WHERE L.HEAT_PLAN_NO = T.HEAT_PLAN_NO AND L.BAL_YEAR_MONTH ='202602')
AND EXISTS (SELECT 1 FROM PORT_MCH_BATCH_SAMPLE_RESULT WHERE HEAT_PLAN_NO = M.HEAT_PLAN_NO)
and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100')
UNION ALL -- 部分精整返线转短节 上料支数减去
SELECT
T.PRODUCCODE,
T.PRODUCNAME,
T.STEELCODE,
T.STEELNAME,
T.GRADECODE,
T.GRADENAME,
T.OUTDIAMETER DIMATER,
T.WALLTHICK HEIGHT,
T.ORDER_NO || '/' || T.ORDER_SEQ PRO_ORDER_NO,
T.MODEL_CODE,
T.MODEL_DESC,
T.JUDGE_STOVE_NO,
(SELECT MAX(batch_no) FROM pln_zy_jgx_c WHERE heat_plan_no=M.heat_plan_no ) ,
T.PLINE_CODE,
T.PLINE_NAME,
0 ACT_COUNT,
0 ACT_WEIGHT,
'' WATING_DECISION_MEMO,
0- M.PLN_INSINGLE_NUM SOURCE_NUM_TOTAL,
0 - M.PLN_INSINGLE_WT SOURCE_WT_TOTAL,
DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = T.HEAT_PLAN_NO),'0', 0- M.PLN_INSINGLE_NUM,0)
SOURCE_NUM0,
DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = T.HEAT_PLAN_NO),'0',0- M.PLN_INSINGLE_WT,0)
SOURCE_WT0,
DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = T.HEAT_PLAN_NO),'1',0-M.PLN_INSINGLE_NUM,0)
SOURCE_NUM1,
DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = T.HEAT_PLAN_NO),'1',0-M.PLN_INSINGLE_WT,0)
SOURCE_WT1,
DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = T.HEAT_PLAN_NO),'2',0-M.PLN_INSINGLE_NUM,0)
SOURCE_NUM2,
DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = T.HEAT_PLAN_NO),'2',0-M.PLN_INSINGLE_WT,0)
SOURCE_WT2,
0 JUDGE_NUM,
0 JUDGE_WT,
0 FAIL_NUM,
0 FAIL_WT,
0 GROUP_NUM,
0 GROUP_WT,
0 END_NUM,
0 END_WT,
0 COUPLING_NUM ,
0 COUPLING_WT,
0 XS_ACT_COUNT,
0 XS_ACT_WEIGHT
FROM PLN_ZY_JGX_M T,PLN_ZY_JGX_M M
WHERE T.HEAT_PLAN_NO = M.HEAT_PLAN_NO_OLD
AND M.IF_MATCH_ODD ='6'
AND M.BAL_YEAR_MONTH = '202602'
AND EXISTS
(SELECT *
FROM COM_BASE_PRODUCT
WHERE PRODUCCODE = M.PRODUCCODE
AND PRODUC_STYLE = '405004')
AND EXISTS (SELECT 1 FROM MCH_LOADING_RESULT L
WHERE L.HEAT_PLAN_NO = T.HEAT_PLAN_NO AND L.BAL_YEAR_MONTH ='202602')
AND EXISTS (SELECT 1 FROM PORT_MCH_BATCH_SAMPLE_RESULT WHERE HEAT_PLAN_NO = M.HEAT_PLAN_NO)
and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100')
UNION ALL
SELECT T.PRODUCCODE,
T.PRODUCNAME,
T.STEELCODE,
T.STEELNAME,
T.GRADECODE,
T.GRADENAME,
T.ACT_DIMATER DIMATER,
T.ACT_HEIGHT HEIGHT,
T.PRO_ORDER_NO,
T.MODEL_CODE,
T.MODEL_DESC,
T.JUDGE_STOVE_NO,
T.BATCH_NO,
T.PLINE_CODE,
T.PLINE_NAME,
0 ACT_COUNT,
0 ACT_WEIGHT,
'' WATING_DECISION_MEMO,
0 SOURCE_NUM_TOTAL,
0 SOURCE_WT_TOTAL,
0 SOURCE_NUM0,
0 SOURCE_WT0,
0 SOURCE_NUM1,
0 SOURCE_WT1,
0 SOURCE_NUM2,
0 SOURCE_WT2,
0 JUDGE_NUM,
0 JUDGE_WT,
0 FAIL_NUM,
0 FAIL_WT,
0 GROUP_NUM,
0 GROUP_WT,
0 END_NUM,
0 END_WT,
0 COUPLING_NUM,
0 COUPLING_WT,
T.ACT_COUNT XS_ACT_COUNT,
T.ACT_WEIGHT XS_ACT_WEIGHT
FROM YDM_ZC_INLIST T
WHERE T.BAL_YEAR_MONTH = '202602' and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100')
AND INSTOCK_TYPE_CODE != '800713'
)
WHERE 1 = 1
GROUP BY PRODUCCODE,
PRODUCNAME,
STEELCODE,
STEELNAME,
GRADECODE,
GRADENAME,
DIMATER,
HEIGHT,
PRO_ORDER_NO,
MODEL_CODE,
MODEL_DESC,
JUDGE_STOVE_NO,
BATCH_NO,
PLINE_CODE,
PLINE_NAME) ss
WHERE END_NUM > 0 and PRO_ORDER_NO not like '%ZZBY%'
group by JUDGE_STOVE_NO,PRO_ORDER_NO,BATCH_NO,PRODUCNAME,STEELNAME,GRADENAME,DIMATER,HEIGHT,MODEL_DESC,PLINE_CODE,PLINE_NAME
ORDER BY PLINE_CODE, JUDGE_STOVE_NO, BATCH_NO) j
;
curoporder_jg oporder_cursor_jg%rowtype;
begin
--先删除
/* delete from hot_production_mes_jg
where acct_period in ('2025-01', '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;
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 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_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 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_jg.orderid, 3)
and material_code is not null
and rownum = 1;
end if;
end if;
if a > 0 then
insert into hot_production_mes_jg
(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,judge_stove_no,PRE_PROCESS,DELIVERY_STATUS,INSERT_TIME)
values
(seq_mes_cx.nextval, curoporder_jg.contract_type,
prod_name_cx, sg_sign_cx,
sg_grade_cx, outer_diam_cx || '*' || wall_thickness_cx, '在产品',
0, curoporder_jg.mate_weight,
0, curoporder_jg.process,
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, material_code_cx,
curoporder_jg.resource_type,
--(case when curOPOrder_jg.BILLET_CODE like ),
(case
when curoporder_jg.mate_code like '%B' then
curoporder_jg.mate_code || '1'
when curoporder_jg.mate_code like '%C' then
curoporder_jg.mate_code || '2'
when curoporder_jg.mate_code like '%H' or curoporder_jg.mate_code like '%G' then
curoporder_jg.mate_code || '3'
end
), '', '0',MATERIAL_NAME_cx,curoporder_jg.orderid,curoporder_jg.judge_stove_no,(case
when material_code_cx like '%B' then
'热轧'
when material_code_cx like '%C' then
'热处理'
when curoporder_jg.mate_code like '%H' or curoporder_jg.mate_code like '%G' then
'加工'
end),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_production_mes_jg
(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,judge_stove_no)
values
(seq_mes_cx.nextval, curoporder_jg.contract_type,
curoporder_jg.prod_variety, curoporder_jg.steel_level,
curoporder_jg.steel_no, curoporder_jg.spec, '在产品',
0, curoporder_jg.mate_weight,
0, curoporder_jg.process,
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, curoporder_jg.mate_code,
curoporder_jg.resource_type,
--(case when curOPOrder_jg.BILLET_CODE like ),
(case when curoporder_jg.mate_code like '%B' then
curoporder_jg.mate_code || '1' when
curoporder_jg.mate_code like '%C' then
curoporder_jg.mate_code || '2' end), '', '0',null,curoporder_jg.orderid,curoporder_jg.judge_stove_no);
end if;
end loop;
close oporder_cursor_jg;
commit;
/*
exception
when others then
null;*/
end mes_cx_JG_zzpnew_hand;