XinYu_RZMES/.svn/pristine/95/957cec00799f9a407e7c371d88a9202884077b0d.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="综合判定确定">
	
	<query id="UIB020235_01.SELECT" desc="获得判定的钢卷的信息">
	    SELECT      'N'  CHK
       ,CCM.COIL_NO
	   ,CCM.OLD_SAMPL_NO	
	   ,CCM.SAMPL_NO
	   ,DECODE(LCL.ORD_NO||LCL.ORD_SEQ , NULL , CCM.ORD_NO||CCM.ORD_SEQ , LCL.ORD_NO||LCL.ORD_SEQ)  ORD
	   ,DECODE(LCM.SPEC_ABBSYM , NULL , CCM.SPEC_ABBSYM , LCM.SPEC_ABBSYM)                          SPEC_ABBSYM
	   ,DECODE(LCM.SPEC_STL_GRD, NULL , CCM.SPEC_STL_GRD ,LCM.SPEC_STL_GRD)                         SPEC_STL_GRD
       ,CCM.INGR_DEC_GRD
       ,CCM.MATLQLTY_DEC_GRD
       ,CCM.SIZE_DEC_RST
       ,CCM.WGT_DEC_RST
       ,CCM.PROC_DEC_RST
       ,CCM.EXTSHAPE_DEC_GRD
       ,CCM.TOT_DEC_GRD
       ,LCM.SMP_NO
       ,LCM.SMP_CUT_LOC  
       ,DECODE(LCM.OVER_SMP_TP , NULL , NULL , SUBSTR(LCM.SMP_NO , 14))  SMP_COUNT
        FROM     TBH02_COIL_COMM  CCM   , TBB02_LOT_COIL LCL , TBB02_LOT_COM LCM
        WHERE   (CCM.MATLQLTY_DEC_GRD = '0' OR  CCM.MATLQLTY_DEC_GRD = '1'
                 OR ( LCM.OVER_SMP_TP  IS NOT NULL 
		              AND   CCM.MATLQLTY_DEC_GRD IS NOT NULL)  )
         AND      CCM.SIZE_DEC_RST     IS NOT NULL
   	     AND      CCM.WGT_DEC_RST      IS NOT NULL
--		 AND      CCM.PROC_DEC_RST     IS NOT NULL
--		 AND      (CCM.SPM_YN IS NULL OR CCM.SPM_YN = 'N') --取消平整限制 20190115
		 AND      CCM.EXTSHAPE_DEC_GRD IS NOT NULL
		 AND      CCM.TOT_DEC_GRD      IS     NULL
		 AND      CCM.SAMPL_NO         =      LCM.SMP_NO(+)
		 AND      CCM.SAMPL_CUT_LOC    =      LCM.SMP_CUT_LOC(+)
		 AND      CCM.ORD_FL           =      '1'          
		 AND      CCM.COIL_NO          =      LCL.COIL_NO(+)
		 ORDER  BY  CCM.OLD_SAMPL_NO 
	</query>
	
	<query id="UIB020235_01_0.SELECT" desc="获得判定的钢卷的信息">
	    SELECT      'N'  CHK
       ,CCM.COIL_NO
	   ,CCM.OLD_SAMPL_NO	
	   ,CCM.SAMPL_NO
	   ,DECODE(LCL.ORD_NO||LCL.ORD_SEQ , NULL , CCM.ORD_NO||CCM.ORD_SEQ , LCL.ORD_NO||LCL.ORD_SEQ)  ORD
	   ,DECODE(LCM.SPEC_ABBSYM , NULL , CCM.SPEC_ABBSYM , LCM.SPEC_ABBSYM)   SPEC_ABBSYM
	   ,DECODE(LCM.SPEC_STL_GRD, NULL , CCM.SPEC_STL_GRD ,LCM.SPEC_STL_GRD)  SPEC_STL_GRD
       ,CCM.INGR_DEC_GRD
       ,CCM.MATLQLTY_DEC_GRD
       ,CCM.SIZE_DEC_RST
       ,CCM.WGT_DEC_RST
       ,CCM.PROC_DEC_RST
       ,CCM.EXTSHAPE_DEC_GRD
       ,CCM.TOT_DEC_GRD
       ,LCM.SMP_NO
       ,LCM.SMP_CUT_LOC  
       ,DECODE(LCM.OVER_SMP_TP , NULL , NULL , SUBSTR(LCM.SMP_NO , 14))  SMP_COUNT
        FROM     TBH02_COIL_COMM  CCM   , TBB02_LOT_COIL LCL , TBB02_LOT_COM LCM
        WHERE    (CCM.MATLQLTY_DEC_GRD IS NOT NULL  AND CCM.MATLQLTY_DEC_GRD != '2')
         AND      CCM.SIZE_DEC_RST     IS NOT NULL
   	     AND      CCM.WGT_DEC_RST      IS NOT NULL
--		 AND      CCM.PROC_DEC_RST     IS NOT NULL
		 AND      CCM.EXTSHAPE_DEC_GRD IS NOT NULL
		 AND      CCM.TOT_DEC_GRD      IS     NULL
--		 AND      (CCM.SPM_YN IS NULL OR CCM.SPM_YN = 'N')
		 AND      CCM.SAMPL_NO         =      LCM.SMP_NO(+)
		 AND      CCM.SAMPL_CUT_LOC    =      LCM.SMP_CUT_LOC(+)
		 AND      CCM.ORD_FL          =      '1'          
		 AND      CCM.COIL_NO         =      LCL.COIL_NO(+)
		 ORDER  BY  CCM.OLD_SAMPL_NO 
	</query>
	
	<query id="UIB020235_01.CALL" desc="综合判定">
		{call NIB028080(?,?,?)}
	</query>
	
	<query id="UIB020235_02.CALL" desc="复合元素计算">
		{call ZL_HOST_DCS01.PIB020_CHEM_ANYS(?,?,?,?)}
	</query>
	
	<query id="UIB020235_02.SELECT" desc="">
		  SELECT     
		  CC.COIL_NO 
		, CC.ORD_NO 
		, CC.ORD_SEQ 
        , CC.ORD_FL   
        , CC.ACT_WGT
        , CC.CUR_PROG_CD 
        , CC.BEF_PROG_CD
          FROM         TBH02_COIL_COMM  CC   
          WHERE        CC.COIL_NO    =  ?
	</query>
	
	<query id="UIB020235_03.SELECT" desc="不合格成分查询">
		  SELECT         DECODE(SEQ.COMP_YN , 'Y' , SEQ.COMP_CAL , SEQ.CHEM_CD ) CHEM_CD
         			    ,OI.CHEM_MIN
						,OI.CHEM_MAX
						,OI.DSN_KIND
						,INGR.CHEM_VAL                             
		 FROM            TBB01_ORD_INGR   OI , TBB02_WORK_INGR INGR , TBB01_SEQ_INGR SEQ
         WHERE           OI.CHEM_CD             =      SEQ.CHEM_CD
         AND             OI.CHEM_CD             =      INGR.CHEM_CD(+)
         AND             OI.DSN_KIND            =      '2'
         AND             INGR.CHARGE_NO(+)      =      SUBSTR( ? , 1 , 10)
         AND             INGR.CHEM_SEQ(+)       =      ZL_HOST_DCS01.FIB020_LST_INGR(SUBSTR(?,1,10))
		 AND             INGR.PROC_CD(+)        =      'JJ'
         AND             OI.ORD_NO              =      ?
         AND             OI.ORD_SEQ             =      ?
         ORDER BY        SEQ.COMP_YN , SEQ.DISPLAY_SEQ
	</query>
	
	<query id="UIB020235_04.SELECT" desc="">
		SELECT               OQ.ORD_NO
                            ,OQ.ORD_SEQ
                            ,(SELECT t.QLTY_CD_CFNM FROM TBB01_SEQ_QLTY T WHERE T.QLTY_CD = OQ.QLTY_CD  ) QLTY_CD
                            ,OQ.DSN_KIND 
                            ,OQ.QLTY_MIN
                            ,OQ.QLTY_MAX
                            ,OQ.QLTY_UNIT
                            ,OQ.QLTY_TYPE
                            ,WQ.QLTY_VAL_WK
                            ,WQ.QLTY_UNIT_WK                            
                            ,WQ.QLTY_TYPE_WK
              FROM           TBB01_ORD_QLTY           OQ
                            ,TBB02_LOT_COIL           LL
                            ,(SELECT *
                             FROM TBB02_WORK_QLTY     W
                                 ,TBB02_LOT_COIL      LL
                             WHERE     LL.COIL_NO     =         ?
                             AND       W.SMP_NO       =         LL.SMP_NO
                             AND       W.SMP_CUT_LOC  =         LL.SMP_CUT_LOC)   WQ
              WHERE          LL.COIL_NO               =         ?
              AND            LL.ORD_NO                =         OQ.ORD_NO
              AND            LL.ORD_SEQ               =         OQ.ORD_SEQ
              AND            OQ.ORD_NO                =         WQ.ORD_NO(+)
              AND            OQ.ORD_SEQ               =         WQ.ORD_SEQ(+)
              AND            OQ.DSN_KIND              =          (SELECT MIN(QLTY.DSN_KIND) FROM  TBB01_ORD_QLTY QLTY WHERE QLTY.ORD_NO = OQ.ORD_NO  AND QLTY.ORD_SEQ = OQ.ORD_SEQ )
              AND            OQ.QLTY_CD              !=         'SAMP'                                                                                                              
              AND            OQ.QLTY_CD               =         WQ.QLTY_CD(+)
	</query>
	
	<query id="UIB020235_05.SELECT" desc="重量判定不合格原因">
		SELECT PROD_WGT_MIN||'~'||PROD_WGT_MAX   INSTR_COIL_WGT , ACT_WGT FROM TBB01_ORD_HEAD OH , TBH02_COIL_COMM CC
		WHERE    OH.ORD_NO      =  ?
		AND      OH.ORD_SEQ     =  ?
		AND      CC.COIL_NO     =  ?
	</query>
	
	<query id="UIB020235_06.SELECT" desc="尺寸判定不合格原因">
		SELECT  T.INSTR_COIL_THK||'*'||T.INSTR_COIL_WTH  INSTR_SIZE , T.COIL_THK||'*'||T.COIL_WTH  ACT_SIZE
         FROM   TBH02_COIL_COMM T 
		 WHERE  T.COIL_NO    = ? 
	</query>
	
	<query id="UIB020235_07.SELECT" desc="">
		WITH QQ AS( SELECT 
        SM_CFNM
       ,SM_CD
        FROM TBZ00_COMMCD WHERE LG_CD='H02001'  )
		SELECT 
		MAX(DECODE(CC.CRK_CD1 ,  QQ.SM_CD , QQ.SM_CFNM ))||'   '||MAX(DECODE(CC.CRK_CD2 ,  QQ.SM_CD , QQ.SM_CFNM ))||'   '||MAX(DECODE(CC.CRK_CD3 ,  QQ.SM_CD , QQ.SM_CFNM ))  CRK_CD
		FROM TBH02_COIL_COMM CC , QQ WHERE CC.COIL_NO   =  ?
	</query>
	
	<query id="UIB020235_01.UPDATE" desc="综合判定人和时间">
		UPDATE    TBH02_COIL_COMM
		SET       TOT_DEC_DTIME  =  to_char(sysdate , 'YYYYMMDDHH24MISS')
				 ,TOT_DEC_EMP_ID =  ?
		WHERE     COIL_NO        =  ?
	</query>
</queryMap>