XinYu_RZMES/.svn/pristine/0b/0b98643ce86c76560b19beaadbbc7ccbde769995.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="UID020020">
	<query id="UID020020.SrcSlabDnRst.Select" desc="SearchSrcSlabDnRst">
        <![CDATA[
		/*	SELECT LEVEL
				 , A.DSN_SLAB_NO, A.P_DSN_SLAB_NO
				 , A.ORD_NO, A.ORD_SEQ
				 , A.SLAB_THK, A.SLAB_WTH, A.SLAB_LEN, A.SLAB_WGT
			     , A.SLAB_TRMTH_TP
				 , A.FAC_STL_GRD, A.FAC_STL_GRP
				 , A.COIL_THK, A.COIL_WTH, A.COIL_LEN, A.COIL_WGT
			     , A.PROC_PATH
				 , A.HCR_YN, A.EMGMTR_YN, A.COMB_YN
				 , B.ORD_DEVLMT_DATE 
			     , B.SCARFING_PROC_YN
			     , B.SPM_PROC_YN
				 , 'N' AS CHK
			     , A.GRAVITY
			     , A.COIL_INDIA
			     , A.COIL_OUTDIA
				 , A.AUTODSN_YN
				 , A.KEEPWARM_YN
				 , A.PROD_LINE
				 , A.C_COIL_THK
				 , A.C_COIL_WTH
			FROM TBD01_SLABDN_RST A
			   ,(SELECT *
			     FROM TBA01_ORD_LINE
			     WHERE ORD_DEVLMT_DATE BETWEEN NVL(:1,'00000000') AND NVL(:2,'99991231')) B
			    
			WHERE 1 = 1
			AND A.ORD_NO = B.ORD_NO(+)
			AND A.ORD_SEQ = B.ORD_SEQ(+)
			AND A.FAC_STL_GRP LIKE :3||'%'
			AND A.FAC_STL_GRD LIKE :4||'%'
			AND A.SLAB_WTH BETWEEN NVL(:5,0) AND NVL(:6,999999)
			AND A.COIL_THK BETWEEN NVL(:7,0) AND NVL(:8,999999)
			AND A.SLAB_LEN BETWEEN NVL(:9,0) AND NVL(:10,999999)
			START WITH A.P_DSN_SLAB_NO IS NULL
			CONNECT BY PRIOR A.DSN_SLAB_NO = A.P_DSN_SLAB_NO
			ORDER SIBLINGS BY A.COIL_WTH DESC, A.COIL_THK DESC*/
		
			
			select level, n.*
  from (select distinct k.*
          from (SELECT A.DSN_SLAB_NO,
                       A.P_DSN_SLAB_NO,
                       A.ORD_NO,
                       A.ORD_SEQ,
                       A.SLAB_THK,
                       A.SLAB_WTH,
                       A.SLAB_LEN,
                       A.SLAB_WGT,
                       A.SLAB_TRMTH_TP,
                       A.FAC_STL_GRD,
                       A.FAC_STL_GRP,
                       A.COIL_THK,
                       A.COIL_WTH,
                       A.COIL_LEN,
                       A.COIL_WGT,
                       A.PROC_PATH,
                       A.HCR_YN,
                       A.EMGMTR_YN,
                       A.COMB_YN,
                       B.ORD_DEVLMT_DATE,
                       B.SCARFING_PROC_YN,
                       B.SPM_PROC_YN,
                       'N' AS CHK,
                       A.GRAVITY,
                       A.COIL_INDIA,
                       A.COIL_OUTDIA,
                       A.AUTODSN_YN,
                       A.KEEPWARM_YN,
                       A.PROD_LINE,
                       A.C_COIL_THK,
                       A.C_COIL_WTH
                  FROM TBD01_SLABDN_RST A,
                       TBA01_ORD_LINE B,
                       (SELECT A.DSN_SLAB_NO,
                               A.P_DSN_SLAB_NO,
                               A.ORD_NO,
                               A.ORD_SEQ,
                               A.SLAB_THK,
                               A.SLAB_WTH,
                               A.SLAB_LEN,
                               A.SLAB_WGT,
                               A.SLAB_TRMTH_TP,
                               A.FAC_STL_GRD,
                               A.FAC_STL_GRP,
                               A.COIL_THK,
                               A.COIL_WTH,
                               A.COIL_LEN,
                               A.COIL_WGT,
                               A.PROC_PATH,
                               A.HCR_YN,
                               A.EMGMTR_YN,
                               A.COMB_YN,
                               B.ORD_DEVLMT_DATE,
                               B.SCARFING_PROC_YN,
                               B.SPM_PROC_YN,
                               'N' AS CHK,
                               A.GRAVITY,
                               A.COIL_INDIA,
                               A.COIL_OUTDIA,
                               A.AUTODSN_YN,
                               A.KEEPWARM_YN,
                               A.PROD_LINE,
                               A.C_COIL_THK,
                               A.C_COIL_WTH
                          FROM TBD01_SLABDN_RST A
                                /*,(SELECT *
			     FROM TBA01_ORD_LINE
			     WHERE ORD_DEVLMT_DATE BETWEEN NVL(:1,'00000000') AND NVL(:2,'99991231')) B*/
			     , TBA01_ORD_LINE B
			WHERE 1 = 1
			AND A.ORD_NO = B.ORD_NO(+)
			AND A.ORD_SEQ = B.ORD_SEQ(+)
			AND NVL(A.FAC_STL_GRP,1) LIKE :3||'%'
			AND A.FAC_STL_GRD LIKE :4||'%'
			AND A.SLAB_WTH BETWEEN NVL(:5,0) AND NVL(:6,999999)
			AND A.COIL_THK BETWEEN NVL(:7,0) AND NVL(:8,999999)
			AND A.SLAB_LEN BETWEEN NVL(:9,0) AND NVL(:10,999999)) m
                 WHERE 1 = 1
                   AND A.ORD_NO = B.ORD_NO(+)
                   AND A.ORD_SEQ = B.ORD_SEQ(+)
                   AND a.P_DSN_SLAB_NO = m.DSN_SLAB_NO
                union all
                SELECT A.DSN_SLAB_NO,
                       A.P_DSN_SLAB_NO,
                       A.ORD_NO,
                       A.ORD_SEQ,
                       A.SLAB_THK,
                       A.SLAB_WTH,
                       A.SLAB_LEN,
                       A.SLAB_WGT,
                       A.SLAB_TRMTH_TP,
                       A.FAC_STL_GRD,
                       A.FAC_STL_GRP,
                       A.COIL_THK,
                       A.COIL_WTH,
                       A.COIL_LEN,
                       A.COIL_WGT,
                       A.PROC_PATH,
                       A.HCR_YN,
                       A.EMGMTR_YN,
                       A.COMB_YN,
                       B.ORD_DEVLMT_DATE,
                       B.SCARFING_PROC_YN,
                       B.SPM_PROC_YN,
                       'N' AS CHK,
                       A.GRAVITY,
                       A.COIL_INDIA,
                       A.COIL_OUTDIA,
                       A.AUTODSN_YN,
                       A.KEEPWARM_YN,
                       A.PROD_LINE,
                       A.C_COIL_THK,
                       A.C_COIL_WTH
                  FROM TBD01_SLABDN_RST A
                      /* (SELECT *
                          FROM TBA01_ORD_LINE
                         WHERE ORD_DEVLMT_DATE BETWEEN NVL(:11, '00000000') AND
                               NVL(:12, '99991231')) B*/
                                , TBA01_ORD_LINE B
                 WHERE 1 = 1
                   AND A.ORD_NO = B.ORD_NO(+)
                   AND A.ORD_SEQ = B.ORD_SEQ(+)
                   AND NVL(A.FAC_STL_GRP,1) LIKE :13 || '%'
                   AND A.FAC_STL_GRD LIKE :14 || '%'
                   AND A.SLAB_WTH BETWEEN NVL(:15, 0) AND NVL(:16, 999999)
                   AND A.COIL_THK BETWEEN NVL(:17, 0) AND NVL(:18, 999999)
                   AND A.SLAB_LEN BETWEEN NVL(:19, 0) AND NVL(:20, 999999)) k) n

 START WITH n.P_DSN_SLAB_NO IS NULL
CONNECT BY PRIOR n.DSN_SLAB_NO = n.P_DSN_SLAB_NO
 ORDER SIBLINGS BY n.COIL_WTH DESC, n.COIL_THK DESC
        ]]>
    </query>
	
	<query id="UID020020.SrcSlabDnRstChem.Select" desc="SearchSrcSlabDnRst">
        <![CDATA[
		SELECT T.CHEM_CD ||'('||NVL(TO_CHAR(T.CHEM_MIN, '0.0000'), '0')||'-'||
		   TO_CHAR(T.CHEM_MAX, '0.0000') || ')' CHEM
		  FROM TBB01_ORD_INGR T , TBB01_ORD_HEAD T1
		  WHERE T.ORD_NO = :1
	        AND T.ORD_SEQ = :2
	        AND T.ORD_NO  = T1.ORD_NO
	        AND T.ORD_SEQ = T1.ORD_SEQ
            AND T.DSN_KIND = 1
	    ]]>
    </query>
	
	<query id="UID020020.AllMatDnRst.Select" desc="SearchAllMatDnRst">
        <![CDATA[
			SELECT LEVEL
				 , X.*
                 ,'N' AS CHK
			FROM TBD01_MATDN_RST X
			START WITH X.MAT_TP = :1
			CONNECT BY PRIOR X.MAT_SPEC_SEQ = X.PMAT_SPEC_SEQ
			ORDER SIBLINGS BY X.WITHIN_SUB_SEQ 
        ]]>
    </query>

	<query id="charge_slabdn_rst.insert" desc="板坏设计结果插入">
        <![CDATA[
            INSERT INTO 
			TBD01_SLABDN_RST(
			DSN_SLAB_NO,
            P_DSN_SLAB_NO,
			ORD_NO,
			ORD_SEQ,
			SLAB_THK,
			SLAB_WTH,
			SLAB_LEN,
			SLAB_WGT,
			SLAB_TRMTH_TP,
			GRAVITY,
			AUTODSN_YN,
			COIL_PACK_WGT_MIN,
			COIL_PACK_WGT_MAX,
			FAC_STL_GRD,
			COIL_THK,
			COIL_WTH,
			COIL_LEN,
			COIL_WGT,
			PROC_PATH,
			HCR_YN,
			EMGMTR_YN,
			FAC_STL_GRP,
            COMB_YN,
			COIL_INDIA,
			COIL_OUTDIA
			)
            values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
			]]>
    </query>
    <query id="UID020020.MatDnRst.Type.Select" desc="SearchAllMatDnRst">
        <![CDATA[
            SELECT DECODE(X.MAT_TP, '01', 1
                                  , '02', 2
                                  , '03', 3
                                  , '04', 4) AS LVL
                 , X.*
            FROM TBD01_MATDN_RST X
            WHERE X.MAT_TP = :1
			ORDER BY X.MAT_SPEC_SEQ
        ]]>
    </query>

    <query id="UID020020.MatDnRst.All.Delete" desc="SearchAllMatDnRst">
        <![CDATA[
            DELETE FROM TBD01_MATDN_RST WHERE 1 = 1
        ]]>
    </query>

    <query id="UID020020.SlabDnRst.Rows.Delete" desc="SearchAllMatDnRst">
        <![CDATA[
				DELETE FROM TBD01_SLABDN_RST X
				WHERE EXISTS (SELECT 'X'
							  FROM TBD01_MATDN_RST Y
							  WHERE Y.MAT_TP >= '03'
							  AND Y.DSN_SLAB_NO = X.DSN_SLAB_NO)        
			]]>
    </query>

    <query id="UID020020.MatDnRst.ByType.MaxMatSpecSeq" desc="SearchAllMatDnRst">
        <![CDATA[
            SELECT NVL(MAX(MAT_SPEC_SEQ),0) AS MAX_MAT_SPEC_SEQ
            FROM TBD01_MATDN_RST
            WHERE MAT_TP = NVL(:1,'01')
        ]]>
    </query>

    <query id="UID020020.MatDnRst.Row.Insert" desc="SearchAllMatDnRst">
        <![CDATA[
            INSERT INTO TBD01_MATDN_RST( MAT_SPEC_SEQ, PMAT_SPEC_SEQ
                                       , MAT_TP
                                       , EXP_MAT_NO, WITHIN_SUB_SEQ, CMAT_CNT
                                       , FAC_STL_GRD, FAC_STL_GRP
                                       , MAT_RT
                                       , TREAT_RT_CD
                                       , PATH_PROC_CD, LINE_PROC_CD
                                       , MAT_THK, MAT_WTH, MAT_LEN, MAT_LEN_MIN, MAT_LEN_MAX, MAT_WGT
                                       , CCM_TERM_TIME
                                       , WID_CHG_FL, HCR_YN, EMGMTR_YN
                                       , SMS_DUEDATE
                                       , PRC_LINE
                                       , CCM_PRC_LINE
                                       , PROG_CD
                                       , SLAB_CUT_SEQ
                                       , DSG_RATE
                                       , OVER_ROLL_YN, SAMPLE_CUT, ORD_FL
                                       , ORD_NO, ORD_SEQ, DELV_REQ_DATE
                                       , SPM_PROC_YN, SCARPING_PROC_YN
                                       , SEND_STS
                                       , COMB_YN
                                       , COIL_THK, COIL_WTH, COIL_LEN, COIL_WGT,DSN_SLAB_NO,P_DSN_SLAB_NO 
									   , GRAVITY, AUTODSN_YN, COIL_INDIA, COIL_OUTDIA,KEEPWARM_YN
									   , PROD_LINE, C_COIL_THK, C_COIL_WTH)
            SELECT ?, ?
                 , ?
                 , ?, ?, ?
                 , ?, ?
                 , ?
                 , ?
                 , ?, ?
                 , ?, ?, ?, ?, ?, ?
                 , ?
                 , ?, ?, ?
                 , ?
                 , ?
                 , ?
                 , ?
                 , ?
                 , ?
                 , ?, ?, ?
                 , ?, ?, ?
                 , ?, ?
                 , ?
                 , ?
                 , ?, ?, ?, ?, ?, ?
			     , ?, ?, ?, ?,?
			     , ?, ?, ?
            FROM DUAL
        ]]>
    </query>
	<query id="UID020020.DeleteMatDnRst.Row.Insert" desc="删除炉次浇次信息时板坯的还原处理">
		INSERT INTO TBD01_SLABDN_RST (DSN_SLAB_NO, P_DSN_SLAB_NO, ORD_NO, ORD_SEQ
                            , SLAB_THK, SLAB_WTH, SLAB_LEN, SLAB_WGT
                            , SLAB_TRMTH_TP, GRAVITY, AUTODSN_YN
                            , COIL_PACK_WGT_MIN, COIL_PACK_WGT_MAX
                            , FAC_STL_GRD
                            , COIL_THK, COIL_WTH, COIL_LEN, COIL_WGT
                            , PROC_PATH
                            , HCR_YN, EMGMTR_YN, FAC_STL_GRP, COMB_YN
                            , COIL_INDIA, COIL_OUTDIA,KEEPWARM_YN
                            , PROD_LINE, C_COIL_THK, C_COIL_WTH)
		SELECT A.DSN_SLAB_NO, A.P_DSN_SLAB_NO, A.ORD_NO, A.ORD_SEQ  -- , A.MAT_TP, A.MAT_SPEC_SEQ, A.PMAT_SPEC_SEQ
			 , A.MAT_THK, A.MAT_WTH, A.MAT_LEN, A.MAT_WGT
			 , A.SCARPING_PROC_YN, A.GRAVITY, A.AUTODSN_YN
			 , B.ORD_UNIT_WGT_MIN, B.ORD_UNIT_WGT_MAX
			 , A.FAC_STL_GRD
			 , A.COIL_THK, A.COIL_WTH, A.COIL_LEN, A.COIL_WGT
			 , A.PATH_PROC_CD
			 , A.HCR_YN, A.EMGMTR_YN, A.FAC_STL_GRP, A.COMB_YN
			 , A.COIL_INDIA, A.COIL_OUTDIA, A.KEEPWARM_YN
			 , A.PROD_LINE, A.C_COIL_THK, A.C_COIL_WTH
		FROM TBD01_MATDN_RST A, TBE02_ORD_PRC B
		WHERE A.ORD_NO = B.ORD_NO(+)
		AND A.ORD_SEQ = B.ORD_SEQ(+)
		AND A.MAT_TP||'' IN ('03','04')
		AND A.ORD_FL = '1'
		START WITH A.MAT_SPEC_SEQ = :1
		CONNECT BY PRIOR A.MAT_SPEC_SEQ = A.PMAT_SPEC_SEQ
	</query>
	<query id="UID020020.DeleteMatDnRst.Row.Select" desc="删除炉次浇次信息时炉次浇次删除的SELECT">
		SELECT X.MAT_SPEC_SEQ
		FROM (SELECT ROWNUM AS RN, A.MAT_SPEC_SEQ
			  FROM TBD01_MATDN_RST A
			  START WITH A.MAT_SPEC_SEQ = :1
			  CONNECT BY PRIOR A.MAT_SPEC_SEQ = A.PMAT_SPEC_SEQ
			  ORDER BY 1 DESC) X
	</query>
	<query id="UID020020.DeleteMatDnRst.Row.Delete" desc="删除炉次浇次信息时炉次浇次的删除">
			DELETE FROM TBD01_MATDN_RST WHERE MAT_SPEC_SEQ = :1
	</query>
	<query id="UID020020.UpdateMatDnRst.Row.Select" desc="删除炉次浇次信息时炉次浇次顺序的调整SELECT">
		SELECT *
		FROM (SELECT MAT_SPEC_SEQ, PMAT_SPEC_SEQ, WITHIN_SUB_SEQ
				   , ROW_NUMBER() OVER (PARTITION BY PMAT_SPEC_SEQ ORDER BY WITHIN_SUB_SEQ) AS RNUM
			  FROM TBD01_MATDN_RST) A
		WHERE A.WITHIN_SUB_SEQ != A.RNUM
		START WITH A.PMAT_SPEC_SEQ = :1
		CONNECT BY PRIOR A.MAT_SPEC_SEQ = A.PMAT_SPEC_SEQ
	</query>
	<query id="UID020020.UpdateMatDnRst.Row.Update" desc="删除炉次浇次信息时炉次浇次顺序的调整的UPDATE">
		UPDATE TBD01_MATDN_RST SET WITHIN_SUB_SEQ = :1 WHERE MAT_SPEC_SEQ = :2
	</query>
	
	<query id="UID020020.getseq.select" desc="获取最大序号">
		SELECT  LPAD(NVL(MAX(MAT_SPEC_SEQ),0)+1,8,0)   MATSEQ 
        FROM TBD01_MATDN_RST t
        where t.MAT_SPEC_SEQ like substr(:1, 1, 4) || '%' and length(t.MAT_SPEC_SEQ)=8
	</query>
	
	
	<query id="UID020020.chkseq.select" desc="获取最大序号">
		SELECT  *
        FROM TBD01_MATDN_RST t
        where t.MAT_SPEC_SEQ like substr(:1, 1, 4) || '%' and length(t.MAT_SPEC_SEQ)=8
	</query>
	
	
</queryMap>