XinYu_RZMES/.svn/pristine/c0/c062b3f71c41ab1629f0b6dbdc5d5e75acf115da.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="热轧管理">
	<query id="rollwork.find" desc="查询轧制作业指示" fetchSize="10">
        <![CDATA[

SELECT   A.ROLL_MANA_NO        -- 轧辊管理号             
		,A.ROLL_SLAB_SEQ       -- 轧辊内顺序        
		,A.SLAB_NO             -- 板坯号  
	    ,A.COIL_NO             --钢卷号             
		,A.SLAB_THK            --板坯厚度
		,A.SLAB_WTH            --板坯宽度
		,A.SLAB_LEN            --板坯长度
		,A.SLAB_WGT            --板坯重量     
		,A.HOT_THK_AIM         --热厚度AIM
		,A.HOT_THK_MIN         --热厚度MIN
		,A.HOT_THK_MAX         --热厚度MAX
		,A.WTH_AIM             --宽度AlM
		,A.WTH_MIN             --宽度MIN
		,A.WTH_MAX             --宽度MAX  
		,A.MILL_LEN            --轧制长度
		,A.MILL_WGT            --轧制重量   
		,A.SPEC_STL_GRD   STL_GRD       --钢码             
		,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               -- 去向              
		,A.FUR_IN_PRE_DTIME    -- 计划加热炉装炉日期
        ,NVL(E.EMGMTR_NMG_CLF,'N') EMGMTR_NMG_CLF
		,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   TBF02_SPEC_MILL A  
      ,TBG02_SLAB_COMM B,TBE02_ORD_PRC   E
      ,(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 =  'E01012') T3
	  ,(SELECT T.SM_CD, T.SM_CFNM FROM TBZ00_COMMCD T  WHERE T.LG_CD =  'A01007') T4
      ,(SELECT T.SM_CD, T.SM_CFNM FROM TBZ00_COMMCD T  WHERE T.LG_CD =  'B01004') T5
	  ,(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           = 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.SLAB_NO        =  B.SLAB_NO(+)
		AND   B.CUR_PROG_CD    =  T1.SM_CD(+)  
		AND   A.STATUS_CD      =  T2.SM_CD
		AND   A.HCR_CLF        =  T3.SM_CD 
		AND   A.ORD_USEAGE_CD  =  T4.SM_CD  
		AND   A.ROUTE          =  T5.SM_CD
        AND A.STATUS_CD        in ('A','B')
        AND A.SLAB_NO          = B.SLAB_NO(+)
      ORDER   BY  A.ROLL_MANA_NO, 
                  A.ROLL_SLAB_SEQ
			
        ]]>
    </query>  
	
	<query id="rollwork_bb.findRollManaNo" desc="查询轧制作业指示" fetchSize="10">
        <![CDATA[

SELECT * FROM (
				   SELECT  '%'    AS ROLL_MANA_NO 
						  , '' AS ROLL_MANA_NO_2
						  , 0 seq
					FROM DUAL
					WHERE 1 = 1
					UNION ALL        
					SELECT DISTINCT T.ROLL_MANA_NO, 
									T.ROLL_MANA_NO AS ROLL_MANA_NO_2 
									,row_number() over (ORDER BY roll_mana_no DESC)  seq
					FROM TBF02_SPEC_ROLL T 
				ORDER BY SEQ ASC,ROLL_MANA_NO DESC )  AA
 WHERE  SEQ <21

			
		]]>
    </query> 
	<query id="rollwork_bb.find" desc="轧制计划查询" fetchSize="10">
        <![CDATA[


		/*SELECT   A.ROLL_SLAB_SEQ SEQ --流水号        
        ,A.COIL_NO
        ,u.USER_NM
        ,a.MOD_DTIME
        ,DECODE(H.SMP_DIR_PIC_YN,'Y',SUBSTR(H.OLD_SAMPL_NO,1,10),'')   PIHAO                 -- 批号                          
        ,ROW_NUMBER() OVER ( PARTITION BY SUBSTR(H.OLD_SAMPL_NO,1,10) ORDER BY H.OLD_SAMPL_NO) PIHAO_SEQ            -- 序号
        ,(SELECT  X.STL_GRD   FROM TBF01_SPEC_SLAB X WHERE X.SLAB_MANA_NO =B.PLAN_SLAB_NO AND ROWNUM = 1 )    LG_STL_GRD
        ,A.SPEC_STL_GRD                                  STL_GRD       --钢码              
        ,A.SLAB_THK                                                    --板坯厚度
        ,A.SLAB_WTH                                                     --板坯宽度
        ,A.SLAB_LEN                                                     --板坯长度
        ,A.SLAB_WGT                                                     --板坯重量     
        ,A.ORD_THK  HOT_THK_AIM                                         --热厚度AIM
        ,A.ORD_WTH  WTH_AIM                                             --宽度AlM
        ,A.SLAB_NO
       -- ,DECODE(B.SLAB_STOCK_TYPE,3,'加回',4,'轧回','') PLLX    --20130402添加板坯类型
        ,DECODE(B.MISSNO_CLF,'2','加回','3','轧回','') PLLX 
        ,H.OLD_SAMPL_NO GJH             
        ,DECODE(H.SMP_DIR_PIC_YN,'Y','*','')             QY
        ,SUBSTR(A.SLAB_NO,1,10)                          CHARGE_NO
        ,T2.SM_CFNM                                      BZ
        ,T3.SM_CFNM ORDCUST_CD
        ,T1.SM_CFNM
        ,A.COIL_NO                                                      --钢卷号  
        ,CASE  WHEN (A.STATUS_CD = 'A' AND H.COIL_STAT IS NULL )    THEN '轧制作业待机'
               WHEN (A.STATUS_CD = 'A' AND H.COIL_STAT = '0')       THEN '上料进行'
               WHEN (A.STATUS_CD = 'D'                           )  THEN '轧制吊销'
               WHEN (A.STATUS_CD = 'B' )                            THEN '轧制进行'
               WHEN (A.STATUS_CD = 'C' AND H.MISSNO_CLF_CD IS NULL) THEN '轧制完成'
               WHEN (A.STATUS_CD = 'C' AND H.MISSNO_CLF_CD =  'C')  THEN '轧废或中板'
               ELSE '轧制完成'                                      END    STATUS_CD
        ,A.ROLL_MANA_NO                                                       ROLLMANANUMBER
        ,A.ORD_NO
        ,A.ORD_SEQ
        ,ROUND((((SYSDATE - TO_DATE(B.SLAB_CUT_DTIME,'YYYY-MM-DD HH24:MI:SS')) - (SYSDATE - TO_DATE(B.SLAB_STOCK_EXT_DTIME,'YYYY-MM-DD HH24:MI:SS')) )*24),2) TIMES
        ,ROUND((((SYSDATE - TO_DATE(B.SLAB_CUT_DTIME, 'YYYY-MM-DD HH24:MI:SS')) ) * 24), 2) TIMES1
        ,H.CUR_LOAD_LOC
        ,H.ACT_WGT
        ,h.WGT_TIME
        ,B.CUR_LOAD_LOC  SLAB_LOAD_LOC
		,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  --交付条件特殊要求	
		,H.MILL_DTIME
		,DECODE(B.SLAB_INGR_DEC_GRD,'0','待判定','1','合格','2','不合格','') SLAB_INGR_DEC_GRD     
		,E.PRODNM_CD                                                 --交货状态
    	,E.C_PRODNM_CD                                               --交货状态
    	,E.PRD_TY                                                    --产品类型 
    	,E.DEL_TO_DATE				--交货期
    	,L.APPLICATION
    	,B.WEIGHT_WGT
    	,H.DCS_THK
    	,H.DCS_WTH
    	,tH.INFUR_HOUR
    	,
       CASE
         WHEN B.WEIGHT_WGT IS NULL OR B.WEIGHT_WGT = 0 THEN
          0
         ELSE
          ROUND(NVL(H.ACT_WGT / 1000, '0') / B.WEIGHT_WGT,4)*100 
       END CCL*/
SELECT A.ROLL_MANA_NO  ROLLMANANUMBER,
       A.ROLL_SLAB_SEQ SEQ,
       A.COIL_NO,
       A.SLAB_NO
       -- ,DECODE(B.SLAB_STOCK_TYPE,3,'加回',4,'轧回','') PLLX    --20130402添加板坯类型
      ,
       ROUND((((SYSDATE -
             TO_DATE(B.SLAB_CUT_DTIME, 'YYYY-MM-DD HH24:MI:SS')) -
             (SYSDATE -
             TO_DATE(B.SLAB_STOCK_EXT_DTIME, 'YYYY-MM-DD HH24:MI:SS'))) * 24),
             2) TIMES,
       ROUND((((SYSDATE -
             TO_DATE(B.SLAB_CUT_DTIME, 'YYYY-MM-DD HH24:MI:SS'))) * 24),
             2) TIMES1,
       H.OLD_SAMPL_NO GJH,
       DECODE(H.SMP_DIR_PIC_YN, 'Y', SUBSTR(H.OLD_SAMPL_NO, 1, 10), '') PIHAO -- 批号                          
      ,
       ROW_NUMBER() OVER(PARTITION BY SUBSTR(H.OLD_SAMPL_NO, 1, 10) ORDER BY H.OLD_SAMPL_NO) PIHAO_SEQ -- 序号
      ,
       DECODE(H.SMP_DIR_PIC_YN, 'Y', '*', '') QY,
       (SELECT X.STL_GRD
          FROM TBF01_SPEC_SLAB X
         WHERE X.SLAB_MANA_NO = B.PLAN_SLAB_NO
           AND ROWNUM = 1) LG_STL_GRD,
       A.SPEC_STL_GRD STL_GRD --钢码              
      ,
       A.SLAB_THK --板坯厚度
      ,
       A.SLAB_WTH --板坯宽度
      ,
       t4.avg_width--平均宽度
      ,
       A.SLAB_LEN --板坯长度
      ,
       DECODE(B.MISSNO_CLF, '2', '加回', '3', '轧回', '') PLLX,
       B.CUR_LOAD_LOC SLAB_LOAD_LOC,
       A.SLAB_WGT --板坯重量     
      ,
       B.WEIGHT_WGT,
       H.DCS_THK,
       H.DCS_WTH,
       A.ORD_THK HOT_THK_AIM --热厚度AIM
      ,
       A.ORD_WTH WTH_AIM --宽度AlM
      ,
       CASE
         WHEN (A.STATUS_CD = 'A' AND H.COIL_STAT IS NULL) THEN
          '轧制作业待机'
         WHEN (A.STATUS_CD = 'A' AND H.COIL_STAT = '0') THEN
          '上料进行'
         WHEN (A.STATUS_CD = 'D') THEN
          '轧制吊销'
         WHEN (A.STATUS_CD = 'B') THEN
          '轧制进行'
         WHEN (A.STATUS_CD = 'C' AND H.MISSNO_CLF_CD IS NULL) THEN
          '轧制完成'
         WHEN (A.STATUS_CD = 'C' AND H.MISSNO_CLF_CD = 'C') THEN
          '轧废或中板'
         ELSE
          '轧制完成'
       END STATUS_CD,
       SUBSTR(A.SLAB_NO, 1, 10) CHARGE_NO,
       T2.SM_CFNM BZ,
       T3.SM_CFNM ORDCUST_CD,
       T1.SM_CFNM,
       A.ORD_NO,
       A.ORD_SEQ,
       E.DEL_TO_DATE --交货期
      ,
       u.USER_NM,
       a.MOD_DTIME,
       L.APPLICATION,
       E.PRD_TY --产品类型 
      ,
       H.CUR_LOAD_LOC,
       tH.INFUR_HOUR,
       H.ACT_WGT,
       h.WGT_TIME,
       CASE
         WHEN B.WEIGHT_WGT IS NULL OR B.WEIGHT_WGT = 0 THEN
          0
         ELSE
          ROUND(NVL(H.ACT_WGT / 1000, '0') / B.WEIGHT_WGT, 4) * 100
       END CCL,
       H.MILL_DTIME,
       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 --交付条件特殊要求 
      ,
       
       DECODE(B.SLAB_INGR_DEC_GRD,
              '0',
              '待判定',
              '1',
              '合格',
              '2',
              '不合格',
              '') SLAB_INGR_DEC_GRD,       
       E.PRODNM_CD --交货状态
      ,
       E.C_PRODNM_CD --交货状态       
       
FROM   TBF02_SPEC_MILL A  
      ,TBG02_SLAB_COMM B
      ,TBE02_ORD_PRC   E
      ,TBH02_COIL_COMM H
      ,TBA01_ORD_LINE  L
      ,TBZ01_USER U
      ,TBH02_REHEATFUR th
      ,(SELECT T.CUST_CD SM_CD,T.CUST_NM SM_CFNM FROM TBZ00_CUSTOMER T) T1
      ,(SELECT   SM_CD, SM_CFNM FROM TBZ00_COMMCD  WHERE LG_CD = 'A01012') T2
      ,(SELECT T.CUST_CD SM_CD,T.CUST_NM SM_CFNM FROM TBZ00_CUSTOMER T) T3
      ,(SELECT * FROM STL_CCM_WIDTHGAUGE@XGEJGZXT_WGT
             UNION
 		SELECT * FROM J#STL_CCM_WIDTHGAUGE@XGEJGZXT_WGT) T4
 	  
	  ,(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           = 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.ORD_NO           =L.ORD_NO
        AND A.ORD_SEQ         = L.ORD_SEQ
        AND A.SLAB_NO          =  B.SLAB_NO(+)
        AND B.CUST_CD          =  T1.SM_CD(+)  
        AND E.ORDCUST_CD       =  T3.SM_CD(+)
        AND L.DLIV_TP          =  T2.SM_CD(+)  
        AND A.COIL_NO      =  H.COIL_NO(+)
      --  and A.SLAB_NO= TH.SLAB_NO(+)
        and A.COIL_NO = TH.COIL_NO(+)
        and A.MOD_ID=U.USER_CD(+)
        AND A.SLAB_NO = B.SLAB_NO(+)
        AND A.SLAB_NO = t4.BILLETNO(+)
        AND A.STATUS_CD NOT LIKE 'R'
        AND A.ROLL_MANA_NO LIKE ? ||'%'
        AND SUBSTR(A.MOD_DTIME,1,8)    BETWEEN ? AND ?
		AND NVL(H.MILL_DTIME,'000000')  BETWEEN ?||'000000' AND ?||'999999'
		and a.ord_no like ?||'%'
		and a.ord_seq like ?||'%'
		--and e.PRD_TY  like ?||'%'
		--AND INSTR(NVL( , E.PRD_TY) , E.PRD_TY) > 0 
		AND INSTR(NVL(? , NVL(E.PRD_TY,'NULL')) , NVL(E.PRD_TY,'NULL')) > 0
		AND T2.SM_CFNM LIKE ?||'%'
		AND E.ORD_THK LIKE ?||'%'
		AND rownum<5000
      ORDER   BY  A.ROLL_MANA_NO, 
                  A.ROLL_SLAB_SEQ

			
        ]]>
    </query> 

</queryMap>