XinYu_RZMES/.svn/pristine/be/be3a09bf7ef10d0500263a883efabfcb11059ec2.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="">
<query id="UIF024020.findRollMana" desc="求各炉次所接受的热轧板坯参数" fetchSize="10">
        <![CDATA[
/*****************************************************************************/
			
SELECT A.SPM_MANA_NO ROLL_EDT_SEQ,
       A.SPM_NO ROLL_MANA_NO,
       COUNT(A.SPM_EDT_SEQ) coilCnt
  FROM TBF05_SPEC_MILL A
 WHERE A.STATUS_CD IN ('A', 'B')
 GROUP BY SPM_MANA_NO, SPM_NO
 ORDER BY SPM_NO ASC
			
/*****************************************************************************/
        ]]>
</query> 
	<query id="UIF024010.findCoilWth" desc="求轧制单元板坯宽度结果集 按升序排列" fetchSize="10">
        <![CDATA[
			
			/*求轧制单元板坯宽度结果集 按升序排列  动态生成宽度的刻度*/
       SELECT DISTINCT T.ORD_WTH 
       FROM TBF02_SPEC_MILL T 
       ORDER BY T.ORD_WTH ASC
			
        ]]>
	</query> 
   <query id="UIF024020.findChart" desc="求轧制单元板坯宽度结果集 按升序排列" fetchSize="10">
        <![CDATA[
			
/******************************************************************************/
			

SELECT A.SPM_NO ROLL_MANA_NO, --轧辊号
       A.SPM_EDT_SEQ ROLL_SLAB_SEQ, --轧辊内顺序
       A.COIL_NO SLAB_NO, --板坯号
       A.COIL_THK HOT_THK_AIM, --轧制厚度目标
       A.COIL_WTH WTH_AIM, --轧制宽度目标
       '' HCR_CLF, --热送状态
       A.STATUS_CD, --进行状态
       B.COIL_STAT --钢卷状态
  FROM tbf05_spec_mill A, TBH02_COIL_COMM B
 WHERE A.STATUS_CD IN ('A', 'B')
   AND A.COIL_NO = B.COIL_NO(+)
 ORDER BY ROLL_MANA_NO ASC, ROLL_SLAB_SEQ ASC 
			
/******************************************************************************/
			
        ]]>
	</query> 
		
	 <query id="UIF024020.findBehindCoilInfos" desc="求轧制单元板坯宽度结果集 按升序排列" fetchSize="10">
        <![CDATA[			
/***************************************************************************************/

SELECT A.SPM_NO ROLL_MANA_NO, -- 轧辊号
       A.SPM_EDT_SEQ ROLL_SLAB_SEQ, -- 轧辊内顺序
       --A.SLAB_NO, -- 板坯号
       A.COIL_NO, --钢卷号    
       A.COIL_THK HOT_THK_AIM, --热厚度AIM
    
       A.COIL_WTH WTH_AIM, --宽度AlM
    
       A.SPEC_STL_GRD STL_GRD, --钢码
   	  (SELECT T.SM_CFNM
		          FROM TBZ00_COMMCD T
		         WHERE T.LG_CD = 'H02001'
		           AND T.SM_CD = B.CRK_CD1)  SLAB_THK,
      (SELECT T.SM_CFNM
		          FROM TBZ00_COMMCD T
		         WHERE T.LG_CD = 'H02001'
		           AND T.SM_CD = B.CRK_CD2)  SLAB_WTH,
       (SELECT T.SM_CFNM
		          FROM TBZ00_COMMCD T
		         WHERE T.LG_CD = 'H02001'
		           AND T.SM_CD = B.CRK_CD3)  SLAB_LEN,
      (SELECT T.SM_CFNM
		          FROM TBZ00_COMMCD T
		         WHERE T.LG_CD = 'H02001'
		           AND T.SM_CD = B.CRK_CD4)  SLAB_WGT,
      (SELECT T.SM_CFNM
		          FROM TBZ00_COMMCD T
		         WHERE T.LG_CD = 'H02001'
		           AND T.SM_CD = B.CRK_CD5)  HOT_THK_MIN,
       DECODE(DECODE(A.STATUS_CD,
                     'B',
                     DECODE(B.CUR_PROG_CD,
                            'RBB',
                            '加热炉出炉待机',
                            T1.SM_CFNM),
                     T1.SM_CFNM),
              '',
              '板坯切割待机',
              DECODE(A.STATUS_CD,
                     'B',
                     DECODE(B.CUR_PROG_CD,
                            'RBB',
                            '加热炉出炉待机',
                            T1.SM_CFNM),
                     T1.SM_CFNM)) CUR_PROG_CD, -- 当前进程   
       T2.SM_CFNM STATUS_CD, --   状态区分
       B.CUR_LOAD_LOC, -- 堆置位置
       --T3.SM_CFNM HCR_CLF, -- 热送状态
       A.ORD_NO, --   合同号
       A.ORD_SEQ, --   订单号
       A.SPEC_ABBSYM, --  标准号
       T4.SM_CFNM ORD_USEAGE_CD, --   订单用途代码
       A.DELV_REQ_DATE, --   交货期
       --T5.SM_CFNM ROUTE, --    去向
       E.EMGMTR_NMG_CLF, --  紧急材
    
       T6.SM_CFNM DLIV_TP -- 发货方式
      ,
       DECODE(K.DSN_KIND, '1', 'Y', 'N') AS INGR_YN,
       DECODE(QL.DSN_KIND, '1', 'Y', 'N') AS QLTY_YN --材质特殊要求
      ,
       DECODE(DE.DSN_KIND, '1', 'Y', 'N') AS DEL_YN --交付条件特殊要求 
    
  FROM TBF05_SPEC_MILL A,
       tbh02_coil_comm B,
       (SELECT T.SM_CD, T.SM_CFNM
          FROM TBZ00_COMMCD T
         WHERE T.LG_CD = 'E01002') T1,
       (SELECT T.SM_CD, T.SM_CFNM
          FROM TBZ00_COMMCD T
         WHERE T.LG_CD = 'Z01001') T2,
     
       (SELECT T.SM_CD, T.SM_CFNM
          FROM TBZ00_COMMCD T
         WHERE T.LG_CD = 'A01007') T4,
     
       TBE02_ORD_PRC E,
       (SELECT SM_CD, SM_CFNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01012') T6,
       TBA01_ORD_LINE A01,
       (SELECT DISTINCT ORD_NO, ORD_SEQ, DSN_KIND
          FROM TBB01_ORD_INGR
         WHERE DSN_KIND = '1') K,
       (SELECT DISTINCT ORD_NO, ORD_SEQ, DSN_KIND
          FROM TBB01_ORD_QLTY
         WHERE DSN_KIND = '1') QL --材质特殊要求
      ,
       (SELECT DISTINCT ORD_NO, ORD_SEQ, DSN_KIND
          FROM TBB01_ORD_DEL
         WHERE DSN_KIND = '1') DE --交付条件特殊要求
 WHERE A.ORD_NO = A01.ORD_NO(+)
   AND A.ORD_SEQ = A01.ORD_SEQ(+)
   AND A01.DLIV_TP = T6.SM_CD(+)
   AND A.ORD_NO = E.ORD_NO
   AND A.ORD_SEQ = E.ORD_SEQ
   AND A.ORD_NO = K.ORD_NO(+)
   AND A.ORD_SEQ = K.ORD_SEQ(+)
   AND A.ORD_NO = QL.ORD_NO(+)
   AND A.ORD_SEQ = QL.ORD_SEQ(+)
   AND A.ORD_NO = DE.ORD_NO(+)
   AND A.ORD_SEQ = DE.ORD_SEQ(+)
   AND A.STATUS_CD IN ('A', 'B') --处于指示和作业
   AND A.COIL_NO = B.OLD_SAMPL_NO(+)
   AND B.CUR_PROG_CD = T1.SM_CD(+)
   AND A.STATUS_CD = T2.SM_CD
    AND A.SPM_NO || LPAD(TO_CHAR(A.SPM_EDT_SEQ), 4, 0) >=
	   ? || LPAD(TO_CHAR(?), 4, 0)
 --  AND A.HCR_CLF = T3.SM_CD
   AND b.ORD_USE_CD = T4.SM_CD(+)
   ORDER BY A.SPM_NO ASC, A.SPM_EDT_SEQ ASC



/***************************************************************************************/			
        ]]>
    </query> 
	
	<query id="UIF024010.findCoilThk" desc="求轧制单元轧制厚度结果集 按升序排列 动态生成轧制厚度的刻度" fetchSize="10">
        <![CDATA[
			
			/*求轧制单元轧制厚度结果集 按升序排列*/
       SELECT DISTINCT T.ORD_THK
       FROM TBF02_SPEC_MILL T 
       ORDER BY T.ORD_THK ASC			
        ]]>
    </query>	
	
	<query id="UIF024010.rht_l2_resend_by_R_update" desc="求轧制单元轧制厚度结果集 按升序排列 动态生成轧制厚度的刻度" fetchSize="10">
        <![CDATA[
			
  UPDATE TBH06_L2_RHT_INS T
  SET T.MSG_FLAG = 'N'
 WHERE T.SLAB_NO       =(SELECT X.SLAB_NO 
                          FROM ( SELECT T1.SLAB_NO ,MAX(T1.TRANS_DTIME) MAX_TREANS_DTIME
                                   FROM TBH06_L2_RHT_INS T1
                                GROUP BY T1.SLAB_NO)X
                         WHERE X.SLAB_NO            =  T.SLAB_NO
                           AND T.TRANS_DTIME   = X.MAX_TREANS_DTIME)

   AND T.SEND_CLF      IN ('1','2')
   AND T.ROLL_UNIT_NO BETWEEN ? AND ? 			
        ]]>
    </query>	
	
	<query id="UIF024010.rht_l2_resend_by_S_update" desc="求轧制单元轧制厚度结果集 按升序排列 动态生成轧制厚度的刻度" fetchSize="10">
        <![CDATA[
			
  UPDATE TBH06_L2_RHT_INS T
  SET T.MSG_FLAG = 'N'
 WHERE T.SLAB_NO       =(SELECT X.SLAB_NO 
                          FROM ( SELECT T1.SLAB_NO ,MAX(T1.TRANS_DTIME) MAX_TREANS_DTIME
                                   FROM TBH06_L2_RHT_INS T1
                                GROUP BY T1.SLAB_NO)X
                         WHERE X.SLAB_NO            =  T.SLAB_NO
                           AND T.TRANS_DTIME   = X.MAX_TREANS_DTIME)

   AND T.SEND_CLF      IN ('1','2')
   AND T.ROLL_UNIT_SEQ BETWEEN to_number(?) AND to_number(?) 
   AND T.ROLL_UNIT_NO  = ?
					
        ]]>
    </query>
	
	<query id="UIF024010.mill_l2_resend_by_R_update" desc="求轧制单元轧制厚度结果集 按升序排列 动态生成轧制厚度的刻度" fetchSize="10">
        <![CDATA[
			
  UPDATE TBH06_L2_MILL_INS T
  SET T.MSG_FLAG = 'N'
 WHERE T.SLAB_NO       =(SELECT X.SLAB_NO 
                          FROM ( SELECT T1.SLAB_NO ,MAX(T1.TRANS_DTIME) MAX_TREANS_DTIME
                                   FROM TBH06_L2_MILL_INS T1
                                GROUP BY T1.SLAB_NO)X
                         WHERE X.SLAB_NO            =  T.SLAB_NO
                           AND T.TRANS_DTIME   = X.MAX_TREANS_DTIME)

   AND T.TRANS_FLAG      IN ('1','2')
   AND T.ROLL_MANA_NO BETWEEN ? AND ? 
					
        ]]>
    </query>
	
		<query id="UIF024010.mill_l2_resend_by_S_update" desc="求轧制单元轧制厚度结果集 按升序排列 动态生成轧制厚度的刻度" fetchSize="10">
        <![CDATA[
  UPDATE TBH06_L2_MILL_INS T
  SET T.MSG_FLAG = 'N'
 WHERE T.SLAB_NO       =(SELECT X.SLAB_NO 
                          FROM ( SELECT T1.SLAB_NO ,MAX(T1.TRANS_DTIME) MAX_TREANS_DTIME
                                   FROM TBH06_L2_MILL_INS T1
                                GROUP BY T1.SLAB_NO)X
                         WHERE X.SLAB_NO            =  T.SLAB_NO
                           AND T.TRANS_DTIME   = X.MAX_TREANS_DTIME)

   AND T.TRANS_FLAG      IN ('1','2')
   AND ROLL_SLAB_SEQ BETWEEN to_number(?) AND to_number(?) 
   AND T.ROLL_MANA_NO  = ?
					
        ]]>
    </query>
	
 <query id="UIF024040_RollManaNo.select" desc="it's query from TBF02_SPEC_MILL Table" fetchSize="10">
	   <![CDATA[
			SELECT  A.ROLL_MANA_NO ROLL_MANA_ST, A.SLAB_EDT_SEQ SLAB_EDT_SEQ_ST, 
                    B.ROLL_MANA_NO ROLL_MANA_EN, B.SLAB_EDT_SEQ SLAB_EDT_SEQ_EN
              FROM  (SELECT ROLL_MANA_NO, SLAB_EDT_SEQ FROM TBF02_SPEC_MILL WHERE SLAB_NO = ?) A
                   ,(SELECT ROLL_MANA_NO, SLAB_EDT_SEQ FROM TBF02_SPEC_MILL WHERE SLAB_NO = ?) B
       ]]>
   </query>
	
	 <query id="UIF024040_RollSlabSeqFind.select" desc="it's query from TBF02_SPEC_MILL Table" fetchSize="10">
	   <![CDATA[
			SELECT  A.ROLL_SLAB_SEQ ROLL_SLAB_SEQ_ST
                   ,B.ROLL_SLAB_SEQ ROLL_SLAB_SEQ_EN
              FROM  (SELECT ROLL_SLAB_SEQ FROM TBF02_SPEC_MILL WHERE SLAB_NO = ?) A
                   ,(SELECT ROLL_SLAB_SEQ FROM TBF02_SPEC_MILL WHERE SLAB_NO = ?) B

       ]]>
   </query>
   
   <query id="UIF024010.findCurLoadLoc" desc="求轧制单元轧制厚度结果集 按升序排列 动态生成轧制厚度的刻度" fetchSize="10">
        <![CDATA[
			
			/*查询当前计划生产所有坯料的放置垛位*/
        SELECT SUBSTR(t1.CUR_LOAD_LOC,1,6) cur_load_loc,count(1) cnt  
        FROM tbf02_spec_mill t
		     ,tbg02_slab_comm t1
		WHERE 1=1--t.ROLL_MANA_NO = 'A3190'
		AND T.STATUS_CD = 'A'
		and t1.SLAB_STAT = '2'
		AND T1.CUR_LOAD_LOC <> 'A7T'
		and t.SLAB_NO = t1.SLAB_NO
		group by SUBSTR(t1.CUR_LOAD_LOC,1,6)			
        ]]>
    </query>	
</queryMap>