XinYu_RZMES/.svn/pristine/e8/e874b4a6b686fd38f8646c31ffb599040eeebbd1.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
	<query id="UIH051700_01.select" desc="  ">
        <![CDATA[
     
select substr(A.OLD_SAMPL_NO, 1, 10) SPM_NO,
       XB_PACKAGE.GF_COMNNAME_FIND('A01004', c.PRD_TY) PRD_TY, -- 产品类型
       A.OLD_SAMPL_NO,
       A.ORD_NO,
       A.ORD_SEQ,
       decode(A.MILL_SHIFT, '0', '常白班', '1', '早', '2','中', '3', '晚','') MILL_SHIFT,
       decode(A.MILL_GROUP, '1', '甲', '2', '乙', '3','丙', '4', '丁','') MILL_GROUP,
       A.ORG_ORD_NO,
       A.ORG_ORD_SEQ,
       (select CUST_NM
          FROM TBZ00_CUSTOMER
         WHERE CUST_CD =
               (SELECT CUST_CD FROM TBA01_ORD_COMM WHERE ORD_NO = A.ORD_NO)
           and REC_TP = '01') CUST_NM, --合同单位
       C.DEL_TO_DATE, --交货期
       substr(a.SLAB_NO, 1, 10) CHARGE_NO,
       A.SPEC_STL_GRD,
       D.SLAB_THK,
       D.SLAB_WTH,
       D.SLAB_LEN,
       D.SLAB_STOCK_ENT_DTIME, --来料时间
       A.MILL_DTIME,
       DECODE(B.WKSHIFT, '1', '早', '2', '中', '3', '晚', null) WKSHIFT,
       DECODE(B.WKGROUP, 'A', '甲', 'B', '乙', 'C', '丙', 'D', '丁', null) WKGROUP,
       DECODE(A.CUR_PROG_CD,
              'SRC',
              '充当待机',
              'SED',
              '综合判定待机',
              'SFA',
              '发货待机',
              'SFB',
              '运送待机',
              'SFF',
              '发货完成',
              null) COIL_STAT,
       B.WK_EMP_USE_NO,
       B.WK_EMP_USE_DTIME,
       A.YARD_ENTRY_REG,
       A.YARD_ENTRY_USE_TIME,
       A.COIL_THK,
       A.COIL_WTH,
       A.INSTR_COIL_THK,
       A.INSTR_COIL_WTH,
       
       TO_CHAR(A.ACT_WGT / 1000) ACT_WGT,
       a.CAL_WGT,
       A.CUR_LOAD_LOC

  from tbh02_coil_comm   a,
       tbh02_mill_result b,
       tbe02_ord_prc     c,
       TBG02_SLAB_COMM   d
 where a.COIL_NO = b.COIL_NO(+)
   AND A.ORD_NO = C.ORD_NO(+)
   AND A.ORD_SEQ = C.ORD_SEQ(+)
   and A.SLAB_NO = D.SLAB_NO
   AND SUBSTR(A.MILL_DTIME, 1, 12) >= :1
   AND SUBSTR(A.MILL_DTIME, 1, 12) <= :2
   AND NVL(A.ORG_ORD_NO, '1') LIKE :3 || '%'
   AND NVL(A.ORG_ORD_SEQ, '1') LIKE :4 || '%'
   AND B.MISSNO_CLF_CD IS NULL
 order by a.MILL_DTIME
		]]>
    </query>
    <query id="UIH051701_01.select" desc="  ">
        <![CDATA[
        /*
select substr(A.OLD_SAMPL_NO,1,10) SPM_NO ,
       A.OLD_SAMPL_NO,
       A.ORD_NO,
       A.ORD_SEQ,
       substr(a.SLAB_NO,1,10) CHARGE_NO,
       A.SPEC_STL_GRD,
       (SELECT C.SLAB_THK||'*'||C.SLAB_WTH FROM TBG02_SLAB_COMM C WHERE C.SLAB_NO = A.SLAB_NO) ST_SW,
       A.SPM_DTIME,
       a.SPM_SHIFT,
       a.SPM_GROUP,
       DECODE(A.CUR_PROG_CD,'SRC','充当待机','SED','综合判定待机','SFA','发货待机','SFB','运送待机','SFF','发货完成',null) COIL_STAT,
       '' WK_EMP_USE_NO,
       '' WK_EMP_USE_DTIME,
       A.YARD_ENTRY_REG,
       A.YARD_ENTRY_USE_TIME,
       A.COIL_THK||'*'||A.COIL_WTH CT_CW,
       A.INSTR_COIL_THK||'*'||A.INSTR_COIL_WTH IT_IW,
       TO_CHAR(A.ACT_WGT/1000) ACT_WGT,
       A.CUR_LOAD_LOC
        
       
       from tbh02_coil_comm a
where SUBSTR(A.SPM_DTIME, 1, 8) >= :1
   AND SUBSTR(A.SPM_DTIME, 1, 8) <= :2
   AND nvl(a.SPM_SHIFT, '空') like  nvl(:3, '%')
   AND nvl(a.SPM_GROUP, '空') like  nvl(:4, '%')
   AND A.OLD_SAMPL_NO LIKE :5||'%'
order by A.MILL_DTIME */
select substr(A.OLD_SAMPL_NO, 1, 10) SPM_NO,
       A.OLD_SAMPL_NO,
       XB_PACKAGE.GF_COMNNAME_FIND('A01004', c.PRD_TY) PRD_TY, -- 产品类型
       A.ORD_NO,
       A.ORD_SEQ,
       (select CUST_NM
          FROM TBZ00_CUSTOMER
         WHERE CUST_CD =
               (SELECT CUST_CD FROM TBA01_ORD_COMM WHERE ORD_NO = A.ORD_NO)
           and REC_TP = '01') CUST_NM, --合同单位
       C.DEL_TO_DATE, --交货期
       substr(a.SLAB_NO, 1, 10) CHARGE_NO,
       A.SPEC_STL_GRD,
       A.SPM_DTIME,
       a.SPM_SHIFT,
       a.SPM_GROUP,
       D.SLAB_THK,
       D.SLAB_WTH,
       D.SLAB_LEN,
       A.EXTSHAPE_DEC_GRD,
       A.TOT_DEC_GRD,
       DECODE(A.CUR_PROG_CD,
              'SRC',
              '充当待机',
              'SED',
              '综合判定待机',
              'SFA',
              '发货待机',
              'SFB',
              '运送待机',
              'SFF',
              '发货完成',
              null) COIL_STAT,
       '' WK_EMP_USE_NO,
       '' WK_EMP_USE_DTIME,
       A.YARD_ENTRY_REG,
       A.YARD_ENTRY_USE_TIME,
       A.COIL_THK,
       A.COIL_WTH,
       A.INSTR_COIL_THK,
       A.INSTR_COIL_WTH,
       TO_CHAR(A.ACT_WGT / 1000) ACT_WGT,
       A.SPM_YN,            --SMP是否作业
       A.CUR_LOAD_LOC

  from tbh02_coil_comm a, tbe02_ord_prc c, TBG02_SLAB_COMM d
 where SUBSTR(A.SPM_DTIME, 1, 8) >= :1
   AND SUBSTR(A.SPM_DTIME, 1, 8) <= :2
   AND A.ORD_NO = C.ORD_NO(+)
   AND A.ORD_SEQ = C.ORD_SEQ(+)
   AND A.SLAB_NO = D.SLAB_NO
   AND nvl(a.SPM_SHIFT, '空') like nvl(:3, '%')
   AND nvl(a.SPM_GROUP, '空') like nvl(:4, '%')
   AND A.OLD_SAMPL_NO LIKE :5|| '%'
 order by A.MILL_DTIME
		]]>
    </query>
	<query id="UIH051700_02.select" desc=" 统计总数 ">
        <![CDATA[
  select COUNT(A.OLD_SAMPL_NO) COIL_CNT,
        TO_CHAR(SUM(A.ACT_WGT)/1000) COIL_WGT  
        from tbh02_coil_comm a,tbh02_mill_result b
where a.COIL_NO = b.COIL_NO(+) 
AND   SUBSTR(A.MILL_DTIME,1,12) >= :1
AND   SUBSTR(A.MILL_DTIME,1,12) <= :2
AND   NVL(A.ORG_ORD_NO, '1') LIKE :3 || '%'
AND   NVL(A.ORG_ORD_SEQ, '1') LIKE :4 || '%'
AND   B.MISSNO_CLF_CD IS NULL 
order by A.MILL_DTIME        
		]]>
    </query>
    <query id="UIH051701_02.select" desc=" 统计总数 ">
        <![CDATA[
  
select COUNT(A.OLD_SAMPL_NO) COIL_CNT,
        TO_CHAR(SUM(A.ACT_WGT)/1000) COIL_WGT 
       
       from tbh02_coil_comm a
where SUBSTR(A.SPM_DTIME, 1, 8) >= :1
   AND SUBSTR(A.SPM_DTIME, 1, 8) <= :2
   AND nvl(a.SPM_SHIFT, '空') like  nvl(:3, '%')
   AND nvl(a.SPM_GROUP, '空') like  nvl(:4, '%')
   AND A.OLD_SAMPL_NO LIKE :5||'%'
order by A.MILL_DTIME       
		]]>
    </query>
    
    <query id="UIH051700_01.update" desc=" 统计总数 ">
        <![CDATA[
   update tbh02_coil_comm t set t.MILL_SHIFT=?,t.MILL_GROUP=? where t.OLD_SAMPL_NO=?       
		]]>
    </query>
</queryMap>