XinYu_LZMES/.svn/pristine/44/44c71a0180e470b6962f5f0b77c5eabee11c5332.svn-base

<?xml version="1.0" encoding='UTF-8'?>

<queryMap desc="综合判定结果查询">

<query id="UIB030220_HOST_01.SELECT"  desc="查询可进行综合判定的钢卷信息">
	    SELECT     
		CCM.COIL_NO
	   ,CCM.OLD_SAMPL_NO	
	   ,SP.ROLL_MANA_NO	
	   ,CCM.SAMPL_NO
	   ,DECODE(CCM.ORD_NO , NULL , CCM.ORG_ORD_NO , CCM.ORD_NO)      ORD_NO 
	   ,DECODE(CCM.ORD_SEQ , NULL , CCM.ORG_ORD_SEQ , CCM.ORD_SEQ)   ORD_SEQ  
	   ,PKG_QUALITY_COMM.FIB010200(DECODE(CCM.ORD_NO , NULL , CCM.ORG_ORD_NO , CCM.ORD_NO) ,DECODE(CCM.ORD_SEQ , NULL , CCM.ORG_ORD_SEQ , CCM.ORD_SEQ) ) CUST_SPEC_NO	
	   ,CCM.SPEC_ABBSYM
	   ,CCM.SPEC_STL_GRD
	   ,CCM.INGR_DEC_GRD
       ,CCM.MATLQLTY_DEC_GRD
       ,CCM.SIZE_DEC_RST
       ,CCM.WGT_DEC_RST
       ,CCM.PROC_DEC_RST
       ,CCM.EXTSHAPE_DEC_GRD
	   ,CCM.SLAB_NO	
       ,CCM.TOT_DEC_GRD
       ,CCM.TOT_DEC_DTIME
       ,CCM.TOT_DEC_EMP_ID
	   ,CCM.CUR_PROG_CD	
        FROM         TBH02_COIL_COMM  CCM  , TBB02_LOT_COIL LC  ,  TBF02_SPEC_MILL   SP
        WHERE        CCM.TOT_DEC_GRD     IS   NOT  NULL
		AND          CCM.COIL_NO         =    LC.COIL_NO(+)
		AND          CCM.COIL_NO         =    SP.COIL_NO
        AND          CCM.TOT_DEC_DTIME   BETWEEN ? AND ?
        ORDER  BY    CCM.OLD_SAMPL_NO
</query>

<query id="UIB030220_CHEM.SELECT" desc="成分">
		  SELECT         DECODE(SEQ.COMP_YN , 'Y' , SEQ.COMP_CAL , SEQ.CHEM_CD ) CHEM_CD
         			    ,OI.CHEM_MIN
						,OI.CHEM_MAX
						,INGR.CHEM_VAL                             
		 FROM            TBB01_ORD_INGR   OI , TBB02_WORK_INGR INGR , TBB01_SEQ_INGR SEQ
         WHERE           OI.CHEM_CD             =      SEQ.CHEM_CD
         AND             OI.CHEM_CD             =      INGR.CHEM_CD(+)
         AND             OI.DSN_KIND            =      '2'
         AND             INGR.CHARGE_NO(+)      =      SUBSTR( ? , 1 , 10)
         AND             INGR.CHEM_SEQ(+)       =      ZL_HOST_DCS01.FIB020_LST_INGR(SUBSTR(?,1,10))
		 AND             INGR.PROC_CD(+)        =      'JJ'
         AND             OI.ORD_NO              =      ?
         AND             OI.ORD_SEQ             =      ?
         ORDER BY        SEQ.COMP_YN , SEQ.DISPLAY_SEQ
</query>

<query id="UIB030220_QLTY.SELECT" desc="材质">
		SELECT  T1.* , T2.QLTY_SEQ , T2.QLTY_VAL_WK , T2.QLTY_VAL_WKX , T2.QLTY_CD_AFTER
		FROM   (SELECT     OQ.QLTY_MIN 
		                  ,OQ.QLTY_MAX 
		                  ,OQ.QLTY_DEC_CD 
		                  ,SQ.QLTY_CD_CFNM 
		                  ,OQ.QLTY_CD 
		        FROM       TBB01_ORD_QLTY   OQ  , TBB01_SEQ_QLTY  SQ 
		        WHERE      OQ.QLTY_CD   =   SQ.QLTY_CD  
		        AND        OQ.ORD_NO    =   ? 
		        AND        OQ.ORD_SEQ   =   ? 
		        AND        OQ.DSN_KIND  =   PKG_QUALITY_COMM.FIB010202(? , ?)
		) T1 
		,      (SELECT     RQ.QLTY_SEQ 
		                  ,WQ.QLTY_VAL_WK 
		                  ,RQ.QLTY_VAL_WK  QLTY_VAL_WKX 
		                  ,WQ.QLTY_CD 
		                  ,(SELECT X.QLTY_CD_CFNM FROM TBB01_SEQ_QLTY X WHERE X.QLTY_CD = WQ.QLTY_CD_AFTER)   QLTY_CD_AFTER
		        FROM       TBB02_WORK_QLTY   WQ , TBB02_REWORK_QLTY RQ 
				WHERE      WQ.QLTY_CD             =   RQ.QLTY_CD(+)
				AND        WQ.SMP_NO              =   RQ.SMP_NO(+)
				AND        WQ.SMP_CUT_LOC         =   RQ.SMP_CUT_LOC(+)
				AND        WQ.SMP_NO              =   ?
				AND        WQ.SMP_CUT_LOC         =   ?
		) T2
		WHERE   T1.QLTY_CD             =   T2.QLTY_CD(+)
</query>

<query id="UIB030220_OTHERS.SELECT">
<![CDATA[	
	 SELECT    '厚度'                  T_NAME
	           ,CASE WHEN T.DEL_TOL_THK_MIN  < 1  THEN '0'||TO_CHAR(T.DEL_TOL_THK_MIN , 'FM9999.9999') ELSE TO_CHAR(T.DEL_TOL_THK_MIN , 'FM9999.9999') END  T_MIN
	           ,CASE WHEN T.DEL_TOL_THK_MAX  < 1  THEN '0'||TO_CHAR(T.DEL_TOL_THK_MAX , 'FM9999.9999') ELSE TO_CHAR(T.DEL_TOL_THK_MAX , 'FM9999.9999') END  T_MAX
	           ,?                     T_VALUE    --厚度值
	           ,'' RMK
	  FROM      TBB01_ORD_DEL       T 
	  WHERE     T.ORD_NO            =   ? 
	  AND       T.ORD_SEQ           =   ? 
	  AND       T.DSN_KIND          =   PKG_QUALITY_COMM.FIB010203(? , ?)
	  UNION 
	  SELECT   '宽度'                  T_NAME
		        ,TO_CHAR(T.DEL_TOL_WTH_MIN) 
		        ,TO_CHAR(T.DEL_TOL_WTH_MAX) 
		        ,?     COIL_WTH                  --宽度值
		        ,''
	  FROM       TBB01_ORD_DEL       T 
	  WHERE      T.ORD_NO            =    ? 
	  AND        T.ORD_SEQ           =    ? 
	  AND        T.DSN_KIND          =    PKG_QUALITY_COMM.FIB010203(? , ?)
	  UNION 
	  SELECT   '内径'                  T_NAME
		        ,TO_CHAR(T.C_ORD_INDIA)
		        ,'' 
		        ,?                   
		        ,''      
	  FROM       TBA01_ORD_LINE       T 
	  WHERE      T.ORD_NO            =    ?
	  AND        T.ORD_SEQ           =    ?
	  UNION
      SELECT     '重量'               T_NAME
                ,TO_CHAR(OH.PROD_WGT_MIN) 
                ,TO_CHAR(OH.PROD_WGT_MAX)
                ,?       
                ,''             
      FROM       TBB01_ORD_HEAD      OH
      WHERE      OH.ORD_NO           =    ?
      AND        OH.ORD_SEQ          =    ?
      UNION
      SELECT    '外观'                  T_NAME
		        ,T.C_EXTSHAPE_REQ 
		        ,'' 
		        ,?     
		        ,?                     
	  FROM       TBB01_ORD_DEL       T 
	  WHERE      T.ORD_NO            =    ? 
	  AND        T.ORD_SEQ           =    ? 
	  AND        T.DSN_KIND          =    '4'
	  UNION
      SELECT    '包装'                  T_NAME
		        ,PKG_QUALITY_COMM.FZ00_COMM('A01010' ,T.PAKMTH_TP) --订单包装方法 
		        ,'' 
		        ,?    
		        ,''                     
	  FROM       TBA01_ORD_LINE       T 
	  WHERE      T.ORD_NO            =    ?
	  AND        T.ORD_SEQ           =    ?
	  UNION
      SELECT    '切边'                  T_NAME
		        ,nvl(t.EDGE_PROC_YN,'N') --订单切边要求 
		        ,'' 
		        ,?    
		        ,''                    
	  FROM       TBB01_ORD_HEAD       T 
	  WHERE      T.ORD_NO            =    ?
	  AND        T.ORD_SEQ           =    ?
	  ]]>
</query>

<query id="UIB030220_SUAN_01.SELECT" desc="判定结果查询">
      SELECT
      '酸轧'  PROD_LINE      
     ,T.COIL_NO
     ,T.SMP_NO
     ,T.SMP_CUT_LOC
     ,(select A.SM_CFNM
          from tbz00_commcd A
         where A.SM_CD = t.CRK_CD1
           and A.LG_CD = 'B02001') CR_DK1
     ,DECODE(T.ORD_NO , NULL , T.ORG_ORD_NO , T.ORD_NO)      ORD_NO 
     ,DECODE(T.ORD_SEQ , NULL , T.ORG_ORD_SEQ , T.ORD_SEQ)   ORD_SEQ  
     ,T.PRODNM_CD
     ,T.SPEC_ABBSYM
     ,T.SPEC_STL_GRD
     ,T.INGR_DEC_GRD
     ,T.MATLQLTY_DEC_GRD
     ,T.SIZE_DEC_RST
     ,T.WGT_DEC_RST
     ,T.PROC_DEC_RST
     ,T.EXTSHAPE_DEC_GRD
     ,T.SLAB_NO  
     ,T.TOT_DEC_GRD
     ,T.TOT_DEC_DTIME
     ,T.TOT_DEC_EMP_ID
     ,PKG_QUALITY_COMM.FZ00_COMM('A01010' ,T.PACKAGE_LEVEL) PACKAGE_LEVEL  --实绩包装方式
     ,T.COIL_INDIA  --实绩内径
     ,T.COIL_THK
     ,T.COIL_WTH
     ,T.ACT_WGT
     ,T.TOT_DEC_TYPE
     ,EE.SM_CFNM          CUR_PROG_CD  
     ,T.EXTSHAPE_QUALITY
     ,DECODE(T.ORD_FL , 1, '订单材' , '2' , '余材' , T.ORD_FL)   ORD_FL
     ,PKG_QUALITY_COMM.FZ00_ORDCUST_NM(NVL(T.ORD_NO , T.ORG_ORD_NO))               ORDCUST_CD
      FROM         C_TBL02_COIL_COMM  T  , ( SELECT   T.SM_CFNM  ,  T.SM_CD 
                                              FROM     TBZ00_COMMCD           T  
                                              WHERE    T.LG_CD = 'E01002')    EE
      WHERE        T.TOT_DEC_GRD     IS   NOT  NULL
      AND          T.CUR_PROG_CD     =    EE.SM_CD(+)
      AND          T.TOT_DEC_DTIME   BETWEEN ? AND ?
      AND          SUBSTR(T.COIL_NO , 1,9) BETWEEN NVL(? , SUBSTR(T.COIL_NO , 1,9)) AND  NVL(? , SUBSTR(T.COIL_NO , 1,9))
      AND          DECODE(? , '1' , '1' , NVL(T.ORD_NO , T.ORG_ORD_NO)) = ?
      AND          NVL(T.SPEC_STL_GRD , ' ')	LIKE  ?||'%'
      ORDER  BY    T.COIL_NO
</query>

<query id="UIB030220_SUAN_02.SELECT" desc="待判钢卷">
	  SELECT
      '酸轧'  PROD_LINE      
     ,T.COIL_NO
     ,T.ORD_FL
     ,T.SMP_NO
     ,T.SMP_CUT_LOC
     ,T.ORD_NO
     ,T.ORD_SEQ
     ,T.PRODNM_CD
     ,T.SPEC_ABBSYM
     ,T.SPEC_STL_GRD
     ,T.INGR_DEC_GRD
     ,T.MATLQLTY_DEC_GRD
     ,T.SIZE_DEC_RST
     ,T.WGT_DEC_RST
     ,T.PROC_DEC_RST
     ,T.SLAB_NO  
     ,PKG_QUALITY_COMM.FZ00_COMM('A01010' ,T.PACKAGE_LEVEL) PACKAGE_LEVEL  --实绩包装方式
     ,T.COIL_INDIA  --实绩内径
     ,T.COIL_THK
     ,T.COIL_WTH
     ,T.ACT_WGT
     ,EE.SM_CFNM          CUR_PROG_CD  
     ,T.EXTSHAPE_QUALITY
     ,DECODE(T.ORD_FL , 1, '订单材' , '2' , '余材' , T.ORD_FL)   ORD_FL
     ,T.MILL_DTIME
      FROM         C_TBL02_COIL_COMM  T  , ( SELECT   T.SM_CFNM  ,  T.SM_CD 
                                              FROM     TBZ00_COMMCD           T  
                                              WHERE    T.LG_CD = 'E01002')    EE
      WHERE        T.TOT_DEC_GRD     IS     NULL
      AND          T.COIL_STAT       =    '2'
      AND          T.CUR_PROG_CD     =    EE.SM_CD(+)
      AND          T.ORD_FL          =    '1'
      ORDER  BY    T.COIL_NO
</query>

<query id="UIB030220_SUAN_HISDCS.SELECT" desc="综合判定历史记录">
	  SELECT       T.MATERIAL_NO
                  ,DECODE(T.DCS_FLAG , 'D' , '综合判定' , 'C' , '判定取消')         DCS_FLAG
                  ,T.PRODNM_CD
                  ,T.SPEC_ABBSYM
                  ,T.SPEC_STL_GRD
                  ,PKG_QUALITY_COMM.FZ00_COMM('A01007' , T.ORD_USE_CD)   ORD_USE_CD
                  ,T.ORD_NO
                  ,T.ORD_SEQ
                  ,T.COIL_THK
                  ,T.COIL_WTH
                  ,T.ACT_WGT
                  ,T.ORG_ORD_NO
                  ,T.ORG_ORD_SEQ
                  ,T.SLAB_NO
                  ,T.MATLQLTY_DEC_GRD
                  ,T.SIZE_DEC_RST
                  ,T.WGT_DEC_RST
                  ,T.PROC_DEC_RST
                  ,T.EXTSHAPE_DEC_GRD
                  ,T.INGR_DEC_GRD
                  ,T.TOT_DEC_GRD
                  ,T.REG_ID
                  ,T.REG_DTIME
                  ,T.EXTSHAPE_QUALITY
      FROM         TBB02_HIS_DCS          T
      WHERE        T.DCS_COUNT            =     '2'
      AND          T.REG_DTIME       BETWEEN  ?  AND  ?
      AND          SUBSTR(T.MATERIAL_NO , 1,9) BETWEEN NVL(? , SUBSTR(T.MATERIAL_NO , 1,9)) AND  NVL(? , SUBSTR(T.MATERIAL_NO , 1,9))
      AND          DECODE(? , '1' , '1' , NVL(T.ORD_NO , T.ORG_ORD_NO)) = ?
      AND          NVL(T.SPEC_STL_GRD , ' ')  LIKE  ?||'%'
      ORDER  BY    T.MATERIAL_NO
</query>

<query id="UIB030220_SUAN_01.CALL">
	{call ZL_SUAN_DCS01.DCS_COIL_CANCEL(?,?,?)}
</query>

<query id="UIB030220_LIAN_01.SELECT" desc="连退综合判定结果查询">
	SELECT
      '连退'  PROD_LINE      
     ,T.OLD_SAMPL_NO   COIL_NO
     ,T.SMP_NO
     ,T.SMP_CUT_LOC
     ,(select A.SM_CFNM
          from tbz00_commcd A
         where A.SM_CD = t.CRK_CD1
           and A.LG_CD = 'B02001') CR_DK1
     ,DECODE(T.ORD_NO , NULL , T.ORG_ORD_NO , T.ORD_NO)      ORD_NO 
     ,DECODE(T.ORD_SEQ , NULL , T.ORG_ORD_SEQ , T.ORD_SEQ)   ORD_SEQ  
     ,T.PRODNM_CD
     ,T.SPEC_ABBSYM
     ,T.SPEC_STL_GRD
     ,T.INGR_DEC_GRD
     ,T.MATLQLTY_DEC_GRD
     ,T.SIZE_DEC_RST
     ,T.WGT_DEC_RST
     ,T.PROC_DEC_RST
     ,T.EXTSHAPE_DEC_GRD
     ,T.SLAB_NO  
     ,PKG_QUALITY_COMM.FZ00_COMM('A01010' ,T.PACKAGE_LEVEL) PACKAGE_LEVEL  --实绩包装方式
     ,T.COIL_INDIA  --实绩内径
     ,T.TOT_DEC_GRD
     ,T.TOT_DEC_DTIME
     ,T.TOT_DEC_EMP_ID
     ,T.COIL_THK
     ,T.COIL_WTH
     ,T.ACT_WGT
     ,EE.SM_CFNM          CUR_PROG_CD  
     ,T.EXTSHAPE_QUALITY
     ,T.TOT_DEC_TYPE
     ,DECODE(T.ORD_FL , 1, '订单材' , '2' , '余材' , T.ORD_FL)   ORD_FL
     ,PKG_QUALITY_COMM.FZ00_ORDCUST_NM(NVL(T.ORD_NO , T.ORG_ORD_NO))               ORDCUST_CD
      FROM         C_TBC02_COIL_COMM  T  , ( SELECT   T.SM_CFNM  ,  T.SM_CD 
                                              FROM     TBZ00_COMMCD           T  
                                              WHERE    T.LG_CD = 'E01002')    EE
      WHERE        T.TOT_DEC_GRD     IS   NOT  NULL
      AND          T.CUR_PROG_CD     =    EE.SM_CD(+)
      AND          T.FAN_XIU         IS     NULL
      AND          NVL(T.TOT_DEC_DTIME,T.REG_DTIME )   BETWEEN ? AND ?
      AND          SUBSTR(T.OLD_SAMPL_NO , 1,9) BETWEEN NVL(? , SUBSTR(T.OLD_SAMPL_NO , 1,9)) AND  NVL(? , SUBSTR(T.OLD_SAMPL_NO , 1,9))
      AND          DECODE(? , '1' , '1' , NVL(T.ORD_NO , T.ORG_ORD_NO)) = ?
      AND          NVL(T.SPEC_STL_GRD , ' ')  LIKE  ?||'%'
      ORDER  BY    T.COIL_NO
</query>


<query id="UIB030220_LIAN_HISDCS.SELECT" desc="综合判定历史记录">
	  SELECT       T.MATERIAL_NO
                  ,DECODE(T.DCS_FLAG , 'D' , '综合判定' , 'C' , '判定取消')         DCS_FLAG
                  ,T.PRODNM_CD
                  ,T.SPEC_ABBSYM
                  ,T.SPEC_STL_GRD
                  ,PKG_QUALITY_COMM.FZ00_COMM('A01007' , T.ORD_USE_CD)   ORD_USE_CD
                  ,T.ORD_NO
                  ,T.ORD_SEQ
                  ,T.COIL_THK
                  ,T.COIL_WTH
                  ,T.ACT_WGT
                  ,T.ORG_ORD_NO
                  ,T.ORG_ORD_SEQ
                  ,T.SLAB_NO
                  ,T.MATLQLTY_DEC_GRD
                  ,T.SIZE_DEC_RST
                  ,T.WGT_DEC_RST
                  ,T.PROC_DEC_RST
                  ,T.EXTSHAPE_DEC_GRD
                  ,T.INGR_DEC_GRD
                  ,T.TOT_DEC_GRD
                  ,T.REG_ID
                  ,T.REG_DTIME
                  ,T.EXTSHAPE_QUALITY
      FROM         TBB02_HIS_DCS          T
      WHERE        T.DCS_COUNT            =     '3'
      AND          T.REG_DTIME       BETWEEN  ?  AND  ?
      AND          SUBSTR(T.MATERIAL_NO , 1,9) BETWEEN NVL(? , SUBSTR(T.MATERIAL_NO , 1,9)) AND  NVL(? , SUBSTR(T.MATERIAL_NO , 1,9))
      AND          DECODE(? , '1' , '1' , NVL(T.ORD_NO , T.ORG_ORD_NO)) = ?
      AND          NVL(T.SPEC_STL_GRD , ' ')  LIKE  ?||'%'
      ORDER  BY    T.MATERIAL_NO
</query>

<query id="UIB030220_LIAN_01.CALL">
	{call ZL_LIANT_DCS01.DCS_COIL_CANCEL(?,?,?)}
</query>


<query id="UIB030220_LIAN_02.SELECT" desc="待判钢卷">
	  SELECT
      '连退'  PROD_LINE      
     ,T.OLD_SAMPL_NO   COIL_NO
     ,T.ORD_FL
     ,T.SMP_NO
     ,T.SMP_CUT_LOC
     ,T.ORD_NO
     ,T.ORD_SEQ
     ,T.PRODNM_CD
     ,T.SPEC_ABBSYM
     ,T.SPEC_STL_GRD
     ,T.INGR_DEC_GRD
     ,T.MATLQLTY_DEC_GRD
     ,T.SIZE_DEC_RST
     ,T.WGT_DEC_RST
     ,T.PROC_DEC_RST
     ,T.SLAB_NO  
     ,PKG_QUALITY_COMM.FZ00_COMM('A01010' ,T.PACKAGE_LEVEL) PACKAGE_LEVEL  --实绩包装方式
     ,T.COIL_INDIA  --实绩内径
     ,T.COIL_THK
     ,T.COIL_WTH
     ,T.ACT_WGT
     ,EE.SM_CFNM          CUR_PROG_CD  
     ,T.EXTSHAPE_QUALITY
     ,DECODE(T.ORD_FL , 1, '订单材' , '2' , '余材' , T.ORD_FL)   ORD_FL
     ,T.MILL_DTIME
      FROM         C_TBC02_COIL_COMM  T  , ( SELECT   T.SM_CFNM  ,  T.SM_CD 
                                              FROM     TBZ00_COMMCD           T  
                                              WHERE    T.LG_CD = 'E01002')    EE
      WHERE        T.TOT_DEC_GRD     IS     NULL
      AND          T.FAN_XIU         IS     NULL
      AND          T.COIL_STAT       =    '2'
      AND          T.CUR_PROG_CD     =    EE.SM_CD(+)
      AND          T.ORD_FL          =    '1'
      AND          T.CUR_PROG_CD     =    'DED'
      ORDER  BY    T.COIL_NO
</query>
</queryMap>