XinYu_RZMES/.svn/pristine/95/953d0334f85963fb1d504113f5a478b1ab38a55f.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING PROGRESS    ">
              
   <query id="NIG020640_SLAB_COMM.select" desc="it's selected query  from TBG03_MAINRAW table   " fetchSize="10">
        <![CDATA[
         SELECT SLAB_WAY,HCR_CLF,STL_GRD,SLAB_WTH,SLAB_LEN,ORD_NO||ORD_SEQ ORD_NO,
                PRODNM_CD,SLAB_INGR_DEC_GRD,EXTSHAPE_GRD,CORRECT_RSLT_CD ,NONORD_CAUSE_CD   
           FROM TBG02_SLAB_COMM
          WHERE SLAB_NO = ?
        ]]>
    </query>
    <query id="NIG020640_STD_SEARCH.select" desc="it's selected query  from TBG03_MAINRAW table   " fetchSize="10">
        <![CDATA[
         SELECT YARD_ADDR ,YARD_COND_CLF
          FROM TBG04_YARD_STD_SEARCH a,
         TBG04_STD_SLAB_YARD  b
       WHERE a.YARD_COND_CLF = b.ADDR_SERVICE 
        AND  ? like a.STL_GRD ||'%'
        ]]>
    </query>
    <query id="NIG020640_YARD_MAP.select" desc="it's selected query  from TBG03_MAINRAW table   " fetchSize="10">
        <![CDATA[
        SELECT STORELAY,SLAB_NO 
          FROM TBG04_SLAB_YARD  
         WHERE  YARD_ADDR = ?  
        ]]>
    </query>
    <query id="NIG020640_SLAB_YARD.select" desc="it's selected query  from TBG03_MAINRAW table   " fetchSize="10">
        <![CDATA[
        SELECT
           SLAB_NO,STL_GRD,PRODNM_CD,ORD_NO,SLAB_LEN,SLAB_WTH,YARD_ADDR,STORELAY
         FROM 
         (
           SELECT  SLAB_NO,STL_GRD,PRODNM_CD,ORD_NO,SLAB_LEN,SLAB_WTH 
             FROM  TBG02_SLAB_COMM   
            WHERE  SLAB_NO =     
             (SELECT  SLAB_NO
                FROM 
                (
                  SELECT  a.* 
                     , ROW_NUMBER() OVER (ORDER BY STORELAY DESC) RN
                  FROM    TBG04_SLAB_YARD a
                  WHERE a.YARD_ADDR = ?
                    AND  a.SLAB_NO IS NOT NULL 
                )
                WHERE RN = 1
             )
         ) a,  
         (
            SELECT  YARD_ADDR,STORELAY
              FROM 
            (
              SELECT  a.* 
                    , ROW_NUMBER() OVER (ORDER BY STORELAY DESC) RN
                FROM    TBG04_SLAB_YARD a
               WHERE a.YARD_ADDR = ?
                AND  a.SLAB_NO IS NOT NULL 
            )
            WHERE RN = 1
     
        ) b
        ]]>
    </query>
     <query id="NIG020640_YARD_COMMAND.select" desc="it's selected query  from TBG03_MAINRAW table   " fetchSize="10">
        <![CDATA[
         SELECT MAX(ORD_SEQ) ORD_SEQ  
           FROM TBG04_SLAB_CRANE_ORDER 
        ]]>
    </query>
     <query id="NIG020640_YARD_COMMAND.insert" desc="it's selected query  from TBG03_MAINRAW table   " fetchSize="10">
        <![CDATA[
       INSERT TBG04_SLAB_CRANE_ORDER INTO
       (
          ORD_SEQ
         ,AREA_CD
         ,CR_NO
         ,FROM_ADDR
         ,TO_ADDR
         ,SLAB_CNT
         ,ORD_DTIME
         ,ORD_STAT
       )
       VALUES 
       ( 
          SLAB_CRANE_ORDER_SEQ.NEXTVAL
         ,?
         ,?
         ,?
         ,?
         ,1
         ,to_char(sysdate,'yyyymmddhh24miss')
         ,'1'  
       )
        ]]>
    </query>
    <query id="NIG020640_STLGRD.select" desc="it's selected query  from TBG03_MAINRAW table   " fetchSize="10">
        <![CDATA[
            SELECT YARD_ADDR ,YARD_COND_CLF
              FROM TBG04_YARD_STD_SEARCH a,
                   TBG04_STD_SLAB_YARD  b,
                   TBG02_SLAB_COMM c
             WHERE a.YARD_COND_CLF = b.ADDR_SERVICE 
               AND  c.STL_GRD like a.STL_GRD ||'%'
               AND  YARD_ADDR like 'Z1%'
               AND  c.SLAB_NO = ?
        ]]>
    </query>
	<query id="NIG020640_SMS_YARD.select" desc="   " fetchSize="10">
        <![CDATA[
       SELECT   YARD_ADDR,TOT_SLAB,MAX(SLAB_CNT)  SLAB_CNT
         FROM   
        (
          SELECT a.YARD_ADDR,COUNT(SLAB_NO) TOT_SLAB , SLAB_CNT 
            FROM  TBG04_SLAB_YARD a,
          (
            SELECT YARD_ADDR ,SLAB_CNT
             FROM
             (  
               SELECT YARD_ADDR,MAX( STORELAY ) STORELAY,COUNT(*) SLAB_CNT  
                 FROM TBG04_SLAB_YARD  
                WHERE SUBSTR(SLAB_NO,1,10) = ?
                 AND  YARD_ADDR LIKE ?||'%' 
                 GROUP BY YARD_ADDR
               ORDER BY SLAB_CNT DESC
    
             )
         )  b
         WHERE a.YARD_ADDR = B.YARD_ADDR   
         GROUP BY a.YARD_ADDR,SLAB_CNT
         ORDER BY  SLAB_CNT DESC
     )    
     WHERE  TOT_SLAB < 12
      AND ROWNUM = 1
      GROUP BY YARD_ADDR,TOT_SLAB
        ]]>
    </query>
	<query id="NIG020640_CR_CMD.select" desc="   " fetchSize="10">
        <![CDATA[
            SELECT SEQ_NO 
              FROM TBG04_SLAB_CRANE_ORDER 
             WHERE ORD_STAT     = '1'
			   AND FROM_ADDR    like ?||'%'
               AND TO_ADDR      = ?
               AND ORD_SLAB_NO1 = ? 
        ]]>
    </query>
	<query id="NIG020640_EMPTY_YARD.select" desc="   " fetchSize="10">
        <![CDATA[
        SELECT  YARD_ADDR
         
         FROM 
         (   
             SELECT count(SLAB_NO) SLAB_CNT ,YARD_ADDR
              FROM TBG04_SLAB_YARD 
             WHERE    
               YARD_ADDR LIKE ?||'%'  
              GROUP BY  YARD_ADDR
              ORDER BY YARD_ADDR DESC  
         )
         WHERE SLAB_CNT = 0   
          AND  ROWNUM = 1    
        ]]>
    </query>
	<query id="NIG020640_SAME_GRD.select" desc="   " fetchSize="10">
        <![CDATA[
        SELECT YARD_ADDR,STORELAY 
          FROM   
         (
           SELECT  a.YARD_ADDR
               ,a.STORELAY
               ,a.SLAB_NO
               ,b.STL_GRD
               ,(SELECT CASE WHEN ABS(SLAB_WTH - ?) >  50 THEN 'N' ELSE 'Y'  END FROM DUAL) WTH_COMP 
               ,(SELECT CASE WHEN ABS(SLAB_LEN - ?) > 100 THEN 'N' ELSE 'Y'  END FROM DUAL) LEN_COMP 
                  
           FROM  TBG04_SLAB_YARD a,TBG02_SLAB_COMM b,
           (   
            SELECT  YARD_ADDR,MAX(STORELAY) STORELAY  
              FROM TBG04_SLAB_YARD  
             WHERE   
                        SLAB_NO IS NOT NULL  
                 AND    YARD_ADDR   LIKE ?||'%' 
                 GROUP BY  YARD_ADDR 
              ORDER BY YARD_ADDR DESC   
           ) c  
           WHERE 
                  a.SLAB_NO   = b.SLAB_NO  
             AND  a.YARD_ADDR = c.YARD_ADDR
             AND  a.STORELAY  = c.STORELAY
       )   
       WHERE  STORELAY < 12
         AND  LEN_COMP = 'Y'
         AND  WTH_COMP = 'Y' 
         AND  STL_GRD = ?
         AND  ROWNUM = 1
      
     
        ]]>
    </query>
	<query id="NIG020640_CR_CMD.delete" desc="   " fetchSize="10">
        <![CDATA[
            DELETE  FROM TBG04_SLAB_CRANE_ORDER 
             WHERE SEQ_NO     =  ? 
        ]]>
    </query>
	<query id="NIG020640_CR_CMD.insert" desc="   " fetchSize="10">
        <![CDATA[
           INSERT INTO TBG04_SLAB_CRANE_ORDER 
		   (  
			 SEQ_NO    
			,FROM_ADDR
			,TO_ADDR
			,SLAB_CNT   
			,ORD_STAT   
			,ORD_DTIME  
			,REG_ID    
			,REG_DTIME 
			,ORD_SLAB_NO1  
			
		   )
		   VALUES 
		   ( 
			 SLAB_CRANE_ORDER_SEQ.NEXTVAL
			,?
			,?
			,1
			,'1'
			, TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
			,'NIG020640'
			, TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
			, ? 
		   )					
            
        ]]>
    </query>
	<query id="NIG020640_RHF_L1.insert" desc="   " fetchSize="10">
        <![CDATA[
         INSERT INTO   TBH06_L1_SEND_SLAB_TAKEIN 
          (
            SEQ_NO
           ,MSG_FLAG 
           ,TC_ID
           ,TRANS_DTIME
           ,SLAB_NO
           ,LINE_POSITION
           ,HCR_CLF
           ,YARD_LOC
           ,DEST_POSITION 
         )
         VALUES
         (
           TBH06_L1_SEND_SLAB_TAKEIN_SEQ.NEXTVAL
          ,'N' 
          ,'FML010'
          ,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
          ,?
          ,?
          ,?
          ,?
          ,? 
 )
        ]]>
    </query>
</queryMap>