XinYu_LZMES/.svn/pristine/b9/b9286c6fe6639326f94d4a0786b1dd9fa16ec61e.svn-base

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

<queryMap desc="连退产品质量判定单（快报）">

<query id="UIB030180_01.SELECT"  desc="连退查询">
        <![CDATA[
SELECT             DISTINCT
                   T1.COIL_NO
                  ,T1.OLD_SAMPL_NO   
                  ,T1.SPEC_STL_GRD
                  ,TO_CHAR(T2.ORD_THK ,'0.000')||'*'||T2.ORD_WTH    TWL
                  --,DECODE(T1.SIZE_DEC_RST , '1' , '合格' , '2' , '不合格' , NULL , '待判')    SIZE_DCS_RESULT
                  ,CASE
         				WHEN  
              			(T1.COIL_THK IS NOT NULL AND T4.DEL_TOL_THK_MIN IS NOT NULL AND
              			(T1.COIL_THK > T4.DEL_TOL_THK_MAX OR 
               			T1.COIL_THK < T4.DEL_TOL_THK_MIN ))  
               		OR
              			(T1.COIL_WTH IS NOT NULL AND T4.DEL_TOL_WTH_MIN IS NOT NULL AND
              			(T1.COIL_WTH > T4.DEL_TOL_WTH_MAX OR 
               			T1.COIL_WTH <  T4.DEL_TOL_WTH_MIN )) THEN
          				'不合格'
         				ELSE
          				'合格'
       					END   SIZE_DCS_RESULT
                  --,(CASE WHEN T4.DSN_KIND = '4' THEN T4.DEL_TOL_THK_MIN  END)
                  ,T1.COIL_THK          DCS_THK
                  --,(CASE WHEN T4.DSN_KIND = '4' THEN T4.DEL_TOL_THK_MAX  END)
                  --,(CASE WHEN T4.DSN_KIND = '4' THEN T4.DEL_TOL_WTH_MIN  END)
                  ,T1.COIL_WTH          DCS_WTH
                  --,(CASE WHEN T4.DSN_KIND = '4' THEN T4.DEL_TOL_WTH_MAX  END)
                  ,T1.EXTSHAPE_QUALITY
                  ,T1.MATLQLTY_DEC_GRD
                  ,T1.INGR_DEC_GRD
                  ,T1.WGT_DEC_RST
                  ,T1.TOT_DEC_GRD
                  ,T1.ACT_WGT
                  ,DECODE(T1.CAL_TYPE , '2' , 'Y' , '')    FAN_XIU
                  ,CASE WHEN T1.RCAL_CNT >0 THEN RCAL_CNT||'次过渡'  ELSE NULL END    RCAL_CNT
                  ,ZL_NORM_COMM.FZ00_COMM('B02001' , T1.CRK_CD1)||';'||ZL_NORM_COMM.FZ00_COMM('B02001' , T1.CRK_CD2)||';'||ZL_NORM_COMM.FZ00_COMM('B02001' , T1.CRK_CD3)  CRK_DETAIL
                  ,T1.MILL_DTIME  REG_DTIME
                  ,(SELECT SM_CFNM FROM TBZ00_COMMCD WHERE SM_CD =  T1.CUR_PROG_CD)  CUR_PROG_CD
                                    ,T1.ORD_NO||'~'||T1.ORD_SEQ      ORDNOSEQ   
                                  ,CASE WHEN T1.COIL_ID = T2.L_COIL_ID   THEN T2.COIL_WGT ELSE NULL END    COIL_WGT                     ,C_PKG_UIM.GET_ORIGINAL_COIL_WGT(t1.ori_OLD_SAMPL_NO) BEF_WGT
                  ,T3.STL_GRD
       FROM       (SELECT       C1.COIL_NO         COIL_ID                                   ,C2.*                                             FROM         C_TBC02_COIL_COMM                  C1
                               ,C_TBC02_COIL_COMM                  C2
                   WHERE        C1.SLAB_NO                         =      C2.SLAB_NO         
                   AND          C1.LINE_TP                         =      'L'
                   AND          C2.LINE_TP                         =      'L'
                   AND          C2.DIVIDE_YN                       =      'N'
                   AND          C2.MILL_DTIME     BETWEEN     NVL(? , C2.MILL_DTIME)   AND  NVL(? , C2.MILL_DTIME)
                   )  T1
                  ,L_TBF03_SPEC_MILL  T2  
                  ,C_TBK02_COIL_COMM  T3
                  ,TBB01_ORD_DEL      T4
       WHERE           T1.COIL_ID                  =     T2.L_COIL_ID
       AND             T1.LINE_TP                  =     'L'
       AND             T1.DIVIDE_YN                =     'N'  
       AND             T2.COIL_NO                  =     T3.OLD_SAMPL_NO(+)
       AND             T1.ORD_NO                   =     T4.ORD_NO(+)
       AND             T1.ORD_SEQ                  =     T4.ORD_SEQ(+)
       AND             (T4.DSN_KIND = '4' OR T4.DSN_KIND IS NULL)
       ORDER BY        T1.MILL_DTIME,T1.OLD_SAMPL_NO
      ]]>  
</query>


<query id="UIB030180_02.SELECT"  desc="重卷查询">
        <![CDATA[
       SELECT
                   T1.COIL_NO
                  ,T1.OLD_SAMPL_NO   
                  ,T1.SPEC_STL_GRD
                  ,TO_CHAR(T2.ORD_THK ,'0.000')||'*'||T2.ORD_WTH    TWL
                  --,DECODE(T1.SIZE_DEC_RST , '1' , '合格' , '2' , '不合格' , NULL , '待判')    SIZE_DCS_RESULT
                  ,CASE
                 WHEN  
                    (T1.COIL_THK IS NOT NULL AND T3.DEL_TOL_THK_MIN IS NOT NULL AND
                    (T1.COIL_THK > T3.DEL_TOL_THK_MAX  OR 
                     T1.COIL_THK < T3.DEL_TOL_THK_MIN )) 
                 OR 
              		(T1.COIL_WTH IS NOT NULL AND T3.DEL_TOL_WTH_MIN IS NOT NULL AND
              		(T1.COIL_WTH > T3.DEL_TOL_WTH_MAX OR 
               		 T1.COIL_WTH < T3.DEL_TOL_WTH_MIN )) 
               	THEN '不合格'
         		ELSE '合格'
       			END   SIZE_DCS_RESULT
                  ,T1.COIL_THK          DCS_THK
                  ,T1.COIL_WTH          DCS_WTH
                  ,T1.EXTSHAPE_QUALITY
                  ,T1.MATLQLTY_DEC_GRD
                  ,T1.TOT_DEC_GRD
                  ,T1.ACT_WGT
                  ,DECODE(T1.CAL_TYPE , '2' , 'Y' , '')    FAN_XIU
                  ,CASE WHEN T1.RCAL_CNT >0 THEN RCAL_CNT||'次过渡'  ELSE NULL END    RCAL_CNT
                  ,ZL_NORM_COMM.FZ00_COMM('B02001' , T1.CRK_CD1)||';'||ZL_NORM_COMM.FZ00_COMM('B02001' , T1.CRK_CD2)||';'||ZL_NORM_COMM.FZ00_COMM('B02001' , T1.CRK_CD3)  CRK_DETAIL
                  ,T1.MILL_DTIME  REG_DTIME
                  ,(SELECT SM_CFNM FROM TBZ00_COMMCD WHERE SM_CD =  T1.CUR_PROG_CD)  CUR_PROG_CD
                  --,T1.COIL_RT                 
                  ,T1.ORD_NO||'~'||T1.ORD_SEQ      ORDNOSEQ   
                  ,CASE WHEN SUBSTR(T1.OLD_SAMPL_NO , 17) IN ('0' , '1')  THEN  T2.COIL_WGT  ELSE NULL END   COIL_WGT   --原料重量
                  
       FROM       (SELECT       C1.COIL_NO         COIL_ID        --母卷ID
                               ,C2.*                              --子卷信息
                   FROM         C_TBC02_COIL_COMM_BAK              C1
                               ,C_TBC02_COIL_COMM_BAK              C2
                   WHERE        C1.SLAB_NO                         =      C2.SLAB_NO       
                   AND          C1.LINE_TP                         =      'L'
                   AND          C2.LINE_TP                         =      'C'
                   AND          C2.DIVIDE_YN                       =      'N'
                   AND          C2.MILL_DTIME     BETWEEN     NVL(? , C2.MILL_DTIME)   AND  NVL(? , C2.MILL_DTIME)
                   )  T1
                  ,L_TBF03_SPEC_MILL  T2
                  ,TBB01_ORD_DEL 	  T3  
       WHERE           T1.COIL_ID                  =     T2.L_COIL_ID
       AND             T1.LINE_TP                  =     'C'
       AND             T1.DIVIDE_YN                =     'N'
       AND             T1.ORD_NO                   =     T3.ORD_NO(+)
       AND             T1.ORD_SEQ                  =     T3.ORD_SEQ(+)
       AND             (T3.DSN_KIND = '4' OR T3.DSN_KIND IS NULL)
   --    AND             T1.MILL_DTIME     BETWEEN     NVL(? , T1.MILL_DTIME)   AND  NVL(? , T1.MILL_DTIME)
       ORDER BY        T1.MILL_DTIME,T1.OLD_SAMPL_NO
      ]]>  
</query>



<query id="UIB030180_03.SELECT"  desc="酸轧查询">
        <![CDATA[
       SELECT T1.COIL_NO,
       T1.OLD_SAMPL_NO,
       T1.SPEC_STL_GRD,
       TO_CHAR(T2.ORD_THK, '0.000') || '*' || T2.ORD_WTH TWL,      
       --DECODE(T1.SIZE_DEC_RST, '1', '合格', '2', '不合格', NULL, '待判') SIZE_DCS_RESULT,
       CASE
                 WHEN  
                    (T1.COIL_THK IS NOT NULL AND
                    (T1.COIL_THK > T4.DEL_TOL_THK_MAX OR 
                     T1.COIL_THK < T4.DEL_TOL_THK_MIN ))  
               OR
                    (T1.COIL_WTH IS NOT NULL  AND
                    (T1.COIL_WTH >  T4.DEL_TOL_WTH_MAX  OR 
               			 T1.COIL_WTH <  T4.DEL_TOL_WTH_MIN )) 
                THEN
          				'不合格'
         				ELSE
          				'合格'
       					END   SIZE_DCS_RESULT,
       
       
       T1.COIL_THK DCS_THK,
       T1.COIL_WTH DCS_WTH,
       T1.EXTSHAPE_QUALITY,
       T1.MATLQLTY_DEC_GRD,
       T1.INGR_DEC_GRD,
       T1.WGT_DEC_RST,
       T1.TOT_DEC_GRD,
       T1.ACT_WGT,
       DECODE(T1.CAL_TYPE, '2', 'Y', '') FAN_XIU,
       CASE
         WHEN T1.RCAL_CNT > 0 THEN
          RCAL_CNT || '次过渡'
         ELSE
          NULL
       END RCAL_CNT,
       ZL_NORM_COMM.FZ00_COMM('B02001', T1.CRK_CD1) || ';' ||
       ZL_NORM_COMM.FZ00_COMM('B02001', T1.CRK_CD2) || ';' ||
       ZL_NORM_COMM.FZ00_COMM('B02001', T1.CRK_CD3) CRK_DETAIL,
       T1.MILL_DTIME REG_DTIME,
       (SELECT SM_CFNM FROM TBZ00_COMMCD WHERE SM_CD =  T1.CUR_PROG_CD)  CUR_PROG_CD,
       --T1.ROUTE   COIL_RT, ---去向
       T1.ORD_NO || '~' || T1.ORD_SEQ ORDNOSEQ,
       CASE
         WHEN T1.COIL_NO = T2.C_COIL_NO THEN
          T2.COIL_WGT
         ELSE
          NULL
       END COIL_WGT --原料重量
      ,T3.ACT_WGT BEF_WGT,
       T3.STL_GRD
  FROM C_TBL02_COIL_COMM T1, C_TBF03_SPEC_MILL T2, C_TBK02_COIL_COMM T3, TBB01_ORD_DEL T4
 WHERE SUBSTR(T1.COIL_NO, 1, 9) = SUBSTR(T2.C_COIL_NO, 1, 9)
   AND T1.MILL_DTIME BETWEEN NVL(?, T1.MILL_DTIME) AND NVL(?, T1.MILL_DTIME)
   AND T2.COIL_NO = T3.COIL_NO(+)
   AND             T1.ORD_NO                   =     T4.ORD_NO(+)
   AND             T1.ORD_SEQ                  =     T4.ORD_SEQ(+)
   AND             (T4.DSN_KIND = '4' OR T4.DSN_KIND IS NULL)
 ORDER BY T1.MILL_DTIME,T1.OLD_SAMPL_NO
      ]]>  
</query>
</queryMap>