XinYu_RZMES/.svn/pristine/2d/2d8572c22e62ec97964d66858a7c4c1f4ce6e45d.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="设计对象订单选定标准">
	<query id="slabDnEtstdh.select" desc="查询条件" fetchSize="10">
        <![CDATA[
            SELECT * FROM TBD00_SLABDN_ETSTDH WHERE ETSTDH_SEQ=(SELECT MAX(ETSTDH_SEQ)FROM TBD00_SLABDN_ETSTDH)
        ]]>
    </query>


	<query id="sltedStlGrdHeader.select" desc="查询条件" fetchSize="10">
        <![CDATA[

SELECT DECODE(B.RNUM,NULL,1,B.ETSTDH_SEQ) AS ETSTDH_SEQ
     , B.DEVLMT_FR, B.DEVLMT_TO, B.DEVLMT_ALL_YN
     , B.PLAN_WGT_FR, B.PLAN_WGT_TO,B.PLAN_WGT_ALL_YN, B.COIL_WTH_GAP
     , B.REG_DATETIME,B.REGED_BY_CD, B.CHG_DATETIME, B.CHGED_BY_CD
FROM TBZ00_COPY A
   ,(SELECT ROWNUM AS RNUM, X.*
     FROM TBD00_SLABDN_ETSTDH X
     WHERE ROWNUM = 1) B
WHERE A.RNUM = B.RNUM(+)
AND A.RNUM = 1
        ]]>
    </query>


	<query id="sltedStlGrdDetail.select" desc="查询条件" fetchSize="10">
        <![CDATA[
			SELECT  X.*
			, DECODE(Y.SLTED_STL_GRP, NULL, 'false', 'true') AS CHK 
			FROM (SELECT ROW_NUMBER() OVER (ORDER BY FAC_STL_GRP) AS RN
				 , FAC_STL_GRP AS SLTED_STL_GRP
				 , FAC_STL_GRPNM AS SM_CFNM
			FROM TBD00_STLGRD_GRP) X
			,(SELECT SLTED_STL_GRP
			  FROM TBD00_SLABDN_ETSTDD
			  WHERE CDT_TYPE = 'L' AND ETSTDH_SEQ = ?) Y
			WHERE X.SLTED_STL_GRP = Y.SLTED_STL_GRP(+)
        ]]>
    </query>
	<query id="orderByCoilWth.select" desc="查询条件" fetchSize="10">
        <![CDATA[
			SELECT A.NUM_MIN AS F_VAL,
       A.NUM_MAX AS T_VAL,
       DECODE(SIGN(B.REC_CNT), 1, 'true', 'false') AS CHK2,
       B.REC_CNT,
       B.ORD_CNT,
       B.ORD_WGT,
       B.F_DATE,
       B.T_DATE,
       B.MIN_WGT,
       B.MAX_WGT
  FROM /*(SELECT :1 AS THK, :2 AS DUMMY_C1 FROM DUAL) C
               , */ (SELECT *
        FROM TBD00_MATDN_STD
       WHERE BIZ_TP = 'CoilWidthGroup'
         AND USE_YN = 'Y'
       ORDER BY CD_SEQ) A,
       (SELECT X.WTH_GRP,
               COUNT(DISTINCT X.ORD_NO || X.ORD_SEQ) AS REC_CNT,
               SUM(X.ORD_CNT) AS ORD_CNT,
               SUM(X.ORD_REM_WGT) AS ORD_WGT,
               MIN(X.DEL_TO_DATE) AS F_DATE,
               MAX(X.DEL_TO_DATE) AS T_DATE,
               MIN(X.ORD_REM_WGT) AS MIN_WGT,
               MAX(X.ORD_REM_WGT) AS MAX_WGT
          FROM (SELECT X.*,
                       Y.FAC_STL_GRP,
                       CASE
                         WHEN (X.ORD_WTH < 700) THEN
                          'W00'
                         WHEN (700 <= X.ORD_WTH AND X.ORD_WTH < 900) THEN
                          'W01'
                         WHEN (900 <= X.ORD_WTH AND X.ORD_WTH < 1000) THEN
                          'W02'
                         WHEN (1000 <= X.ORD_WTH AND X.ORD_WTH < 1100) THEN
                          'W03'
                         WHEN (1100 <= X.ORD_WTH AND X.ORD_WTH < 1200) THEN
                          'W04'
                         WHEN (1200 <= X.ORD_WTH AND X.ORD_WTH < 1300) THEN
                          'W05'
                         WHEN (1300 <= X.ORD_WTH AND X.ORD_WTH <= 1430) THEN
                          'W06'
                         ELSE
                          'W99'
                       END WTH_GRP
                  FROM TBE02_ORD_PRC X, TBD00_STLGRD Y
                 WHERE X.STL_GRD = Y.FAC_STL_GRD(+)
                   AND X.ORD_PROG_CD IN ('E', 'D')
                   AND X.PROD_LINE||'&' LIKE :1 || '%'
                   AND X.DEL_TO_DATE BETWEEN :2 AND :3
                   AND X.ORD_REM_WGT BETWEEN :4 AND :5) X
         GROUP BY X.WTH_GRP) B
 WHERE A.MATDN_STD_NM = B.WTH_GRP(+)
        ]]>
	</query>
	<query id="slabDnEtstdh.update" desc="板坏设计结果更新">
        <![CDATA[
            UPDATE TBD00_SLABDN_ETSTDH
            set 
			DEVLMT_FR=?,        /***   交货期条件<开始日>      ***/
			DEVLMT_TO=?,        /***   交货期条件<结束日>      ***/
			DEVLMT_ALL_YN=?,    /***   交货期条件<是否全体选择> ***/
			PLAN_WGT_FR=?,      /***   待设计量<开始>         ***/
			PLAN_WGT_TO=?,      /***   待设计量<结束>         ***/
			PLAN_WGT_ALL_YN=?,  /***   待设计量<是否全部选择>  ***/
			COIL_WTH_GAP=?,     /***   钢卷宽度间隔条件        ***/
			CHG_DATETIME=?,     /***   修改时间<Timestamp>   ***/
			CHGED_BY_CD=?       /***   修改人                ***/
            where ETSTDH_SEQ=?  /***   选定标准序号           ***/
        ]]>
    </query>
	<query id="slabDnEtstdh.insert" desc="板坏设计结果更新">
        <![CDATA[
            INSERT INTO TBD00_SLABDN_ETSTDH( 
			DEVLMT_FR,        /***   交货期条件<开始日>      ***/
			DEVLMT_TO,        /***   交货期条件<结束日>      ***/
			DEVLMT_ALL_YN,    /***   交货期条件<是否全体选择> ***/
			PLAN_WGT_FR,      /***   待设计量<开始>         ***/
			PLAN_WGT_TO,      /***   待设计量<结束>         ***/
			PLAN_WGT_ALL_YN,  /***   待设计量<是否全部选择>  ***/
			COIL_WTH_GAP,     /***   钢卷宽度间隔条件        ***/
			REG_DATETIME,     /***   录入时间<Timestamp>   ***/
			REGED_BY_CD,      /***   录入人                ***/
			CHG_DATETIME,     /***   修改时间<Timestamp>   ***/
			CHGED_BY_CD,      /***   修改人                ***/
            ETSTDH_SEQ)       /***   选定标准序号           ***/
			VALUES(?,?,?,?,?,?,?,?,?,?,?,?)     
        ]]>
    </query>
	<query id="slabDnEtstdd.delete" desc="板坏设计结果更新">
        <![CDATA[
           DELETE FROM TBD00_SLABDN_ETSTDD WHERE CDT_TYPE='L'
        ]]>
    </query>
	<query id="slabDnEtstdd.insert" desc="板坏设计结果更新">
        <![CDATA[
			INSERT INTO TBD00_SLABDN_ETSTDD( 
			CDT_TYPE,
			SCR_LINE_NO,
			ETSTDH_SEQ,
			SLTED_STL_GRP,
			SLTED_WTH_FR,
			SLTED_WTH_TO
			)VALUES(?,?,?,?,?,?)     
        ]]>
    </query>
	<query id="slabDnOrder.insert2" desc="板坏设计结果更新">
        <![CDATA[
INSERT INTO TBD01_SLABDN_OBJ( ORD_NO, ORD_SEQ, NEED_WGT, DSN_PROC_STAT, DSN_PROC_YN
                            , HCR_CLF
                            , REG_ID, REG_DTIME
                            , MOD_ID, MOD_DTIME )
SELECT A.ORD_NO, A.ORD_SEQ, A.ORD_REM_WGT, '1', 'N'
     , NVL(B.SMS_HCR_DEF,'H')
     , NVL(:1,'UID010010'), SYSTIMESTAMP
     , NVL(:2,'UID010010'), SYSTIMESTAMP
FROM TBE02_ORD_PRC A, TBB01_ORD_PROD B
WHERE A.ORD_NO = B.ORD_NO(+)
AND A.ORD_SEQ = B.ORD_SEQ(+)
AND A.ORD_PROG_CD IN ('E','D')
AND :3 <= A.ORD_WTH AND A.ORD_WTH < NVL(:4,'9999')
AND A.DEL_TO_DATE BETWEEN :5 AND :6
AND A.ORD_REM_WGT BETWEEN :7 AND :8
        ]]>
    </query>
	
	<query id="slabDnOrder.insert" desc="板坏设计结果更新">
        <![CDATA[
INSERT INTO TBD01_SLABDN_OBJ( ORD_NO, ORD_SEQ, NEED_WGT, DSN_PROC_STAT, DSN_PROC_YN
                            , HCR_CLF
                            , REG_ID, REG_DTIME
                            , MOD_ID, MOD_DTIME )
SELECT A.ORD_NO, A.ORD_SEQ, A.ORD_REM_WGT, '1', 'N'
     , NVL(B.SMS_HCR_DEF,'H')
     , NVL(:1,'UID010010'), SYSTIMESTAMP
     , NVL(:2,'UID010010'), SYSTIMESTAMP
FROM TBE02_ORD_PRC A, TBB01_ORD_PROD B
WHERE A.ORD_NO = B.ORD_NO(+)
AND A.ORD_SEQ = B.ORD_SEQ(+)
AND A.ORD_PROG_CD IN ('E','D')
AND :3 <= A.ORD_WTH AND A.ORD_WTH < NVL(:4,'9999')
AND A.DEL_TO_DATE BETWEEN :5 AND :6
AND A.ORD_REM_WGT BETWEEN :7 AND :8
AND	NOT EXISTS (SELECT 'X' FROM TBD01_SLABDN_OBJ X WHERE X.ORD_NO = A.ORD_NO AND X.ORD_SEQ = A.ORD_SEQ)	

        ]]>
    </query>
	
    <query id="UID010010.SaveOrderDnObj.Delete1" desc="">
        <![CDATA[
DELETE FROM TBD01_SLABDN_OBJ WHERE 1 = 1
        ]]>
    </query>


    <query id="UID010010.SaveOrderDnObj.Delete2" desc="">
        <![CDATA[
DELETE FROM TBD01_SLABDN_RST WHERE 1 = 1
        ]]>
    </query>


    <query id="UID010010.SaveOrderDnObj.Delete3" desc="">
        <![CDATA[
DELETE FROM TBD01_MATDN_RST WHERE 1 = 1
        ]]>
    </query>


	<query id="UID010010.SaveOrderDnObj.Delete4" desc="">
        <![CDATA[
DELETE FROM TBD01_MATDN_RST_BACKUP
WHERE EXISTS (SELECT 'X'
              FROM TBZ00_COMMCD
              WHERE LG_CD = 'D01008' AND SM_CD = 'Y' AND USED_YN = 'Y' AND CD_SEQ = 1 AND ROWNUM = 1)
        ]]>
    </query>
    <query id="UID010010.SaveOrderDnObj.Delete5" desc="">
        <![CDATA[
			DELETE FROM TBD01_SLABDN_OBJ A WHERE A.ORD_NO||A.ORD_SEQ NOT IN 
			((SELECT DISTINCT B.ORD_NO||B.ORD_SEQ FROM TBD01_SLABDN_RST B) 
			UNION  (SELECT DISTINCT C.ORD_NO||C.ORD_SEQ FROM TBD01_MATDN_RST C) )
        ]]>
    </query>

</queryMap>