XinYu_RZMES/.svn/pristine/7e/7e6117589bd72df6c974e862debec2e99f405d0c.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="钢卷号材质判定查询">
	
	<query id="UIB020140_01.INITSELECT" desc="检验项目，页面初始化">
		  SELECT 
		  QLTY_CD_CFNM    LABEL 
        , QLTY_CD         VALUE         
          FROM            TBB01_SEQ_QLTY
	</query>
	
	<query id="UIB020140_02.INITSELECT" desc="判定完成，页面初始化  ChargeNo">
		/*SELECT      DISTINCT  
        SUBSTR(SMP_NO , 1 , 10)   LABEL
      , SUBSTR(SMP_NO , 1 , 10)   VALUE  
        FROM        TBB02_LOT_COM
        WHERE       SMP_PROG_CD = 'E'  
		ORDER BY    LABEL
		*/
		SELECT  'X'  LABEL , 'X' VALUE FROM DUAL
	</query>
	
	<query id="UIB020140_011.SELECT" desc="获取序号">
		 SELECT     DISTINCT
         SUBSTR(a.SMP_NO ,11)      LABEL
       , SUBSTR(a.SMP_NO ,11)      VALUE
         FROM       TBB02_LOT_COM a
		 WHERE      SUBSTR(a.SMP_NO ,1 , 10)  = ?
		 AND        A.SMP_PROG_CD = 'E'  
	</query>
	
	<query  id="UIB020140_01.SELECT" desc= "钢卷及样片信息查询，试样编号作为查询条件">
      	SELECT 
         CCM.COIL_NO 
        ,CCM.OLD_SAMPL_NO
        ,LCM.SMP_NO               
        ,LCM.SMP_CUT_LOC           
        ,LCL.SMP_TIT_YN          
        ,LCL.ORD_NO||LCL.ORD_SEQ   ORD_NO
        ,LCM.SPEC_ABBSYM
        ,LCM.SPEC_STL_GRD
        ,LCL.COIL_THK        
        ,CCM.MATLQLTY_DEC_GRD
        ,LCM.QLTY_DCS_DTIME   
        ,XB_PACKAGE.GF_USER_FIND(CCM.MATLQLTY_DEC_EMP_ID)    MATLQLTY_DEC_EMP_ID
        ,'N'                       OVER_DCS     
        ,DECODE(CCM.TOT_DEC_GRD , NULL, '' , 'Y') TOT_OVER    
         FROM TBH02_COIL_COMM  CCM  , TBB02_LOT_COM LCM , TBB02_LOT_COIL LCL
         WHERE        LCM.SMP_PROG_CD               = 'E'
         AND          LCL.COIL_NO                   = CCM.COIL_NO
         AND          LCM.SMP_NO                    = LCL.SMP_NO
	</query>

	<query id="UIB020140_04.SELECT" desc="判定实绩查询 客户和标准材质最大，最小等，材质实际值及判定结果">
     ------------------------------------------------------------
     --位置SMP_CUT_LOC为非W的情况；对于DSN_KIND ， 客户是1，标准是2 
     ------------------------------------------------------------       
        SELECT 
        A.QLTY_CD
        , to_number('') QLTY_SEQ
        , max(decode(A.DSN_KIND , '1' ,A.QLTY_MIN  , null))  CUST_QLTY_MIN   --客户材质
        , max(decode(A.DSN_KIND , '1' ,A.QLTY_MAX  , null))  CUST_QLTY_MAX
        , max(decode(A.DSN_KIND , '1' ,A.QLTY_UNIT , null))  CUST_QLTY_UNIT

        , max(decode(A.DSN_KIND , '2' ,A.QLTY_MIN  , null))  SPEC_QLTY_MIN   --标准材质
        , max(decode(A.DSN_KIND , '2' ,A.QLTY_MAX  , null))  SPEC_QLTY_MAX
        , max(decode(A.DSN_KIND , '2' ,A.QLTY_UNIT , null))  SPEC_QLTY_UNIT

        , B.QLTY_VAL_WK     QLTY_VAL_WK1      --材质实绩值
        , B.QLTY_UNIT_WK    QLTY_UNIT_WK1      --材质TYPE
        ,
        (
         SELECT QLTY_DCS_RST FROM TBB02_DCS_QLTY WHERE COIL_NO = ? AND QLTY_CD = A.QLTY_CD
        ) QLTY_DCS_RST
        FROM TBB01_ORD_QLTY A  ,  TBB02_WORK_QLTY B
        WHERE               A.QLTY_CD         = B.QLTY_CD  
        AND                 B.SMP_NO          = ? 
		AND                 B.SMP_CUT_LOC     = ?
        AND                 ORD_NO||ORD_SEQ   = ? 
		AND                 A.QLTY_CD        != 'SAMP'
		GROUP  BY           A.QLTY_CD  , B.QLTY_VAL_WK  ,  B.QLTY_UNIT_WK
		               
        ------------------------------
        UNION ALL
        -------------------------------
         SELECT 
          A.QLTY_CD
        , B.QLTY_SEQ
        
        , max(decode(A.DSN_KIND , '1' ,A.QLTY_MIN , null))  CUST_QLTY_MIN   --客户材质
        , max(decode(A.DSN_KIND , '1' ,A.QLTY_MAX , null))  CUST_QLTY_MAX
        , max(decode(A.DSN_KIND , '1' ,A.QLTY_UNIT , null)) CUST_QLTY_UNIT

        , max(decode(A.DSN_KIND , '2' ,A.QLTY_MIN , null))  SPEC_QLTY_MIN   --标准材质
        , max(decode(A.DSN_KIND , '2' ,A.QLTY_MAX , null))  SPEC_QLTY_MAX
        , max(decode(A.DSN_KIND , '2' ,A.QLTY_UNIT , null)) SPEC_QLTY_UNIT

        , B.QLTY_VAL_WK     QLTY_VAL_WK1      --材质实绩值
        , B.QLTY_UNIT_WK    QLTY_UNIT_WK1     --材质单位
        ,
        (
         SELECT QLTY_DCS_RST FROM TBB02_REDCS_QLTY WHERE COIL_NO = ? AND QLTY_CD = A.QLTY_CD AND QLTY_SEQ = B.QLTY_SEQ
        ) QLTY_DCS_RST
        FROM TBB01_ORD_QLTY A , TBB02_REWORK_QLTY B
        WHERE               A.QLTY_CD        =  B.QLTY_CD  
		AND                 B.SMP_NO         =  ? 
		AND                 B.SMP_CUT_LOC    =  ?
        AND                 ORD_NO||ORD_SEQ  =  ? 
		AND                 A.QLTY_CD       !=  'SAMP'
		GROUP  BY   A.QLTY_CD ,  B.QLTY_SEQ , B.QLTY_VAL_WK , B.QLTY_UNIT_WK
 
	</query>
	
	<query id="UIB020140_05.SELECT" desc="判定实绩查询 客户和标准材质最大，最小等，材质实际值及判定结果">
		------------------------------------------------------------
        --位置SMP_CUT_LOC为为W的情况，1为头，2为尾；对于DSN_KIND ， 客户是1，标准是2 
        ------------------------------------------------------------       
        SELECT 
          A.QLTY_CD
        , to_number('') QLTY_SEQ
        , max(decode(A.DSN_KIND , '1' ,A.QLTY_MIN  , null))  CUST_QLTY_MIN   --客户材质
        , max(decode(A.DSN_KIND , '1' ,A.QLTY_MAX  , null))  CUST_QLTY_MAX
        , max(decode(A.DSN_KIND , '1' ,A.QLTY_UNIT , null))  CUST_QLTY_UNIT

        , max(decode(A.DSN_KIND , '2' ,A.QLTY_MIN  , null))  SPEC_QLTY_MIN   --标准材质
        , max(decode(A.DSN_KIND , '2' ,A.QLTY_MAX  , null))  SPEC_QLTY_MAX
        , max(decode(A.DSN_KIND , '2' ,A.QLTY_UNIT , null))  SPEC_QLTY_UNIT

        , max(decode(B.SMP_CUT_LOC , '1' , QLTY_VAL_WK))  QLTY_VAL_WK1    --材质实绩值
        , max(decode(B.SMP_CUT_LOC , '1' , QLTY_UNIT_WK)) QLTY_UNIT_WK1   --材质单位
        , max(decode(B.SMP_CUT_LOC , '2' , QLTY_VAL_WK))  QLTY_VAL_WK2    --材质实绩值
        , max(decode(B.SMP_CUT_LOC , '2' , QLTY_UNIT_WK)) QLTY_UNIT_WK2   --材质单位
        ,
        (
         SELECT QLTY_DCS_RST FROM TBB02_DCS_QLTY WHERE COIL_NO = ? AND QLTY_CD = A.QLTY_CD
        ) QLTY_DCS_RST
        FROM TBB01_ORD_QLTY A , TBB02_WORK_QLTY B
        WHERE               A.QLTY_CD        =  B.QLTY_CD  
		AND                 B.SMP_NO         =  ? 
        AND                 ORD_NO||ORD_SEQ  =  ? 
		AND                 A.QLTY_CD       !=  'SAMP'
		GROUP BY   A.QLTY_CD
        ------------------------------
        UNION ALL
        -------------------------------
         SELECT 
          A.QLTY_CD
        , B.QLTY_SEQ
        
        , max(decode(A.DSN_KIND , '1' ,A.QLTY_MIN , null))  CUST_QLTY_MIN   --客户材质
        , max(decode(A.DSN_KIND , '1' ,A.QLTY_MAX , null))  CUST_QLTY_MAX
        , max(decode(A.DSN_KIND , '1' ,A.QLTY_UNIT , null)) CUST_QLTY_UNIT

        , max(decode(A.DSN_KIND , '2' ,A.QLTY_MIN , null))  SPEC_QLTY_MIN   --标准材质
        , max(decode(A.DSN_KIND , '2' ,A.QLTY_MAX , null))  SPEC_QLTY_MAX
        , max(decode(A.DSN_KIND , '2' ,A.QLTY_UNIT , null)) SPEC_QLTY_UNIT

        , max(decode(B.SMP_CUT_LOC , '1' , QLTY_VAL_WK))    QLTY_VAL_WK    --材质实绩值，1表头
        , max(decode(B.SMP_CUT_LOC , '1' , QLTY_UNIT_WK))   QLTY_TYPE_WK   --材质类型，1表头
        , max(decode(B.SMP_CUT_LOC , '2' , QLTY_VAL_WK))    QLTY_VAL_WK1   --材质实绩值，2表尾
        , max(decode(B.SMP_CUT_LOC , '2' , QLTY_UNIT_WK))   QLTY_TYPE_WK1  --材质类型，2表尾
        ,
        (
         SELECT QLTY_DCS_RST FROM TBB02_REDCS_QLTY WHERE COIL_NO = ? AND QLTY_CD = A.QLTY_CD AND QLTY_SEQ = B.QLTY_SEQ
        ) QLTY_DCS_RST
        FROM TBB01_ORD_QLTY A , TBB02_REWORK_QLTY B
        WHERE               A.QLTY_CD       = B.QLTY_CD  
		AND                 B.SMP_NO        = ?
        AND                 ORD_NO||ORD_SEQ = ? 
        AND                 A.QLTY_CD      != 'SAMP'
		GROUP BY A.QLTY_CD , B.QLTY_SEQ
	</query>
	
	
	<query id="UIB020140_01.UPDATE" desc="取消判定">
		UPDATE      TBB02_LOT_COM T
        SET         T.SMP_PROG_CD        =       'D'
                   ,T.QLTY_DCS_TP        =       ''
                   ,T.QLTY_DCS_DTIME     =       ''
        WHERE       T.SMP_NO             =       ?
	</query>
	
	<query id="UUIB020140_02.UPDATE" desc="">
		UPDATE      TBH02_COIL_COMM
		SET         MATLQLTY_DEC_GRD     =      ''
		           ,MATLQLTY_DEC_EMP_ID  =      ''
		WHERE       SAMPL_NO             =      ?
	</query>
</queryMap>