XinYu_RZMES/.svn/pristine/aa/aa722c27d31223ecb1d1fcdfd39980c10e0bf197.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="品质设计结果之订单状态查询（总）">
    <query id="UIB015020_01.SELECT" desc="" fetchSize="10">   
        <![CDATA[
			  SELECT DISTINCT  ORD_SEQ      FROM     TBE02_ORD_PRC  WHERE   ORD_NO = ?    --订单号
        ]]>   
    </query>
	<query id="UIB015020_02.SELECT" desc="">
		 <![CDATA[
		     SELECT
	         PKG_QUALITY_COMM.FZ00_COMM('E01001' ,T4.ORD_PROG_CD) ORD_PROG_CD         --订单进程 
	        ,T1.DSN_CMT_DATE        --设计确定日
	        ,T1.ORD_DEVLMT_DATE     --订单交货期日期
	        ,T1.PRDNM_CD            --品名代码
	        ,T1.CUST_SPEC_NO        --客户参数号
	        ,T1.CUST_SPEC_NO_INGR 
	        ,T1.CUST_SPEC_NO_QLTY 
	        ,T1.CUST_SPEC_NO_DEL
	        ,T1.ORD_THK             --订单厚度
	        ,T1.ORD_WTH             --订单宽度
	        ,PKG_QUALITY_COMM.FZ00_COMM('A01007' ,T1.ORD_USE_TP ) ORD_USE_TP  --订单用途编码
	        ,T1.SPEC_ABBSYM         --标准号
	        ,T1.SPEC_STL_GRD        --规格钢号
	        ,T1.PROD_WGT_MIN        --包装单重产品重量（下限）
	        ,T1.PROD_WGT_MAX        --包装单重产品重量（上限）
	        ,T1.ORD_CNT             --订单块数
	        ,T1.ORD_WGT             --订单重量
	        ,T1.SMS_STD_NO          --炼钢生产规范编号
	        ,T1.MILL_STD_NO         --轧钢生产规范编号
	        ,T1.COLD_STD_NO
	        ,T1.FAC_STL_GRD         --厂内钢号
	        ,T1.C_PRDNM_CD
	        ,T1.C_SPEC_STL_GRD
	        ,T1.C_SPEC_ABBSYM
	        ,PKG_QUALITY_COMM.FZ00_COMM('A01007' ,T1.C_ORD_USE_TP ) C_ORD_USE_TP  --订单用途编码C_ORD_USE_TP
	        ,T1.C_ORD_THK
	        ,T1.C_ORD_WTH
	        ,T2.CUST_NM
	        ,T1.DSN_CMT_DATE
	        ,T1.PROD_LINE
	        ,DECODE(T1.EDGE_PROC_YN , NULL , 'N' , T1.EDGE_PROC_YN) EDGE_PROC_YN
	        ,PKG_QUALITY_COMM.FZ00_COMM('A01010' ,T3.PAKMTH_TP )    PAKMTH_TP     --包装方式
             FROM    TBB01_DSN_HEAD T1 , TBZ00_CUSTOMER T2 , TBA01_ORD_LINE   T3   , TBE02_ORD_PRC T4
	         WHERE   T1.CUST_CD    =   T2.CUST_CD(+) 
             AND     T1.ORD_NO     =   T4.ORD_NO 
             AND     T1.ORD_SEQ    =   T4.ORD_SEQ
             AND     T1.ORD_NO     =   T3.ORD_NO
             AND     T1.ORD_NO     =   T3.ORD_NO
	         AND     T1.ORD_NO     =   ? 
		     AND     T1.ORD_SEQ    =   ? 
		 ]]>   
	</query>
	<query id="UIB015020_03.SELECT" desc="">
		<![CDATA[
			SELECT    CUST_NM ,ORD_PROG_CD 
			FROM      TBE02_ORD_PRC PRC , TBZ00_CUSTOMER CUST 
			WHERE     PRC.CUST_CD  =   CUST.CUST_CD
			AND       ORD_NO       =   ? 
			AND       ORD_SEQ      =   ?
		 ]]>
	</query>
	<query id="UIB015020_04.SELECT" desc="">
  		     SELECT
	         PKG_QUALITY_COMM.FZ00_COMM('E01001' ,T4.ORD_PROG_CD) ORD_PROG_CD         --订单进程 
	        ,T1.DSN_CMT_DATE        --设计确定日
	        ,T1.ORD_DEVLMT_DATE     --订单交货期日期
	        ,T1.PRDNM_CD            --品名代码
	        ,T1.CUST_SPEC_NO        --客户参数号
	        ,T1.CUST_SPEC_NO_INGR 
	        ,T1.CUST_SPEC_NO_QLTY 
	        ,T1.CUST_SPEC_NO_DEL
	        ,T1.ORD_THK             --订单厚度
	        ,T1.ORD_WTH             --订单宽度
	        ,PKG_QUALITY_COMM.FZ00_COMM('A01007' ,T1.ORD_USE_TP ) ORD_USE_TP  --订单用途编码
	        ,T1.SPEC_ABBSYM         --标准号
	        ,T1.SPEC_STL_GRD        --规格钢号
	        ,T1.PROD_WGT_MIN        --包装单重产品重量（下限）
	        ,T1.PROD_WGT_MAX        --包装单重产品重量（上限）
	        ,T1.ORD_CNT             --订单块数
	        ,T1.ORD_WGT             --订单重量
	        ,T1.SMS_STD_NO          --炼钢生产规范编号
	        ,T1.MILL_STD_NO         --轧钢生产规范编号
	        ,T1.COLD_STD_NO
	        ,T1.FAC_STL_GRD         --厂内钢号
	        ,T1.C_PRDNM_CD
	        ,T1.C_SPEC_STL_GRD
	        ,T1.C_SPEC_ABBSYM
	        ,PKG_QUALITY_COMM.FZ00_COMM('A01007' ,T1.C_ORD_USE_TP ) C_ORD_USE_TP  --订单用途编码C_ORD_USE_TP
	        ,T1.C_ORD_THK
	        ,T1.C_ORD_WTH
	        ,T2.CUST_NM
	        ,T1.DSN_CMT_DATE
	        ,T1.PROD_LINE
	        ,DECODE(T1.EDGE_PROC_YN , NULL , 'N' , T1.EDGE_PROC_YN) EDGE_PROC_YN
	        ,PKG_QUALITY_COMM.FZ00_COMM('A01010' ,T3.PAKMTH_TP )    PAKMTH_TP     --包装方式
             FROM    TBB01_ORD_HEAD T1 , TBZ00_CUSTOMER T2 , TBA01_ORD_LINE   T3   , TBE02_ORD_PRC T4
	         WHERE   T1.CUST_CD    =   T2.CUST_CD(+) 
             AND     T1.ORD_NO     =   T4.ORD_NO 
             AND     T1.ORD_SEQ    =   T4.ORD_SEQ
             AND     T1.ORD_NO     =   T3.ORD_NO
             AND     T1.ORD_NO     =   T3.ORD_NO
	         AND     T1.ORD_NO     =   ? 
		     AND     T1.ORD_SEQ    =   ? 
	</query>
	<query id="UIB015020_05.SELECT" desc="">
SELECT  B.CHEM_CD, A.* FROM
      (SELECT    T.CHEM_CD CD,
      MAX(CASE WHEN T.DSN_KIND = '1' THEN T.CHEM_MIN END)    MIN1,
      MAX(CASE WHEN T.DSN_KIND = '1' THEN T.CHEM_MAX END)    MAX1,
      MAX(CASE WHEN T.DSN_KIND = '2' THEN T.CHEM_MIN END)    MIN2,
      MAX(CASE WHEN T.DSN_KIND = '2' THEN T.CHEM_MAX END)    MAX2,
      MAX(CASE WHEN T.DSN_KIND = '2' THEN T.CHEM_DEC_CD END) CHEM_DEC_CD2,      
      MAX(CASE WHEN T.DSN_KIND = '3' THEN T.CHEM_AIM END)    AIM3,
      MAX(CASE WHEN T.DSN_KIND = '3' THEN T.CHEM_MIN END)    MIN3,
      MAX(CASE WHEN T.DSN_KIND = '3' THEN T.CHEM_MAX END)    MAX3,
      MAX(CASE WHEN T.DSN_KIND = '4' THEN T.CHEM_MIN END)    MIN4,         --冷轧标准要求
      MAX(CASE WHEN T.DSN_KIND = '4' THEN T.CHEM_MAX END)    MAX4,         --冷轧标准要求
      MAX(CASE WHEN T.DSN_KIND = '4' THEN T.CHEM_DEC_CD END) CHEM_DEC_CD4, --冷轧标准要求
      MAX(CASE WHEN T.DSN_KIND = '5' THEN T.CHEM_MIN END)    MIN5,         --冷轧特殊要求
      MAX(CASE WHEN T.DSN_KIND = '5' THEN T.CHEM_MAX END)    MAX5          --冷轧特殊要求
      FROM        TBB01_DSN_INGR  T
      WHERE       T.ORD_NO        =   ?  
      AND         T.ORD_SEQ       =   ?
      GROUP    BY  T.CHEM_CD)  A 
     ,TBB01_SEQ_INGR           B
WHERE    A.CD(+)      =    B.CHEM_CD 
AND      B.COMP_YN    =    'N'
ORDER    BY  B.COMP_YN ASC , B.DISPLAY_SEQ ASC 
	</query>
	<query id="UIB015020_06.SELECT" desc="">
SELECT  B.CHEM_CD, A.* FROM
      (SELECT    T.CHEM_CD CD,
      MAX(CASE WHEN T.DSN_KIND = '1' THEN T.CHEM_MIN END)    MIN1,
      MAX(CASE WHEN T.DSN_KIND = '1' THEN T.CHEM_MAX END)    MAX1,
      MAX(CASE WHEN T.DSN_KIND = '2' THEN T.CHEM_MIN END)    MIN2,
      MAX(CASE WHEN T.DSN_KIND = '2' THEN T.CHEM_MAX END)    MAX2,
      MAX(CASE WHEN T.DSN_KIND = '2' THEN T.CHEM_DEC_CD END) CHEM_DEC_CD2,      
      MAX(CASE WHEN T.DSN_KIND = '3' THEN T.CHEM_AIM END)    AIM3,
      MAX(CASE WHEN T.DSN_KIND = '3' THEN T.CHEM_MIN END)    MIN3,
      MAX(CASE WHEN T.DSN_KIND = '3' THEN T.CHEM_MAX END)    MAX3,
      MAX(CASE WHEN T.DSN_KIND = '4' THEN T.CHEM_MIN END)    MIN4,         --冷轧标准要求
      MAX(CASE WHEN T.DSN_KIND = '4' THEN T.CHEM_MAX END)    MAX4,         --冷轧标准要求
      MAX(CASE WHEN T.DSN_KIND = '4' THEN T.CHEM_DEC_CD END) CHEM_DEC_CD4, --冷轧标准要求
      MAX(CASE WHEN T.DSN_KIND = '5' THEN T.CHEM_MIN END)    MIN5,         --冷轧特殊要求
      MAX(CASE WHEN T.DSN_KIND = '5' THEN T.CHEM_MAX END)    MAX5          --冷轧特殊要求
      FROM        TBB01_ORD_INGR  T
      WHERE       T.ORD_NO        =   ?  
      AND         T.ORD_SEQ       =   ?
      GROUP    BY  T.CHEM_CD)  A 
     ,TBB01_SEQ_INGR           B
WHERE    A.CD(+)      =    B.CHEM_CD 
AND      B.COMP_YN    =    'N'
ORDER    BY  B.COMP_YN ASC , B.DISPLAY_SEQ ASC 
	</query>
	<query id="UIB015020_07.SELECT" desc="">
			SELECT DISTINCT FAC_STL_GRD FROM TBB01_FAC_INGR
	</query>
	<query id="UIB015020_08.SELECT" desc="">
	  SELECT    A.* 
	  FROM  	TBB01_FAC_INGR A JOIN TBB01_SEQ_INGR B 
	  ON        A.CHEM_CD     =   B.CHEM_CD 
      WHERE     B.COMP_YN     =   'N' 
	  AND       A.FAC_STL_GRD =   ?
	</query>
	
	<query id="UIB015020_15.SELECT" desc="">
		select * from TBB01_DSN_INGR 
		where ord_no = ? and ord_seq = ? and dsn_kind = ? and chem_cd = ? 
	</query>
	
	<query id="UIB015020_18.SELECT" desc="">
		select * from TBB01_ORD_INGR 
		where ord_no = ? and ord_seq = ? and dsn_kind = ? and chem_cd = ? 
	</query>
	
	<query id="UIB015020_21.SELECT" desc="订单进程信息查询（除品质设计待机以外的）">
		SELECT  DISTINCT  ORD_NO , ORD_SEQ  FROM   TBE02_ORD_PRC  WHERE   ORD_PROG_CD   =   ?  ORDER  BY  ORD_NO , ORD_SEQ
	</query>	
	
	<query id="UIB015020_22.SELECT" desc="订单进程代码">
		select SM_CD,      --订单进程代码
		SM_CFNM            --订单进程名称
		from tbz00_commcd 
		where lg_cd = 'E01001' AND SM_CD NOT IN('C') order by cd_seq
	</query>
	
	<query id="UIB015020_23.SELECT" desc="品质设计待机订单查询">
		SELECT DISTINCT ORD_NO , ORD_SEQ FROM TBB01_DSN_HEAD ORDER BY  ORD_NO , ORD_SEQ
	</query>	
	
	<query id="UIB015020_24.SELECT">
		SELECT T.ORD_NO , T.ORD_SEQ FROM TBB01_ORD_HEAD T WHERE T.ORD_NO  LIKE ?||'%'  ORDER BY T.ORD_NO  , T.ORD_SEQ
	</query>
		
	<query id="UIB015020_25.SELECT" desc="判断是否有特殊要求">
		SELECT PKG_QUALITY_COMM.FIB010201(? ,?) INGR , PKG_QUALITY_COMM.FIB010202(? ,?) QLTY , PKG_QUALITY_COMM.FIB010203(? ,?) DEL FROM DUAL
	</query>
</queryMap>