XinYu_RZMES/.svn/pristine/1a/1aa3a1c2d065ccd6297b1557c6faa0bdc1ec0552.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="材质判定前试样现况">
	
	<query  id="UIB020280_01.SELECT" desc="试样现况">
		<![CDATA[
WITH RESULT AS
  (SELECT 
      (SELECT COUNT(*) FROM TBH02_COIL_COMM
      WHERE  COIL_STAT = '2'
      AND    SMP_DIR_PIC_YN = 'Y'
      AND    MATLQLTY_DEC_GRD IS NULL
      ) CNT1                                          --代表试样数
      ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
      WHERE  COIL_STAT = '2'
      AND    SAMPL_NO  IS NOT NULL
      AND    SMP_DIR_PIC_YN IS NULL
      AND    MATLQLTY_DEC_GRD IS NULL) CNT2           --从属试样数
      ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
      WHERE  COIL_STAT = '2'
      AND    MATLQLTY_DEC_GRD = '0'
      AND    TOT_DEC_GRD      IS NULL) CNT3           --无试样数
  FROM DUAL
  )
SELECT T.*,(CNT1+CNT2+CNT3) CNT4 FROM RESULT T  
		]]>	
	</query>
	<query  id="UIB020280_02.SELECT" desc="试样进程现况">
		<![CDATA[
WITH RESULT AS
  (SELECT 
        (SELECT COUNT(*)
        FROM   TBB02_LOT_COM LM,TBB02_LOT_COIL LL
        WHERE  LM.SMP_PROG_CD    =  'A'
        AND    LM.SMP_NO         =  LL.SMP_NO
        AND    LL.SMP_TIT_YN     =  'Y') CNT1_1                     --代表试样数(试样采集等待)
        ,(SELECT COUNT(*)
        FROM   TBB02_LOT_COM LM,TBB02_LOT_COIL LL
        WHERE  LM.SMP_PROG_CD    =  'B'
        AND    LM.SMP_NO         =  LL.SMP_NO
        AND    LL.SMP_TIT_YN     =  'Y') CNT1_2                     --代表试样数(试样入库等待)
        ,(SELECT COUNT(*)
        FROM   TBB02_LOT_COM LM,TBB02_LOT_COIL LL
        WHERE  LM.SMP_PROG_CD    =  'C'
        AND    LM.SMP_NO         =  LL.SMP_NO
        AND    LL.SMP_TIT_YN     =  'Y') CNT1_3                     --代表试样数(试样检验等待)
        ,(SELECT COUNT(*)
        FROM   TBB02_LOT_COM LM,TBB02_LOT_COIL LL
        WHERE  LM.SMP_PROG_CD    =  'D'
        AND    LM.SMP_NO         =  LL.SMP_NO
        AND    LL.SMP_TIT_YN     =  'Y') CNT1_4                     --代表试样数(材质判定等待) 
  FROM DUAL
  UNION ALL
  SELECT 
        (SELECT COUNT(*)
        FROM   TBB02_LOT_COM LM, TBB02_LOT_COIL LL
        WHERE   LM.SMP_PROG_CD   =  'A'
        AND    LM.SMP_NO         =  LL.SMP_NO
        AND    LL.SMP_TIT_YN     IS NULL
        ) CNT1_1                     --从属试样数(试样采集等待) 
        ,(SELECT COUNT(*)
        FROM   TBB02_LOT_COM LM, TBB02_LOT_COIL LL
        WHERE   LM.SMP_PROG_CD   =  'B'
        AND    LM.SMP_NO         =  LL.SMP_NO
        AND    LL.SMP_TIT_YN     IS NULL
        ) CNT1_2                     --从属试样数(试样入库等待) 
        ,(SELECT COUNT(*)
        FROM   TBB02_LOT_COM LM, TBB02_LOT_COIL LL
        WHERE   LM.SMP_PROG_CD   =  'C'
        AND    LM.SMP_NO         =  LL.SMP_NO
        AND    LL.SMP_TIT_YN     IS NULL
        ) CNT1_3                     --从属试样数(试样检验等待) 
        ,(SELECT COUNT(*)
        FROM   TBB02_LOT_COM LM, TBB02_LOT_COIL LL
        WHERE   LM.SMP_PROG_CD   =  'D'
        AND    LM.SMP_NO         =  LL.SMP_NO
        AND    LL.SMP_TIT_YN     IS NULL
        ) CNT1_4                     --从属试样数(材质判定等待)                 
  FROM DUAL
  )
SELECT T.*,(CNT1_1+CNT1_2+CNT1_3+CNT1_4) CNT1_5
FROM RESULT T       
		]]>	
	</query>
	<query  id="UIB020280_03.SELECT" desc="试样分类">
		<![CDATA[
WITH RESULT AS(
  SELECT 
      (SELECT COUNT(DISTINCT WQ.SMP_NO)
      FROM   TBB02_LOT_COM LM,TBB02_WORK_QLTY WQ
      WHERE  LM.SMP_PROG_CD       =  'A'
      AND    LM.SMP_NO            =  WQ.SMP_NO
      AND   SUBSTR(WQ.SMP_NO,13,1)=  '0') CNT1_1        --原试样数(试样采集等待)
      ,(SELECT COUNT(DISTINCT WQ.SMP_NO)
      FROM   TBB02_LOT_COM LM,TBB02_WORK_QLTY WQ
      WHERE  LM.SMP_PROG_CD       =  'B'
      AND    LM.SMP_NO            =  WQ.SMP_NO
      AND   SUBSTR(WQ.SMP_NO,13,1)=  '0'
      ) CNT1_2                                          --原试样数(试样入库等待)  
      ,(SELECT COUNT(DISTINCT WQ.SMP_NO)
      FROM   TBB02_LOT_COM LM,TBB02_WORK_QLTY WQ
      WHERE  LM.SMP_PROG_CD       =  'C'
      AND    LM.SMP_NO            =  WQ.SMP_NO
      AND   SUBSTR(WQ.SMP_NO,13,1)=  '0'
      ) CNT1_3                                          --原试样数(试样检验等待) 
      ,(SELECT COUNT(DISTINCT WQ.SMP_NO)
      FROM   TBB02_LOT_COM LM,TBB02_WORK_QLTY WQ
      WHERE  LM.SMP_PROG_CD       =  'D'
      AND    LM.SMP_NO            =  WQ.SMP_NO
      AND   SUBSTR(WQ.SMP_NO,13,1)=  '0') CNT1_4        --原试样数(材质判定等待) 
  FROM DUAL
      UNION ALL
  SELECT
      (SELECT COUNT(DISTINCT WQ.SMP_NO)
      FROM   TBB02_LOT_COM LM,TBB02_WORK_QLTY WQ
      WHERE  LM.SMP_PROG_CD       =  'A'
      AND    LM.SMP_NO            =  WQ.SMP_NO
      AND   SUBSTR(WQ.SMP_NO,13,1)>= '1'
      ) CNT1_1                                           --复试样数(试样采集等待)  
      ,(SELECT COUNT(DISTINCT WQ.SMP_NO)
      FROM   TBB02_LOT_COM LM,TBB02_WORK_QLTY WQ
      WHERE  LM.SMP_PROG_CD       =  'B'
      AND    LM.SMP_NO            =  WQ.SMP_NO
      AND   SUBSTR(WQ.SMP_NO,13,1)>= '1'
      ) CNT1_2                                           --复试样数(试样入库等待) 
      ,(SELECT COUNT(DISTINCT WQ.SMP_NO)
      FROM   TBB02_LOT_COM LM,TBB02_WORK_QLTY WQ
      WHERE  LM.SMP_PROG_CD       =  'B'
      AND    LM.SMP_NO            =  WQ.SMP_NO
      AND   SUBSTR(WQ.SMP_NO,13,1)>= '1'
      ) CNT1_3                                           --复试样数(试样检验等待) 
      ,(SELECT COUNT(DISTINCT WQ.SMP_NO)
      FROM   TBB02_LOT_COM LM,TBB02_WORK_QLTY WQ
      WHERE  LM.SMP_PROG_CD       =  'D'
      AND    LM.SMP_NO            =  WQ.SMP_NO
      AND   SUBSTR(WQ.SMP_NO,13,1)>= '1'
      ) CNT1_4  
      FROM DUAL
      UNION ALL
  SELECT                                             --复试样数(材质判定等待)
      NULL CNT1_1,NULL CNT1_2 
      ,(SELECT COUNT(*) FROM 
      TBB02_LOT_COM LM
      WHERE LM.SMP_PROG_CD = 'C'
      AND   LM.QLTY_MAN_INS_TP = 'Y') CNT1_3             --手工判定试样数(试样检验等待)  
      ,(SELECT COUNT(*) FROM 
      TBB02_LOT_COM LM
      WHERE LM.SMP_PROG_CD         = 'D'
      AND   LM.QLTY_MAN_INS_TP     = 'Y'
      ) CNT1_4         --手工判定试样数(试样检验等待)        
  FROM DUAL
)
SELECT T.*,
(NVL(CNT1_1,0)+NVL(CNT1_2,0)+CNT1_3+CNT1_4)CNT1_5
FROM RESULT T			
		]]>	
	</query>
</queryMap>