XinYu_RZMES/.svn/pristine/d6/d6655a1241e66fa78bdcdffe1be9c4c81dde8468.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="试样材质实绩查询">
	
    <query id="UIB020120_01.INITSELECT" desc="获得试样进程代码信息，初始化页面" fetchSize="10">   
        <![CDATA[
         SELECT '---' LABEL , '' VALUE FROM DUAL
		 UNION  ALL
		 SELECT DISTINCT T.SPEC_STL_GRD , T.SPEC_STL_GRD FROM TBB01_KEY_PROD T
		 ORDER BY LABEL	
        ]]>   
    </query> 
		
	<query id="UIB020120_01.SELECT" desc="获得TBB02_WORK_QLTY , TBB02_REWORK_QLTY中有实绩值的所有材质代码 多条件可选查询">
		SELECT  DISTINCT  SQ.QLTY_CD_CFNM||QLTY_SEQ AAA , QQ.SMP_CUT_LOC , QQ.QLTY_CD , QQ.QLTYCD , QQ.QLTY_SEQ , SQ.DISPLAY_SEQ
        FROM TBB01_SEQ_QLTY SQ ,  TBB02_LOT_COM LCM , TBH02_COIL_COMM CCM , 
        (
          SELECT WQ.SMP_NO , WQ.SMP_CUT_LOC , WQ.QLTY_CD, WQ.QLTY_VAL_WK , WQ.QLTY_CD QLTYCD , '' QLTY_SEQ FROM TBB02_WORK_QLTY  WQ    
          UNION ALL
          SELECT RQ.SMP_NO , RQ.SMP_CUT_LOC , RQ.QLTY_CD||RQ.QLTY_SEQ , RQ.QLTY_VAL_WK , RQ.QLTY_CD , RQ.QLTY_SEQ FROM TBB02_REWORK_QLTY RQ   
        ) QQ
        WHERE    SQ.QLTY_CD         =   QQ.QLTYCD 
        AND      CCM.SAMPL_NO       =   LCM.SMP_NO 
        AND      CCM.SAMPL_CUT_LOC  =   LCM.SMP_CUT_LOC 
        AND      QQ.SMP_NO          =   LCM.SMP_NO
        AND      QQ.SMP_CUT_LOC     =   LCM.SMP_CUT_LOC  
	</query>
	
	<query id="UIB020120_02START.SELECT" desc="动态SQL前半部分">
		SELECT   SMP_NO  ,  SMP_CUT_LOC  
	</query>	
	
	<query id="UIB020120_0201MID.SELECT" desc="需要动态生成部分 ，{0}处以材质代码替换，{1}处以序号替换">
		, MAX(DECODE('{0}' , QLTY_CD  , QLTY_VAL_WK , NULL))  QLTY_VAL_WK{1}
	</query>	
	
	<query id="UIB020120_0201END.SELECT" desc="TBB02_WORK_QLTY中，动态SQL后半部分">
    	FROM   
		 (SELECT WQ.SMP_NO , WQ.SMP_CUT_LOC , WQ.QLTY_CD, WQ.QLTY_VAL_WK FROM TBB02_WORK_QLTY  WQ 
          UNION ALL
          SELECT RQ.SMP_NO , RQ.SMP_CUT_LOC , RQ.QLTY_CD||RQ.QLTY_SEQ , RQ.QLTY_VAL_WK FROM TBB02_REWORK_QLTY RQ 
		)  WQ
		GROUP   BY  SMP_NO  , SMP_CUT_LOC 
	</query>
	
	<query id="UIB020120_0202MID.SELECT" desc="需要动态生成部分 ，{0}处以材质代码替换，{1}处以序号替换">
		,  max(decode('{0}' , QLTY_CD||QLTY_SEQ  , QLTY_VAL_WK , NULL))  QLTY_VAL_WK{1}
		,  max(decode('{0}' , QLTY_CD||QLTY_SEQ  , QLTY_UNIT_WK, NULL))  QLTY_UNIT_WK{1}
	</query>
	
	<query id="UIB020120_03START.SELECT" desc="动态SQL前半部分">
		SELECT    SMP_NO  
		        , SMP_CUT_LOC  
	</query>	
	<query id="UIB020120_03END.SELECT" desc="TBB02_REWORK_QLTY中，动态SQL后半部分">
    	FROM    TBB02_REWORK_QLTY
		GROUP   BY  SMP_NO  , SMP_CUT_LOC 
	</query>
	
	<query id="UIB020120_03.SELECT" desc="获得TBB02表的实绩信息">
		SELECT ROWNUM , X.* , Y.*  FROM
		( 
		    SELECT     DISTINCT
			           CC.OLD_SAMPL_NO  
			          ,A.SMP_NO                --样片号
                      ,A.SMP_PROG_CD           --试样进程代码
                      ,A.SMP_WORK_DTIME        --检验日期
                      ,A.QLTY_DCS_DTIME        --判定日期 
                      ,CC.MATLQLTY_DEC_GRD
                      ,B.COIL_THK
					  ,CC.SPEC_STL_GRD
					  ,CC.SPM_RMK
            ,(SELECT T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD='H02001' AND T.SM_CD = CC.CRK_CD1)  CRK_CD1
            ,(SELECT T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD='H02001' AND T.SM_CD = CC.CRK_CD2)  CRK_CD2
            ,(SELECT T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD='H02001' AND T.SM_CD = CC.CRK_CD3)  CRK_CD3
            ,(SELECT T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD='H02001' AND T.SM_CD = CC.CRK_CD4)  CRK_CD4
            ,(SELECT T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD='H02001' AND T.SM_CD = CC.CRK_CD5)  CRK_CD5
    	     FROM     TBB02_LOT_COM A  , TBB02_LOT_COIL B , TBH02_COIL_COMM CC
		     WHERE    A.SMP_NO           =   B.SMP_NO
		     AND      A.SMP_CUT_LOC      =   B.SMP_CUT_LOC
			 AND      B.COIL_NO          =   CC.COIL_NO
             AND      CC.SMP_DIR_PIC_YN  =   'Y'
		 )  X
		 , 
	</query>
	
	<query id="UIB020120_04.SELECT" desc="获得TBB02_REWORK_QLTY中有实绩值的所有材质代码 多条件可选查询">
		SELECT     DISTINCT   (
		                       SELECT       QLTY_CD_CFNM 
		                       FROM         TBB01_SEQ_QLTY 
		                       WHERE        QLTY_CD = x.QLTY_CD  
		                      )||QLTY_SEQ   QLTY_CDNM   
		,          QLTY_CD||QLTY_SEQ        QLTY_CD
		FROM       TBB02_REWORK_QLTY x  
		WHERE    ( X.QLTY_VAL_WK   IS   NOT   NULL  OR X.QLTY_UNIT_WK IS NOT NULL)
		AND        x.SMP_NO LIKE ?
	</query>

</queryMap>