XinYu_RZMES/.svn/pristine/51/51372ac821a7c6872f1e10a35a3d7bcd235d42ab.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
        ,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
        ,H.CUR_LOAD_LOC
        ,H.ACT_WGT
        ,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
FROM   TBF02_SPEC_MILL A  
      ,TBG02_SLAB_COMM B
      ,TBE02_ORD_PRC   E
      ,TBH02_COIL_COMM H
      ,TBA01_ORD_LINE  L
      ,(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 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 = B.SLAB_NO(+)
        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>