XinYu_RZMES/.svn/pristine/2e/2eb2c5f08b0bcccfaf9113f2b7d998d3349b22a1.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="">

    <query id="UID010031.SlabDnObj.Select" desc="" fetchSize="10">
        <![CDATA[
SELECT '0' AS CHK_FG1
     , X.ORD_NO, X.ORD_SEQ, X.DSN_PROC_STAT
     , X.ORD_DEVLMT_DATE, X.CMT_DLIV_DEVLMT_DATE,X.DEL_TO_DATE,X.H_ORD_DEVLMT_DATE MILL_DUE_DATE
     , X.PROC_PATH
     , X.SPEC_ABBSYM, X.SPEC_STL_GRD, X.FAC_STL_GRP, X.FAC_STL_GRD
     , X.ORD_WGT, X.NEED_WGT, X.PROD_WGT_MIN, X.PROD_WGT_MAX
     , X.COIL_THK, X.COIL_WTH, X.C_COIL_THK, X.C_COIL_WTH, X.PROD_LINE
     , ROUND((X.COIL_WGT / (X.COIL_WTH * X.COIL_THK * X.GRAVITY * 1000)) * 1000000000, 0) AS COIL_LEN
     , X.COIL_WGT
     , CEIL(X.NEED_WGT / X.COIL_WGT)                                  AS COIL_CNT
     , X.SLAB_THK, X.SLAB_WTH
     , DECODE(X.SLAB_LEN, 0, ROUND((X.SLAB_WGT / (X.SLAB_WTH * X.SLAB_THK * X.GRAVITY * 1000)) * 1000000000,-1)
                           , X.SLAB_LEN                                                                        ) AS SLAB_LEN
     , X.SLAB_WGT
     , CEIL(X.NEED_WGT / X.COIL_WGT)                                  AS SLAB_CNT
     , X.SLAB_WGT * CEIL(X.NEED_WGT / X.COIL_WGT)                     AS TOT_SLAB_WGT
     , NVL(SUM(X.ORD_WGT                                 ) OVER (),0) AS SUM_ORD_WGT
     , NVL(SUM(X.NEED_WGT                                ) OVER (),0) AS SUM_NEED_WGT
     , NVL(SUM(CEIL(X.NEED_WGT / X.COIL_WGT)             ) OVER (),0) AS SUM_SLAB_CNT
     , NVL(SUM(X.SLAB_WGT * CEIL(X.NEED_WGT / X.COIL_WGT)) OVER (),0) AS SUM_TOT_SLAB_WGT
     , Y.SLAB_WGT_SMIN, Y.SLAB_WGT_SMAX
     , Y.SLAB_WGT_LMIN, Y.SLAB_WGT_LMAX
     , X.DSN_PROC_YN, X.DSN_ERR_DESC
     , X.NEED_DSN_WGT_MIN, X.NEED_DSN_WGT_MAX
     , X.ORD_INDIA
     , ROUND(SQRT(4 * X.COIL_WGT / (X.COIL_WTH / 1000000 * X.GRAVITY * 3.14 * 0.98) + (X.ORD_INDIA * X.ORD_INDIA)),0) AS ORD_OUTDIA
     , X.GRAVITY
     , X.EMGMTR_YN, X.SCARFING_PROC_YN, X.GRIND_PROC_YN, X.HCR_CLF
     , X.EDGE_PROC_YN, X.SPM_PROC_YN, X.SHL_PROC_YN, X.SFC_PROC_YN
     , X.BTWSHT_INS_YN, X.KEEPWARM_YN, X.EACHLOT_YN
     , DECODE(SIGN(X.SLAB_LEN - 9800),-1,'Y','N') AS COMB_YN
     , X.PROC_RATE
     , X.ORD_NO||'-'||X.ORD_SEQ                   AS SEARCH_KEY
     , X.RCVORD_TP
     , X.PRODNM_CD
     , X.ORD_USE_TP
	 , X.ORD_REG_DTIME
     , X.DLIV_TP
	 , X.MK_CONTENT
     , X.INGR_YN
	 , X.ORD_PLAN_DATA
			,X.XSQY
			,X.MK_COIL
			,ORD_PRC_LOCK
FROM (SELECT A.ORD_NO, A.ORD_SEQ
           , E.STL_GRD               AS FAC_STL_GRD
           , E.ORD_THK               AS COIL_THK
           , E.ORD_WTH               AS COIL_WTH
           , E.PROD_LINE
           , E.C_ORD_THK               AS C_COIL_THK
           , E.C_ORD_WTH               AS C_COIL_WTH
           , 0                       AS COIL_LEN
           , DECODE(D.SLAB_LEN, 0, B.PROD_WGT_MAX
                                 , CEIL(((D.SLAB_LEN * D.ADD_VALUE * D.SLAB_THK * D.GRAVITY * 1000) / 1000000000) * C.PROC_RATE / 100)) AS COIL_WGT
           , 0                       AS COIL_CNT
           , A.NEED_WGT, A.NEED_WGT AS NEED_DSN_WGT_MIN, A.NEED_WGT AS NEED_DSN_WGT_MAX
           , B.PROD_WGT_MIN
           , B.PROD_WGT_MAX
           , F.PATH_PROC_CD          AS PROC_PATH
           , E.DEL_FR_DATE, B.ORD_DEVLMT_DATE, E.DEL_TO_DATE, B.CMT_DLIV_DEVLMT_DATE,E.H_ORD_DEVLMT_DATE
           , A.DSN_PROC_STAT
           , G.FAC_STL_GRP
           , NVL(B.ORD_INDIA ,0)     AS ORD_INDIA
           , NVL(B.ORD_OUTDIA,0)     AS ORD_OUTDIA
           , A.DSN_PROC_YN, A.DSN_ERR_DESC
           , B.SPEC_ABBSYM, B.SPEC_STL_GRD
           , B.ORD_WGT
           , D.SLAB_THK
           , D.ADD_VALUE AS SLAB_WTH
           , D.SLAB_LEN
           --, DECODE(D.SLAB_LEN, 0, B.PROD_WGT_MAX * (1 + (1 - C.PROC_RATE / 100))
			, DECODE(D.SLAB_LEN, 0, B.PROD_WGT_MAX * 100 / C.PROC_RATE                                          --7月9日修改
                                 , CEIL( (D.SLAB_LEN * D.ADD_VALUE * D.SLAB_THK * D.GRAVITY * 1000) / 1000000000                     )) AS SLAB_WGT
           , D.GRAVITY
           , B.EMGMTR_MNG_YN                        AS EMGMTR_YN
           , B.SCARFING_PROC_YN
           , B.GRIND_PROC_YN
           , A.HCR_CLF
           , B.EDGE_PROC_YN
           , B.SPM_PROC_YN
           , B.SHL_PROC_YN
           , B.SFC_PROC_YN
           , B.BTWSHT_INS_YN
           , E.KEEPWARM_YN  
           , B.EACHLOT_YN
     --      , ROUND(CR.PROC_RATE * e.EDGE_RATE,3)                            AS PROC_RATE
           , PKG_UID.GET_PROC_RATE(A.ORD_NO, A.ORD_SEQ , '')  PROC_RATE
           , H.SM_CFNM                              AS RCVORD_TP
           , XB_PACKAGE.GF_COMNNAME_FIND('A01004', E.PRODNM_CD) PRODNM_CD
           , I.SM_CFNM                              AS ORD_USE_TP
		   , J.ORD_CMT_DTIME                        AS ORD_REG_DTIME
		   , B.DLIV_TP
		   , B.MK_CONTENT
       , DECODE(K.DSN_KIND,'1','Y','N')  AS    INGR_YN
		   , B.ORD_PLAN_DATA
		   , B.XSQY
			,B.MK_COIL
			,ORD_PRC_LOCK
      FROM TBD01_SLABDN_OBJ A, TBA01_ORD_LINE B,TBA01_ORD_COMM J
      ,(SELECT DISTINCT ORD_NO, ORD_SEQ,DSN_KIND FROM TBB01_ORD_INGR WHERE DSN_KIND = '1') K
         ,(SELECT PROC_RATE FROM TBE01_PROC_RATE WHERE PROC_CD = 'R') C
         ,(SELECT t1.PROC_RATE,t2.prd_ty FROM TBE01_PROC_RATE t1
                         ,tbe01_proc_rate_rel t2
                       
            WHERE t1.PROC_CD = t2.proc_cd
            and   t2.cur_proc = 'R') CR
         ,(SELECT MAX(DECODE(X.LG_CD,'D01002',TO_NUMBER(X.SM_CD))) AS SLAB_THK
                , MAX(DECODE(X.LG_CD,'D01006',TO_NUMBER(X.SM_CD))) AS GRAVITY
                , NVL(:1,    0)                                    AS SLAB_LEN
                , :2                                               AS ADD_VALUE
           FROM TBZ00_COMMCD X
           WHERE X.LG_CD IN ('D01002','D01006')
           AND X.CD_SEQ = 1) D
			
         ,(SELECT H.ORD_NO
			,H.ORD_SEQ
			,H.KEEPWARM_YN
			,H.STL_GRD
			,H.DEL_FR_DATE
			,H.DEL_TO_DATE
			,H.H_ORD_DEVLMT_DATE
			,H.PRD_TY PRODNM_CD
			,H.RCVORD_CLF
			,H.ORD_USEAGE_CD
			,H.ORD_THK
			,H.ORD_WTH
			,H.C_ORD_THK
			,H.C_ORD_WTH
			,H.PROD_LINE
			,h.EDGE_RATE
			,H.PRD_TY
			,(SELECT TO_CHAR(wmsys.wm_concat(XB_PACKAGE.GF_COMNNAME_FIND('E04002',COLUMN_VALUE))) 
                 FROM TABLE(XB_PACKAGE.GF_TABSTR(h.ORD_PRC_LOCK,'|'))) ORD_PRC_LOCK
           FROM TBE02_ORD_PRC H
           WHERE ORD_PROG_CD IN ('D','E')
           AND (NVL(:3,'NULL') IN (SELECT *
                 FROM TABLE(XB_PACKAGE.GF_TABSTR(H.ORD_PRC_LOCK,'|'))
                 union all
                 select 'NULL' from dual
                 )
                 or 
                 H.ORD_PRC_LOCK is null
                 )
                 
           AND STL_GRD LIKE '%'||:4||'%') E
			
			
         , TBB01_ORD_PROD F
         , TBD00_STLGRD G
         ,(SELECT SM_CD, SM_CFNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01001') H
         ,(SELECT SM_CD, SM_CFNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01007') I
      WHERE A.ORD_NO = B.ORD_NO(+) AND A.ORD_SEQ = B.ORD_SEQ(+)
      AND E.PRD_TY= CR.PRD_TY
	  AND A.ORD_NO = J.ORD_NO
    AND A.ORD_NO = K.ORD_NO(+)  AND A.ORD_SEQ = K.ORD_SEQ(+)
      AND B.ORD_NO = E.ORD_NO(+) AND B.ORD_SEQ = E.ORD_SEQ(+)
      AND E.ORD_NO = F.ORD_NO    AND E.ORD_SEQ = F.ORD_SEQ
      AND E.STL_GRD = G.FAC_STL_GRD(+)
      AND E.RCVORD_CLF = H.SM_CD(+)
	  AND E.RCVORD_CLF <> 'M'
      AND E.ORD_USEAGE_CD = I.SM_CD(+)
      AND A.DSN_PROC_STAT LIKE NVL(:5,'1')||'%'
      AND A.ORD_NO LIKE :6||'%'
      AND A.ORD_SEQ LIKE :7||'%'
	  AND J.ORD_CMT_DTIME BETWEEN NVL(:8,'00000000') AND NVL(:9,'99999999')
	  AND E.ORD_WTH BETWEEN NVL(:10,0) AND NVL(:11,99999999)
	  AND B.DLIV_TP LIKE NVL(:12,'%')
	  --AND H.SM_CD LIKE NVL(:13,'%')
	  AND ( :13 IS NULL OR E.RCVORD_CLF IN (	
					SELECT COLUMN_VALUE FROM TABLE(CAST(XB_PACKAGE.GF_TABSTR(:14) AS TABSTR_T))))
	  -----------------------------------------------------------------	
	  --MODFIY BY SJW 091029
	  -- ADD THE SELECT STATEMENT OF ORD_PLAN_DATA		
	  AND NVL(B.ORD_PLAN_DATA,B.ORD_DEVLMT_DATE) BETWEEN NVL(:15,'00000000') AND NVL(:16,'99999999')
			--AND E.PRODNM_CD LIKE :16||'%'
	  AND INSTR(NVL(:17 , E.PRODNM_CD) , E.PRODNM_CD) > 0 		
	  AND E.PROD_LINE||'&' LIKE :18||'%') X 
	  -----------------------------------------------------------------
    , TBD00_SLABDN_WGTSTD Y
WHERE X.SLAB_THK = Y.SLAB_THK(+)
AND X.SLAB_WTH = Y.SLAB_WTH(+)
			ORDER BY X.ORD_NO, X.ORD_SEQ
        ]]>
    </query>
	
	
	    <query id="UID010031.SlabDnObj.Select_backup" desc="6月10日修改板坯宽度时的备份" fetchSize="10">
        <![CDATA[
SELECT '0' AS CHK_FG1
     , X.ORD_NO, X.ORD_SEQ, X.DSN_PROC_STAT
     , X.ORD_DEVLMT_DATE, X.CMT_DLIV_DEVLMT_DATE
     , X.PROC_PATH
     , X.SPEC_ABBSYM, X.SPEC_STL_GRD, X.FAC_STL_GRP, X.FAC_STL_GRD
     , X.ORD_WGT, X.NEED_WGT, X.PROD_WGT_MIN, X.PROD_WGT_MAX
     , X.COIL_THK, X.COIL_WTH
     , ROUND((X.COIL_WGT / (X.COIL_WTH * X.COIL_THK * X.GRAVITY * 1000)) * 1000000000, 0) AS COIL_LEN
     , X.COIL_WGT
     , CEIL(X.NEED_WGT / X.COIL_WGT)                                  AS COIL_CNT
     , X.SLAB_THK, X.SLAB_WTH
     , DECODE(X.SLAB_LEN, 0, ROUND((X.SLAB_WGT / (X.SLAB_WTH * X.SLAB_THK * X.GRAVITY * 1000)) * 1000000000,-1)
                           , X.SLAB_LEN                                                                        ) AS SLAB_LEN
     , X.SLAB_WGT
     , CEIL(X.NEED_WGT / X.COIL_WGT)                                  AS SLAB_CNT
     , X.SLAB_WGT * CEIL(X.NEED_WGT / X.COIL_WGT)                     AS TOT_SLAB_WGT
     , NVL(SUM(X.ORD_WGT                                 ) OVER (),0) AS SUM_ORD_WGT
     , NVL(SUM(X.NEED_WGT                                ) OVER (),0) AS SUM_NEED_WGT
     , NVL(SUM(CEIL(X.NEED_WGT / X.COIL_WGT)             ) OVER (),0) AS SUM_SLAB_CNT
     , NVL(SUM(X.SLAB_WGT * CEIL(X.NEED_WGT / X.COIL_WGT)) OVER (),0) AS SUM_TOT_SLAB_WGT
     , Y.SLAB_WGT_SMIN, Y.SLAB_WGT_SMAX
     , Y.SLAB_WGT_LMIN, Y.SLAB_WGT_LMAX
     , X.DSN_PROC_YN, X.DSN_ERR_DESC
     , X.NEED_DSN_WGT_MIN, X.NEED_DSN_WGT_MAX
     , X.ORD_INDIA
     , ROUND(SQRT(4 * X.COIL_WGT / (X.COIL_WTH / 1000000 * X.GRAVITY * 3.14 * 0.98) + (X.ORD_INDIA * X.ORD_INDIA)),0) AS ORD_OUTDIA
     , X.GRAVITY
     , X.EMGMTR_YN, X.SCARFING_PROC_YN, X.GRIND_PROC_YN, X.HCR_CLF
     , X.EDGE_PROC_YN, X.SPM_PROC_YN, X.SHL_PROC_YN, X.SFC_PROC_YN
     , X.BTWSHT_INS_YN, X.KEEPWARM_YN, X.EACHLOT_YN
     , DECODE(SIGN(X.SLAB_LEN - 9800),-1,'Y','N') AS COMB_YN
     , X.PROC_RATE
     , X.ORD_NO||'-'||X.ORD_SEQ                   AS SEARCH_KEY
     , X.RCVORD_TP
     , X.PRODNM_CD
     , X.ORD_USE_TP
	 , X.ORD_REG_DTIME
FROM (SELECT A.ORD_NO, A.ORD_SEQ
           , E.STL_GRD               AS FAC_STL_GRD
           , B.ORD_THK               AS COIL_THK
           , B.ORD_WTH               AS COIL_WTH
           , 0                       AS COIL_LEN
           , DECODE(D.SLAB_LEN, 0, B.PROD_WGT_MAX
                                 , CEIL(((D.SLAB_LEN * (B.ORD_WTH + D.ADD_VALUE) * D.SLAB_THK * D.GRAVITY * 1000) / 1000000000) * C.PROC_RATE / 100)) AS COIL_WGT
           , 0                       AS COIL_CNT
           , A.NEED_WGT, A.NEED_WGT AS NEED_DSN_WGT_MIN, A.NEED_WGT AS NEED_DSN_WGT_MAX
           , B.PROD_WGT_MIN
           , B.PROD_WGT_MAX
           , F.PATH_PROC_CD          AS PROC_PATH
           , E.DEL_FR_DATE, B.ORD_DEVLMT_DATE, E.DEL_TO_DATE, B.CMT_DLIV_DEVLMT_DATE
           , A.DSN_PROC_STAT
           , G.FAC_STL_GRP
           , NVL(B.ORD_INDIA ,0)     AS ORD_INDIA
           , NVL(B.ORD_OUTDIA,0)     AS ORD_OUTDIA
           , A.DSN_PROC_YN, A.DSN_ERR_DESC
           , B.SPEC_ABBSYM, B.SPEC_STL_GRD
           , B.ORD_WGT
           , D.SLAB_THK
           , CASE WHEN (B.ORD_WTH + D.ADD_VALUE > 1550) THEN 1550 ELSE B.ORD_WTH + D.ADD_VALUE END AS SLAB_WTH
           , D.SLAB_LEN
           , DECODE(D.SLAB_LEN, 0, B.PROD_WGT_MAX * (1 + (1 - C.PROC_RATE / 100))
                                 , CEIL( (D.SLAB_LEN * (B.ORD_WTH + D.ADD_VALUE) * D.SLAB_THK * D.GRAVITY * 1000) / 1000000000                     )) AS SLAB_WGT
           , D.GRAVITY
           , B.EMGMTR_MNG_YN                        AS EMGMTR_YN
           , B.SCARFING_PROC_YN
           , B.GRIND_PROC_YN
           , A.HCR_CLF
           , B.EDGE_PROC_YN
           , B.SPM_PROC_YN
           , B.SHL_PROC_YN
           , B.SFC_PROC_YN
           , B.BTWSHT_INS_YN
           , B.KEEPWARM_YN
           , B.EACHLOT_YN
           , C.PROC_RATE                            AS PROC_RATE
           , H.SM_CFNM                              AS RCVORD_TP
           , E.PRODNM_CD
           , I.SM_CFNM                              AS ORD_USE_TP
		   , B.ORD_REG_DTIME
      FROM TBD01_SLABDN_OBJ A, TBA01_ORD_LINE B
         ,(SELECT PROC_RATE FROM TBE01_PROC_RATE WHERE PROC_CD = 'R') C
         ,(SELECT MAX(DECODE(X.LG_CD,'D01002',TO_NUMBER(X.SM_CD))) AS SLAB_THK
                , MAX(DECODE(X.LG_CD,'D01006',TO_NUMBER(X.SM_CD))) AS GRAVITY
                , NVL(:1,    0)                                    AS SLAB_LEN
                , NVL(:2,   50)                                    AS ADD_VALUE
           FROM TBZ00_COMMCD X
           WHERE X.LG_CD IN ('D01002','D01006')
           AND X.CD_SEQ = 1) D
         ,(SELECT *
           FROM TBE02_ORD_PRC
           WHERE ORD_PROG_CD IN ('D','E')
           AND STL_GRD LIKE '%'||:3||'%') E
         , TBB01_ORD_PROD F
         , TBD00_STLGRD G
         ,(SELECT SM_CD, SM_CFNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01001') H
         ,(SELECT SM_CD, SM_CFNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01007') I
      WHERE A.ORD_NO = B.ORD_NO(+) AND A.ORD_SEQ = B.ORD_SEQ(+)
      AND B.ORD_NO = E.ORD_NO(+) AND B.ORD_SEQ = E.ORD_SEQ(+)
      AND E.ORD_NO = F.ORD_NO    AND E.ORD_SEQ = F.ORD_SEQ
      AND E.STL_GRD = G.FAC_STL_GRD(+)
      AND E.RCVORD_CLF = H.SM_CD(+)
      AND E.ORD_USEAGE_CD = I.SM_CD(+)
      AND A.DSN_PROC_STAT LIKE NVL(:4,'1')||'%'
      AND A.ORD_NO LIKE :5||'%'
      AND A.ORD_SEQ LIKE :6||'%'
	  AND B.ORD_REG_DTIME BETWEEN :7 AND :8) X 
    , TBD00_SLABDN_WGTSTD Y
WHERE X.SLAB_THK = Y.SLAB_THK(+)
AND X.SLAB_WTH = Y.SLAB_WTH(+)
        ]]>
    </query>

	<query id="UID010031.SlabDnObj.Select1" desc="" fetchSize="10">
        <![CDATA[
SELECT '0' AS CHK_FG1
     , X.ORD_NO, X.ORD_SEQ, X.DSN_PROC_STAT
     , X.ORD_DEVLMT_DATE, X.CMT_DLIV_DEVLMT_DATE
     , X.PROC_PATH
     , X.SPEC_ABBSYM, X.SPEC_STL_GRD, X.FAC_STL_GRP, X.FAC_STL_GRD
     , X.ORD_WGT, X.NEED_WGT, X.PROD_WGT_MIN, X.PROD_WGT_MAX
     , X.COIL_THK, X.COIL_WTH
     , ROUND((X.COIL_WGT / (X.COIL_WTH * X.COIL_THK * X.GRAVITY * 1000)) * 1000000000,0) AS COIL_LEN
     , X.COIL_WGT
     , CEIL(X.NEED_WGT / X.COIL_WGT)                                  AS COIL_CNT
     , X.SLAB_THK, X.SLAB_WTH, X.SLAB_LEN, X.SLAB_WGT
     , CEIL(X.NEED_WGT / X.COIL_WGT)                                  AS SLAB_CNT
     , X.SLAB_WGT * CEIL(X.NEED_WGT / X.COIL_WGT)                     AS TOT_SLAB_WGT
     , NVL(SUM(X.ORD_WGT                                 ) OVER (),0) AS SUM_ORD_WGT
     , NVL(SUM(X.NEED_WGT                                ) OVER (),0) AS SUM_NEED_WGT
     , NVL(SUM(CEIL(X.NEED_WGT / X.COIL_WGT)             ) OVER (),0) AS SUM_SLAB_CNT
     , NVL(SUM(X.SLAB_WGT * CEIL(X.NEED_WGT / X.COIL_WGT)) OVER (),0) AS SUM_TOT_SLAB_WGT
     , Y.SLAB_WGT_SMIN, Y.SLAB_WGT_SMAX
     , Y.SLAB_WGT_LMIN, Y.SLAB_WGT_LMAX
     , X.DSN_PROC_YN, X.DSN_ERR_DESC
     , X.NEED_DSN_WGT_MIN, X.NEED_DSN_WGT_MAX
     , X.ORD_INDIA
     , ROUND(SQRT(4 * X.COIL_WGT / (X.COIL_WTH / 1000000 * X.GRAVITY * 3.14 * 0.98) + (X.ORD_INDIA * X.ORD_INDIA)),0) AS ORD_OUTDIA
     , X.GRAVITY
     , X.EMGMTR_YN, X.SCARFING_PROC_YN, X.GRIND_PROC_YN, X.HCR_CLF
     , X.EDGE_PROC_YN, X.SPM_PROC_YN, X.SHL_PROC_YN, X.SFC_PROC_YN
     , X.BTWSHT_INS_YN, X.KEEPWARM_YN, X.EACHLOT_YN
     , DECODE(SIGN(X.SLAB_LEN - 9800),-1,'Y','N') AS COMB_YN
     , X.PROC_RATE
     , X.ORD_NO||'-'||X.ORD_SEQ                   AS SEARCH_KEY
     , X.RCVORD_TP
     , X.PRODNM_CD
     , X.ORD_USE_TP
FROM (SELECT A.ORD_NO, A.ORD_SEQ
           , E.STL_GRD               AS FAC_STL_GRD
           , B.ORD_THK               AS COIL_THK
           , B.ORD_WTH               AS COIL_WTH
           , 0                       AS COIL_LEN
           , CEIL(((D.SLAB_LEN * (B.ORD_WTH + D.ADD_VALUE) * D.SLAB_THK * D.GRAVITY * 1000) / 1000000000) * C.PROC_RATE / 100) AS COIL_WGT
           , 0                       AS COIL_CNT
           , A.NEED_WGT, A.NEED_WGT AS NEED_DSN_WGT_MIN, A.NEED_WGT AS NEED_DSN_WGT_MAX
           , B.PROD_WGT_MIN
           , B.PROD_WGT_MAX
           , F.PATH_PROC_CD          AS PROC_PATH
           , E.DEL_FR_DATE, B.ORD_DEVLMT_DATE, E.DEL_TO_DATE, B.CMT_DLIV_DEVLMT_DATE
           , A.DSN_PROC_STAT
           , G.FAC_STL_GRP
           , NVL(B.ORD_INDIA ,0)     AS ORD_INDIA
           , NVL(B.ORD_OUTDIA,0)     AS ORD_OUTDIA
           , A.DSN_PROC_YN, A.DSN_ERR_DESC
           , B.SPEC_ABBSYM, B.SPEC_STL_GRD
           , B.ORD_WGT
           , D.SLAB_THK
           , CASE WHEN (B.ORD_WTH + D.ADD_VALUE > 1450) THEN 1450 ELSE B.ORD_WTH + D.ADD_VALUE END AS SLAB_WTH
           , D.SLAB_LEN
           , CEIL( (D.SLAB_LEN * (B.ORD_WTH + D.ADD_VALUE) * D.SLAB_THK * D.GRAVITY * 1000) / 1000000000                     ) AS SLAB_WGT
           , D.GRAVITY
           , B.EMGMTR_MNG_YN                        AS EMGMTR_YN
           , B.SCARFING_PROC_YN
           , B.GRIND_PROC_YN
           , A.HCR_CLF
           , B.EDGE_PROC_YN
           , B.SPM_PROC_YN
           , B.SHL_PROC_YN
           , B.SFC_PROC_YN
           , B.BTWSHT_INS_YN
           , B.KEEPWARM_YN
           , B.EACHLOT_YN
           , C.PROC_RATE                            AS PROC_RATE
           , H.SM_CFNM                              AS RCVORD_TP
           , E.PRODNM_CD
           , I.SM_CFNM                              AS ORD_USE_TP
      FROM TBD01_SLABDN_OBJ A, TBA01_ORD_LINE B
         ,(SELECT PROC_RATE FROM TBE01_PROC_RATE WHERE PROC_CD = 'R') C
         ,(SELECT MAX(DECODE(X.LG_CD,'D01002',TO_NUMBER(X.SM_CD))) AS SLAB_THK
                , MAX(DECODE(X.LG_CD,'D01006',TO_NUMBER(X.SM_CD))) AS GRAVITY
                , NVL(:1,11000)                                    AS SLAB_LEN
                , NVL(:2,   50)                                    AS ADD_VALUE
           FROM TBZ00_COMMCD X
           WHERE X.LG_CD IN ('D01002','D01006')
           AND X.CD_SEQ = 1) D
         ,(SELECT *
           FROM TBE02_ORD_PRC
           WHERE ORD_PROG_CD IN ('D','E')) E
         , TBB01_ORD_PROD F
         , TBD00_STLGRD G
         ,(SELECT SM_CD, SM_CFNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01001') H
         ,(SELECT SM_CD, SM_CFNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01007') I
      WHERE A.ORD_NO = B.ORD_NO(+) AND A.ORD_SEQ = B.ORD_SEQ(+)
      AND B.ORD_NO = E.ORD_NO(+) AND B.ORD_SEQ = E.ORD_SEQ(+)
      AND E.ORD_NO = F.ORD_NO    AND E.ORD_SEQ = F.ORD_SEQ
      AND E.STL_GRD = G.FAC_STL_GRD(+)
      AND E.RCVORD_CLF = H.SM_CD(+)
      AND E.ORD_USEAGE_CD = I.SM_CD(+)
      AND A.DSN_PROC_STAT LIKE NVL(:3,'1')||'%'
      AND A.ORD_NO LIKE :4||'%'
      AND A.ORD_SEQ LIKE :5||'%') X
    , TBD00_SLABDN_WGTSTD Y
WHERE X.SLAB_THK = Y.SLAB_THK(+)
AND X.SLAB_WTH = Y.SLAB_WTH(+)
/************************************************
[1] SLAB_LEN       : 11000        (Default:11000)
[2] ADDED_COIL_WTH : 50           (Default:50   )
[3] DSN_PROC_STAT  :'1'           (Default:'1'  )
[4] ORD_NO         : 412009011202 (Default:NULL )
[5] ORD_SEQ        : 540          (Default:NULL )
*************************************************/
        ]]>
    </query>

    <query id="UID010031.SlabDnObj.Select2" desc="" fetchSize="10">
        <![CDATA[
SELECT '0' AS CHK_FG1
     , X.ORD_NO, X.ORD_SEQ, X.DSN_PROC_STAT
     , X.ORD_DEVLMT_DATE, X.CMT_DLIV_DEVLMT_DATE
     , X.PROC_PATH
     , X.SPEC_ABBSYM, X.SPEC_STL_GRD, X.FAC_STL_GRP, X.FAC_STL_GRD
     , X.ORD_WGT, X.NEED_WGT, X.PROD_WGT_MIN, X.PROD_WGT_MAX
     , X.COIL_THK, X.COIL_WTH
     , ROUND((X.COIL_WGT / (X.COIL_WTH * X.COIL_THK * X.GRAVITY * 1000)) * 1000000000, 0) AS COIL_LEN
     , X.COIL_WGT
     , CEIL(X.NEED_WGT / X.COIL_WGT)                                  AS COIL_CNT
     , X.SLAB_THK, X.SLAB_WTH
     , ROUND((X.SLAB_WGT / (X.SLAB_WTH * X.SLAB_THK * X.GRAVITY * 1000)) * 1000000000,-1) AS SLAB_LEN
     , X.SLAB_WGT
     , CEIL(X.NEED_WGT / X.COIL_WGT)                                  AS SLAB_CNT
     , X.SLAB_WGT * CEIL(X.NEED_WGT / X.COIL_WGT)                     AS TOT_SLAB_WGT
     , NVL(SUM(X.ORD_WGT                                 ) OVER (),0) AS SUM_ORD_WGT
     , NVL(SUM(X.NEED_WGT                                ) OVER (),0) AS SUM_NEED_WGT
     , NVL(SUM(CEIL(X.NEED_WGT / X.COIL_WGT)             ) OVER (),0) AS SUM_SLAB_CNT
     , NVL(SUM(X.SLAB_WGT * CEIL(X.NEED_WGT / X.COIL_WGT)) OVER (),0) AS SUM_TOT_SLAB_WGT
     , Y.SLAB_WGT_SMIN, Y.SLAB_WGT_SMAX
     , Y.SLAB_WGT_LMIN, Y.SLAB_WGT_LMAX
     , X.DSN_PROC_YN, X.DSN_ERR_DESC
     , X.NEED_DSN_WGT_MIN, X.NEED_DSN_WGT_MAX
     , X.ORD_INDIA
     , ROUND(SQRT(4 * X.COIL_WGT / (X.COIL_WTH / 1000000 * X.GRAVITY * 3.14 * 0.98) + (X.ORD_INDIA * X.ORD_INDIA)),0) AS ORD_OUTDIA
     , X.GRAVITY
     , X.EMGMTR_YN, X.SCARFING_PROC_YN, X.GRIND_PROC_YN, X.HCR_CLF
     , X.EDGE_PROC_YN, X.SPM_PROC_YN, X.SHL_PROC_YN, X.SFC_PROC_YN
     , X.BTWSHT_INS_YN, X.KEEPWARM_YN, X.EACHLOT_YN
     , DECODE(SIGN(X.SLAB_LEN - 9800),-1,'Y','N') AS COMB_YN
     , X.PROC_RATE
     , X.ORD_NO||'-'||X.ORD_SEQ                   AS SEARCH_KEY
     , X.RCVORD_TP
     , X.PRODNM_CD
     , X.ORD_USE_TP
FROM (SELECT A.ORD_NO, A.ORD_SEQ
           , E.STL_GRD               AS FAC_STL_GRD
           , B.ORD_THK               AS COIL_THK
           , B.ORD_WTH               AS COIL_WTH
           , 0                       AS COIL_LEN
           , B.PROD_WGT_MAX          AS COIL_WGT
           , 0                       AS COIL_CNT
           , A.NEED_WGT, A.NEED_WGT AS NEED_DSN_WGT_MIN, A.NEED_WGT AS NEED_DSN_WGT_MAX
           , B.PROD_WGT_MIN
           , B.PROD_WGT_MAX
           , F.PATH_PROC_CD          AS PROC_PATH
           , E.DEL_FR_DATE, B.ORD_DEVLMT_DATE, E.DEL_TO_DATE, B.CMT_DLIV_DEVLMT_DATE
           , A.DSN_PROC_STAT
           , G.FAC_STL_GRP
           , NVL(B.ORD_INDIA ,0)     AS ORD_INDIA
           , NVL(B.ORD_OUTDIA,0)     AS ORD_OUTDIA
           , A.DSN_PROC_YN, A.DSN_ERR_DESC
           , B.SPEC_ABBSYM, B.SPEC_STL_GRD
           , B.ORD_WGT
           , D.SLAB_THK
           , CASE WHEN (B.ORD_WTH + D.ADD_VALUE > 1450) THEN 1450 ELSE B.ORD_WTH + D.ADD_VALUE END AS SLAB_WTH
           , D.SLAB_LEN
           , B.PROD_WGT_MAX * (1 + (1 - C.PROC_RATE / 100)) AS SLAB_WGT
           , D.GRAVITY
           , B.EMGMTR_MNG_YN                        AS EMGMTR_YN
           , B.SCARFING_PROC_YN
           , B.GRIND_PROC_YN
           , A.HCR_CLF
           , B.EDGE_PROC_YN
           , B.SPM_PROC_YN
           , B.SHL_PROC_YN
           , B.SFC_PROC_YN
           , B.BTWSHT_INS_YN
           , B.KEEPWARM_YN
           , B.EACHLOT_YN
           , C.PROC_RATE                            AS PROC_RATE
           , H.SM_CFNM                              AS RCVORD_TP
           , E.PRODNM_CD
           , I.SM_CFNM                              AS ORD_USE_TP
      FROM TBD01_SLABDN_OBJ A, TBA01_ORD_LINE B
         ,(SELECT PROC_RATE FROM TBE01_PROC_RATE WHERE PROC_CD = 'R') C
         ,(SELECT MAX(DECODE(X.LG_CD,'D01002',TO_NUMBER(X.SM_CD))) AS SLAB_THK
                , MAX(DECODE(X.LG_CD,'D01006',TO_NUMBER(X.SM_CD))) AS GRAVITY
                , NVL(:1,11000)                                    AS SLAB_LEN
                , NVL(:2,   50)                                    AS ADD_VALUE
           FROM TBZ00_COMMCD X
           WHERE X.LG_CD IN ('D01002','D01006')
           AND X.CD_SEQ = 1) D
         ,(SELECT *
           FROM TBE02_ORD_PRC
           WHERE ORD_PROG_CD IN ('D','E')) E
         , TBB01_ORD_PROD F
         , TBD00_STLGRD G
         ,(SELECT SM_CD, SM_CFNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01001') H
         ,(SELECT SM_CD, SM_CFNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01007') I
      WHERE A.ORD_NO = B.ORD_NO(+) AND A.ORD_SEQ = B.ORD_SEQ(+)
      AND B.ORD_NO = E.ORD_NO(+) AND B.ORD_SEQ = E.ORD_SEQ(+)
      AND E.ORD_NO = F.ORD_NO    AND E.ORD_SEQ = F.ORD_SEQ
      AND E.STL_GRD = G.FAC_STL_GRD(+)
      AND E.RCVORD_CLF = H.SM_CD(+)
      AND E.ORD_USEAGE_CD = I.SM_CD(+)
      AND A.DSN_PROC_STAT LIKE NVL(:3,'1')||'%'
      AND A.ORD_NO LIKE :4||'%'
      AND A.ORD_SEQ LIKE :5||'%') X
    , TBD00_SLABDN_WGTSTD Y
WHERE X.SLAB_THK = Y.SLAB_THK(+)
AND X.SLAB_WTH = Y.SLAB_WTH(+)	
        ]]>
    </query>
	
	<query id="SaveSlabDnObj.insert" desc="" fetchSize="10">
        <![CDATA[
INSERT INTO TBD01_SLABDN_OBJ( ORD_NO, ORD_SEQ                                -- :1, :2
                            , NEED_WGT                                       -- :3
                            , DSN_PROC_STAT                                  -- :4
                            , DSN_PROC_YN                                    -- :5
                            , DSN_ERR_DESC                                   -- :6
                            , HCR_CLF                                        -- :7
                            , REG_ID, REG_DTIME, MOD_ID, MOD_DTIME )         -- :8, SYSTIMESTAMP, :9, SYSTIMESTAMP
VALUES( :1, :2, :3, :4, :5, :6, :7, NVL(:8,'UID010010'), SYSTIMESTAMP, NVL(:9,'UID010010'), SYSTIMESTAMP )
        ]]>
    </query>

	<query id="SaveSlabDnObj.update" desc="" fetchSize="10">
        <![CDATA[
UPDATE TBD01_SLABDN_OBJ
SET NEED_WGT          = :1
  , DSN_PROC_STAT     = :2
  , DSN_PROC_YN       = :3
  , DSN_ERR_DESC      = :4
  , HCR_CLF           = :5
  , MOD_ID            = NVL(:6,'UID010031')
  , MOD_DTIME         = SYSTIMESTAMP
WHERE ORD_NO          = :7
AND ORD_SEQ           = :8
        ]]>
    </query>

	<query id="SaveSlabDnObj.delete" desc="" fetchSize="10">
        <![CDATA[
DELETE FROM TBD01_SLABDN_OBJ WHERE ORD_NO = :1 AND ORD_SEQ = :2
        ]]>
    </query>

	<query id="ModifyTargetRows.update" desc="" fetchSize="10">
        <![CDATA[
UPDATE TBD01_SLABDN_OBJ
SET DSN_PROC_YN = :1
  , DSN_PROC_STAT = :2
WHERE ORD_NO = :3 AND ORD_SEQ = :4
        ]]>
    </query>

	<query id="DesignSlabDnRst.insert" desc="" fetchSize="10">
        <![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, KEEPWARM_YN
                            , PROD_LINE,  C_COIL_THK,  C_COIL_WTH )
SELECT TO_CHAR(B.MAX_DSN_SLAB_NO + A.RNUM,'FM00000000'), NULL
     , :1, :2                             --, ORD_NO, ORD_SEQ
     , :3, :4, :5, :6                     --, SLAB_THK, SLAB_WTH, SLAB_LEN, SLAB_WGT
     , 'N'                                --, SLAB_TRMTH_TP
     , :7                                 --, GRAVITY
     , 'N'                                --, AUTODSN_YN
     , :8, :9                             --, COIL_PACK_WGT_MIN, COIL_PACK_WGT_MAX
     , :10                                --, FAC_STL_GRD
     , :11, :12, :13, :14                 --, COIL_THK, COIL_WTH, COIL_LEN, COIL_WGT
     , :15                                --, PROC_PATH
     , :16                                --, HCR_YN
     , :17                                --, EMGMTR_YN
     , :18                                --, FAC_STL_GRP
     , :19                                --, COMB_YN
     , :20, :21 ,:22                          --, COIL_INDIA, COIL_OUTDIA, KEEPWARM_YN
     , :23, :24 ,:25                      -- , PROD_LINE,  C_COIL_THK,  C_COIL_WTH 
FROM TBZ00_COPY A
   ,(SELECT NVL(MAX(DSN_SLAB_NO),0) AS MAX_DSN_SLAB_NO FROM TBD01_SLABDN_RST) B
WHERE A.RNUM <= :26
        ]]>
    </query>

	<query id="SearchByDesignProcStatus.select" desc="" fetchSize="10">
        <![CDATA[
SELECT '0' AS CHK_FG2
     , B.DSN_SLAB_NO AS DSN_SLAB_NO2, B.P_DSN_SLAB_NO AS P_DSN_SLAB_NO2
     , B.ORD_NO AS ORD_NO2, B.ORD_SEQ AS ORD_SEQ2
     , B.SLAB_THK AS SLAB_THK2, B.SLAB_WTH AS SLAB_WTH2, B.SLAB_LEN AS SLAB_LEN2, B.SLAB_WGT AS SLAB_WGT2, B.SLAB_TRMTH_TP AS SLAB_TRMTH_TP2, B.GRAVITY AS GRAVITY2 
     , B.AUTODSN_YN AS AUTODSN_YN2
     , B.COIL_PACK_WGT_MIN AS COIL_PACK_WGT_MIN2, B.COIL_PACK_WGT_MAX AS COIL_PACK_WGT_MAX2
     , B.FAC_STL_GRD AS FAC_STL_GRD2
     , B.COIL_THK AS COIL_THK2, B.COIL_WTH AS COIL_WTH2, B.COIL_LEN AS COIL_LEN2, B.COIL_WGT AS COIL_WGT2
     , B.PROC_PATH AS PROC_PATH2
     , B.HCR_YN AS HCR_YN2
     , B.EMGMTR_YN AS EMGMTR_YN2
     , B.FAC_STL_GRP AS FAC_STL_GRP2
     , B.COMB_YN AS COMB_YN2
     , B.COIL_INDIA AS COIL_INDIA2, B.COIL_OUTDIA AS COIL_OUTDIA2,B.KEEPWARM_YN AS KEEPWARM_YN2
       ---增加特殊要求 10.15 wxy
     ,DECODE(K.DSN_KIND, '1', 'Y', 'N') AS INGR_YN 
     ,B.PROD_LINE, B.C_COIL_THK, B.C_COIL_WTH
FROM TBD01_SLABDN_RST B
	 ,(SELECT DISTINCT ORD_NO, ORD_SEQ, DSN_KIND
					  FROM TBB01_ORD_INGR
					 WHERE DSN_KIND = '1') K
WHERE EXISTS (SELECT 'X'
              FROM TBD01_SLABDN_OBJ A
              WHERE A.DSN_PROC_STAT LIKE :1||'%'
              AND A.ORD_NO = B.ORD_NO
              AND A.ORD_SEQ = B.ORD_SEQ)
AND B.ORD_NO = K.ORD_NO(+)
AND B.ORD_SEQ = K.ORD_SEQ(+)             
AND B.ORD_NO LIKE :2||'%'
AND B.ORD_SEQ LIKE :3||'%'
ORDER BY B.DSN_SLAB_NO
/***************************************************************
[1] DSN_PROC_STAT  :'1'           (Default:'1')  : DsnProcStat
[2] ORD_NO         : 412009011202 (Default:NULL ): OrdNo
[3] ORD_SEQ        : 540          (Default:NULL ): OrdSeq
****************************************************************/
        ]]>
    </query>

	<query id="FinishSlabDesignRst.insert" desc="" fetchSize="10">
        <![CDATA[
INSERT INTO TBD01_SLABDN_RST( DSN_SLAB_NO           --   :1
                             ,P_DSN_SLAB_NO         --   :2
                             ,ORD_NO                --   :3
                             ,ORD_SEQ               --   :4
                             ,SLAB_THK              --   :5
                             ,SLAB_WTH              --   :6
                             ,SLAB_LEN              --   :7
                             ,SLAB_WGT              --   :8
                             ,SLAB_TRMTH_TP         --   :9
                             ,GRAVITY               --   :10
                             ,AUTODSN_YN            --   :11
                             ,COIL_PACK_WGT_MIN     --   :12
                             ,COIL_PACK_WGT_MAX     --   :13
                             ,FAC_STL_GRD           --   :14
                             ,COIL_THK              --   :15
                             ,COIL_WTH              --   :16
                             ,COIL_LEN              --   :17
                             ,COIL_WGT              --   :18
                             ,PROC_PATH             --   :19
                             ,HCR_YN                --   :20
                             ,EMGMTR_YN             --   :21
                             ,FAC_STL_GRP           --   :22
                             ,COMB_YN               --   :23
                             ,COIL_INDIA            --   :24
                             ,COIL_OUTDIA         --    :25
						     ,KEEPWARM_YN  )      --    :26
VALUES( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25 ,:26)
        ]]>
    </query>

	<query id="FinishSlabDesignRst.update" desc="" fetchSize="10">
        <![CDATA[
UPDATE TBD01_SLABDN_RST
SET P_DSN_SLAB_NO     =  :1
  , ORD_NO            =  :2
  , ORD_SEQ           =  :3
  , SLAB_THK          =  :4
  , SLAB_WTH          =  :5
  , SLAB_LEN          =  :6
  , SLAB_WGT          =  :7
  , SLAB_TRMTH_TP     =  :8
  , GRAVITY           =  :9
  , AUTODSN_YN        = :10
  , COIL_PACK_WGT_MIN = :11
  , COIL_PACK_WGT_MAX = :12
  , FAC_STL_GRD       = :13
  , COIL_THK          = :14
  , COIL_WTH          = :15
  , COIL_LEN          = :16
  , COIL_WGT          = :17
  , PROC_PATH         = :18
  , HCR_YN            = :19
  , EMGMTR_YN         = :20
  , FAC_STL_GRP       = :21
  , COMB_YN           = :22
  , COIL_INDIA        = :23
  , COIL_OUTDIA       = :24
  , KEEPWARM_YN       = :25
WHERE DSN_SLAB_NO = :26
        ]]>
    </query>

	<query id="FinishSlabDesignRst.delete" desc="" fetchSize="10">
        <![CDATA[
DELETE FROM TBD01_SLABDN_RST WHERE DSN_SLAB_NO = :1
        ]]>
    </query>

	<query id="DeleteDesignResult.delete" desc="" fetchSize="10">
        <![CDATA[
DELETE FROM TBD01_SLABDN_RST WHERE ORD_NO = :1 AND ORD_SEQ = :2
        ]]>
    </query>

	<query id="SearchMaxSlabPKey.select" desc="" fetchSize="10">
        <![CDATA[
SELECT TO_CHAR(NVL(MAX(DSN_SLAB_NO),0),'FM00000000') AS MAX_DSN_SLAB_NO
FROM TBD01_SLABDN_RST
        ]]>
    </query>




</queryMap>