XinYu_RZMES/.svn/pristine/ac/ac0d49b506326dd7a8a1111a6f0058e33ef034d7.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="不合格钢卷号手工材质判定">
	
	<query id="UIB020160_01.INITSELECT" desc="表格2中”材质代码“初始化">
		SELECT 
		QLTY_CD_CFNM    LABEL
		, QLTY_CD       VALUE 
		FROM TBB01_SEQ_QLTY
	</query>
		
	<query id="UIB020160_02.INITSELECT" desc="试样号初始化">
		SELECT     DISTINCT
		           substr(A.SMP_NO , 1 , 10)          LABEL             
		         , substr(A.SMP_NO , 1 , 10)          VALUE
        FROM       TBB02_LOT_COIL A  ,   TBB02_LOT_COM B  ,  TBH02_COIL_COMM  C
        WHERE      A.COIL_NO             =   C.COIL_NO         
        AND        A.SMP_NO              =   B.SMP_NO
        AND        A.SMP_CUT_LOC         =   B.SMP_CUT_LOC
        AND        C.MATLQLTY_DEC_GRD    =   '2'
		AND        C.TOT_DEC_GRD IS NULL
	</query>
	
	<query id="UIB020160_011.SELECT" desc="试样号初始化">
		SELECT     DISTINCT
		           substr(A.SMP_NO , 11)          LABEL             
		         , substr(A.SMP_NO , 11)          VALUE
        FROM       TBB02_LOT_COIL A  ,   TBB02_LOT_COM B  ,  TBH02_COIL_COMM  C
        WHERE      A.COIL_NO             =   C.COIL_NO         
        AND        A.SMP_NO              =   B.SMP_NO
        AND        A.SMP_CUT_LOC         =   B.SMP_CUT_LOC
        AND        C.MATLQLTY_DEC_GRD    =   '2'
		AND        C.TOT_DEC_GRD IS NULL
		AND        substr(A.SMP_NO , 1 , 10) = ?
	</query>
	
	<query id="UIB020160_01.SELECT" desc="多条件查询不合格钢卷号">
		 SELECT 
		 'N' CHK		
		, A.COIL_NO               --钢卷号
		, A.SMP_TIT_YN            --是否取样
		, A.SMP_NO                --试样号
		, A.SMP_CUT_LOC           --取样位置
		, A.ORD_NO||A.ORD_SEQ   ORD                    ---订单号
		, ( SELECT SMP_LOT_CD||SMP_LEN_LOC||SMP_WTH_LOC 
		  FROM    TBB02_INS_QLTY 
		  WHERE   SMP_NO      =   B.SMP_NO 
		  AND     SMP_CUT_LOC =   B.SMP_CUT_LOC 
		  AND     QLTY_CD     =  'SAMP'
		  )       SAMPLINGCODE       --取样代码
		, B.EXAMORG_CD               --检验机关
		, B.SPEC_ABBSYM              --标准号
		, B.SPEC_STL_GRD		     --标准钢号
		, B.ORD_USE_TP               --订单用途
		, A.COIL_THK                 --钢卷厚度
		, A.QLTY_MAN_DEC_GRD         --材质结果
		, B.QLTY_MAN_INS_TP          --是否为手工判定
		, C.MATLQLTY_DEC_GRD
		  FROM      TBB02_LOT_COIL A , TBB02_LOT_COM B  ,  TBH02_COIL_COMM  C
		  WHERE     A.COIL_NO          =   C.COIL_NO         
		  AND       A.SMP_NO           =   B.SMP_NO
		  AND       A.SMP_CUT_LOC      =   B.SMP_CUT_LOC
		  AND       C.TOT_DEC_GRD IS NULL
	</query>
	
	<query id="UIB020160_02.SELECT" desc="获取材质实绩 ,注意，这是SMP_CUT_LOC非W的情况">
		----------------------------------------------------------------------------
		--取样位置非W的情况下
		----------------------------------------------------------------------------
		SELECT 'N' CHK	, X.* , Y.QLTY_DCS_RST , Y.COIL_NO  COIL_NO1 ,  decode(Y.QLTY_CD , null , 'Y')  EX
		FROM
		(SELECT 
		A.SMP_NO          SMP_NO1
	  , A.SMP_CUT_LOC		SMP_CUT_LOC1	 	
	  , A.QLTY_CD
	  , to_number('')     QLTY_SEQ  --实验次数
	  , A.QLTY_CD         QLTY_NM
	  , A.QLTY_MIN
	  , A.QLTY_MAX
	  , A.QLTY_UNIT
	  , B.QLTY_VAL_WK     QLTY_VAL_WK1   --实绩值
	  , B.QLTY_UNIT_WK    QLTY_UNIT_WK1  --类型
		FROM TBB02_INS_QLTY A  ,  TBB02_WORK_QLTY B
		WHERE              (A.SMP_NO          =     B.SMP_NO
		AND                 A.SMP_CUT_LOC     =     B.SMP_CUT_LOC
		AND                 A.QLTY_CD         =     B.QLTY_CD)
		AND                 A.SMP_NO          =     ?
		AND                 A.SMP_CUT_LOC     =     ?
		) X  LEFT JOIN      TBB02_DCS_QLTY  Y                 --可能还未判定，所以这里需要使用左连接
		ON          X.QLTY_CD      =     Y.QLTY_CD
		AND         Y.COIL_NO      =     ?
		WHERE       X.QLTY_CD     !=     'SAMP'
		UNION  		
		SELECT 'N' CHK	, X.* , Y.QLTY_DCS_RST , Y.COIL_NO  COIL_NO1  ,  decode(Y.QLTY_CD , null , 'Y')  EX
		FROM
		(SELECT 
		A.SMP_NO          SMP_NO1
	  , A.SMP_CUT_LOC		SMP_CUT_LOC1	 	
	  , A.QLTY_CD
	  , B.QLTY_SEQ        --实验次数
	  , A.QLTY_CD         QLTY_NM
	  , A.QLTY_MIN
	  , A.QLTY_MAX
	  , A.QLTY_UNIT
	  , B.QLTY_VAL_WK      QLTY_VAL_WK1   --实绩值
	  , B.QLTY_UNIT_WK     QLTY_UNIT_WK1  --类型
		FROM TBB02_INS_QLTY A ,  TBB02_REWORK_QLTY B
		WHERE   (A.SMP_NO         =     B.SMP_NO
		AND      A.SMP_CUT_LOC     =     B.SMP_CUT_LOC
		AND      A.QLTY_CD         =     B.QLTY_CD)
		AND      A.SMP_NO          =     ?
		AND      A.SMP_CUT_LOC     =     ?
		) X     LEFT JOIN        TBB02_REDCS_QLTY  Y                    --可能还未判定，所以这里需要使用左连接
		ON          X.QLTY_CD      =     Y.QLTY_CD
		AND         X.QLTY_SEQ     =     Y.QLTY_SEQ		
		AND         Y.COIL_NO      =     ?
		WHERE       X.QLTY_CD     !=     'SAMP'
	</query>
	
	<query id="UIB020160_03.SELECT" desc="获取材质实绩 ,注意，这是SMP_CUT_LOC为W的情况">
		-------------------------------------------------------------------------
		--取样位置为W时
		-------------------------------------------------------------------------
		SELECT    'N' CHK	,
		          X.* 
		        , Y.QLTY_DCS_RST 
		        , Y.COIL_NO       COIL_NO1
		        , decode(Y.QLTY_CD , null , 'Y')  EX
		FROM    (SELECT 
			  A.SMP_NO                SMP_NO1
			, A.SMP_CUT_LOC			  SMP_CUT_LOC1	 
			, A.QLTY_CD
			, to_number('')           QLTY_SEQ  --实验次数
			, A.QLTY_CD               QLTY_NM
			, A.QLTY_MIN
			, A.QLTY_MAX
			, A.QLTY_TYPE
			, max(decode(A.SMP_CUT_LOC||B.SMP_CUT_LOC , 'W1' , QLTY_VAL_WK))      QLTY_VAL_WK1
			, max(decode(A.SMP_CUT_LOC||B.SMP_CUT_LOC , 'W1' , QLTY_UNIT_WK))     QLTY_UNIT_WK1
			, max(decode(A.SMP_CUT_LOC||B.SMP_CUT_LOC , 'W2' , QLTY_VAL_WK))      QLTY_VAL_WK2
			, max(decode(A.SMP_CUT_LOC||B.SMP_CUT_LOC , 'W2' , QLTY_UNIT_WK))     QLTY_UNIT_WK2
			FROM TBB02_INS_QLTY A  ,  TBB02_WORK_QLTY B
			WHERE   (A.SMP_NO         =   B.SMP_NO
			AND      A.QLTY_CD         =   B.QLTY_CD)
			AND      A.SMP_NO          =   ?
		    GROUP   BY        A.SMP_NO                
			                , A.SMP_CUT_LOC			   
			                , A.QLTY_CD       
							, A.QLTY_CD              
							, A.QLTY_MIN
							, A.QLTY_MAX
							, A.QLTY_TYPE
			) X 
		LEFT JOIN TBB02_DCS_QLTY Y
		ON           X.QLTY_CD      =     Y.QLTY_CD
		AND          Y.COIL_NO      =     ?
		WHERE        X.QLTY_CD     !=     'SAMP'
		UNION 		
		SELECT   'N' CHK	,  X.*  
				,  Y.QLTY_DCS_RST 
				,  Y.COIL_NO  COIL_NO1
				,  decode(Y.QLTY_CD , null , 'Y')  EX
		FROM
			(SELECT 
			  A.SMP_NO               SMP_NO1
			, A.SMP_CUT_LOC			 SMP_CUT_LOC1	 
			, A.QLTY_CD
			, B.QLTY_SEQ           --实验次数
			, A.QLTY_CD              QLTY_NM
			, A.QLTY_MIN
			, A.QLTY_MAX
			, A.QLTY_TYPE
			, max(decode(A.SMP_CUT_LOC||B.SMP_CUT_LOC , 'W1' , QLTY_VAL_WK))      QLTY_VAL_WK1
			, max(decode(A.SMP_CUT_LOC||B.SMP_CUT_LOC , 'W1' , QLTY_UNIT_WK))     QLTY_UNIT_WK1
			, max(decode(A.SMP_CUT_LOC||B.SMP_CUT_LOC , 'W2' , QLTY_VAL_WK))      QLTY_VAL_WK2
			, max(decode(A.SMP_CUT_LOC||B.SMP_CUT_LOC , 'W2' , QLTY_UNIT_WK))     QLTY_UNIT_WK2
			FROM TBB02_INS_QLTY A  ,   TBB02_REWORK_QLTY B
			WHERE   (A.SMP_NO      =   B.SMP_NO
			AND      A.QLTY_CD     =   B.QLTY_CD)
			AND      A.SMP_NO      =   ?
		    GROUP    BY       A.SMP_NO              
							, A.SMP_CUT_LOC			 
							, A.QLTY_CD
							, B.QLTY_SEQ           
							, A.QLTY_CD             
							, A.QLTY_MIN
							, A.QLTY_MAX
							, A.QLTY_TYPE
			) X 
		LEFT JOIN TBB02_REDCS_QLTY Y
		ON          X.QLTY_CD      =     Y.QLTY_CD
		AND         X.QLTY_SEQ     =     Y.QLTY_SEQ		
		AND         Y.COIL_NO      =     ?
		WHERE       X.QLTY_CD      !=     'SAMP'
	</query>
	
	<query id="UIB020160_01.UPDATE" desc="grid1被修改时执行，更改钢卷号的材质检验结果 TBH02_COIL_COMM">
		UPDATE     TBB02_LOT_COIL  
		SET        QLTY_MAN_DEC_GRD  =  ?
		WHERE      COIL_NO           =  ?
	</query>
	
	<query id="UIB020160_011.UPDATE" desc="修改试样号的状态为D，即综合判定待机，grid2被修改时执行">
		UPDATE       TBB02_LOT_COM
        SET          SMP_PROG_CD    =   'D'
		           , MOD_ID         =    ?
		           , MOD_DTIME      =    TO_CHAR(SYSDATE , 'YYYYMMDDHH24MIHH')
        WHERE        SMP_NO         =    ?
        AND          SMP_CUT_LOC    =    ?
	</query>
	
	<query id="UIB020160_012.UPDATE" desc="grid1被修改时执行">
		UPDATE       TBB02_LOT_COM
        SET          QLTY_MAN_INS_TP    =    ?     --手工判定
		           , MOD_ID             =    ?     --修改人
		           , MOD_DTIME          =    TO_CHAR(SYSDATE , 'YYYYMMDDHH24MIHH')  --修改时间
        WHERE        SMP_NO             =    ?
        AND          SMP_CUT_LOC        =    ?
	</query>
	
	<query id="UIB020160_02.UPDATE" desc="TBB02_WORK_QLTY">
		UPDATE   TBB02_WORK_QLTY
		SET      QLTY_VAL_WK    = ?
			    ,QLTY_UNIT_WK   = ?
		        ,QLTY_WK_TP     = 'M'          --'M'表示被修改了
		WHERE    SMP_NO         = ?
		AND      SMP_CUT_LOC    = ?
		AND      QLTY_CD        = ?
		
	</query>
	
	<query id="UIB020160_03.UPDATE" desc="TBB02_REWORK_QLTY">
		UPDATE    TBB02_REWORK_QLTY
		SET      QLTY_VAL_WK    = ?
		        ,QLTY_UNIT_WK   = ?
		WHERE    SMP_NO         = ?
		AND      SMP_CUT_LOC    = ?
		AND      QLTY_CD        = ?
		AND      QLTY_SEQ       = ?
	</query>
	
	<query id="UIB020160_04.UPDATE" desc="TBB02_REDCS_QLTY">
		UPDATE    TBB02_REDCS_QLTY
		SET       QLTY_DCS_RST   = ?
		WHERE     COIL_NO        = ?
		AND       QLTY_CD        = ?
		AND       QLTY_SEQ       = ?
	</query>
	
	<query id="UIB020160_05.UPDATE" desc="">
		UPDATE     TBB02_DCS_QLTY
		SET        QLTY_DCS_RST   =  ?
		WHERE      COIL_NO        =  ?
		AND        QLTY_CD        =  ?
	</query>
	
	<query id="UIB020160_01.DELETE" desc="">
		DELETE    FROM    TBB02_REWORK_QLTY    
		WHERE             SMP_NO         = ?
		AND               SMP_CUT_LOC    = ?
		AND               QLTY_CD        = ?
		AND               QLTY_SEQ       = ?
	</query>
	
	<query id="UIB020160_02.DELETE"  desc="">
		DELETE    FROM    TBB02_REDCS_QLTY
		WHERE             QLTY_DCS_RST   = ?
		AND               COIL_NO        = ?
		AND               QLTY_CD        = ?
		AND               QLTY_SEQ       = ?
	</query>
	
	<query id="UIB020160_01.INSERT"  desc="">
		INSERT INTO  TBB02_REWORK_QLTY( 
		  QLTY_CD
		, QLTY_SEQ
		, QLTY_VAL_WK
		, QLTY_UNIT_WK
		, SMP_NO
		, SMP_CUT_LOC
		) VALUES(?,?,?,?,?,?)
	</query>
	
	<query id="UIB020160_02.INSERT"  desc="">
		INSERT INTO   TBB02_REDCS_QLTY(
		  COIL_NO
		, QLTY_CD
		, QLTY_SEQ
		, QLTY_DCS_RST
		)VALUES(?,?,?,?)
	</query>
	
	<query id="UIB020160_03.INSERT" desc="">
		INSERT INTO   TBB02_DCS_QLTY(COIL_NO , QLTY_CD , QLTY_DCS_RST)  VALUES(?,?,?)
	</query>
	
	<query id="UIB020160_01.CALLSTATEMENT" desc="">
		{call NIB020161(?,?,?,?)}
	</query>
	
</queryMap>