MES_DOC/MES_FIN_MONTH/FinMES202602/mes_cx_JG_zzpnew_hand.sql

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, '{3}' 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 '{0}' 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 = '{0}' 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 = '{0}' 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 = '{0}'
                   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 = '{0}'

                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 = '{0}'

                   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 = '{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,
                 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 < '{1}'
                                   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 < '{1}'
                                   AND T.BAL_YEAR_MONTH > '201810'
                                   AND  T.BAL_YEAR_MONTH_FAIL >=
                                       '{1}'
                                   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 < '{1}'
                                   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 >=
                                       '{1}')
                                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 < '{1}'
                                   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 <
                                               '{1}'
                                           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 = '{1}'
                           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 = '{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,
                 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 = '{0}'
                               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 ='{0}')
                                            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 = '{0}'
                               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 ='{0}')
                                         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 = '{0}' 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;