XinYu_RZMES/.svn/pristine/d0/d0b70e4fac5b34df5310658b0f5bc8024403f993.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="降级余材处理">
	<query id="UIE031080.SlabComm.Select" desc="" fetchSize="10">
		<![CDATA[
		SELECT LVL "LEVEL", ORD_NO, ORD_SEQ,
          '板坯' MAT_NAME,
          DECODE(LVL, 1, SLAB_NO, SLAB_CUT_SEQ) DSP_NO,
          SLAB_NO MAT_NO,        -- 板坯号
          STL_GRD,          -- 钢号
          SLAB_THK MAT_THK,      -- 板坯厚度
          SLAB_WTH MAT_WTH,      -- 板坯宽度
          SLAB_LEN MAT_LEN,      -- 板坯长度
          SLAB_WGT MAT_WGT,      -- 板坯重量
          COIL_INDIA,          -- 钢卷内径
          COIL_OUTDIA,        -- 钢卷外径
          CUR_LOAD_LOC,        -- 现在堆置位置
          SLAB_CUT_DTIME MAT_DTIME,  -- 板坯截断时间
          EXTSHAPE_GRD MAT_GRD,    -- 等级
          XB_PACKAGE.GF_COMNNAME_FIND('E01002', CUR_PROG_CD) CUR_PROG_CD,  -- 现在进度 CODE
          XB_PACKAGE.GF_COMNNAME_FIND('E01002', BEF_PROG_CD) BEF_PROG_CD  -- 之前进度 CODE
          ,DEL_TO_DATE
          ,XB_PACKAGE.GF_COMNNAME_FIND('A01001', RCVORD_CLF) RCVORD_CLF
          ,XB_PACKAGE.GF_COMNNAME_FIND('A01004', PRODNM_CD) PRODNM_CD
      FROM (
      
      SELECT 1 LVL, T1.ORD_NO, T1.ORD_SEQ, T1.STL_GRD,
            T1.SLAB_NO, T1.SLAB_THK, T1.SLAB_WTH, T1.SLAB_LEN, SLAB_WGT, NULL COIL_INDIA, NULL COIL_OUTDIA,
            CUR_LOAD_LOC, SLAB_CUT_DTIME, EXTSHAPE_GRD,
            CUR_PROG_CD, BEF_PROG_CD, '00' SLAB_CUT_SEQ
            ,t3.DEL_TO_DATE
            ,t3.RCVORD_CLF
            ,t3.PRODNM_CD
          FROM TBG02_SLAB_COMM t1
               ,tbg02_slab_comm_d t2
               ,TBE02_ORD_PRC T3
          WHERE t1.SUB_SLAB_NO = t2.SLAB_NO
          AND   T1.ORD_NO = T3.ORD_NO
          AND   T1.ORD_SEQ = T3.ORD_SEQ
          and T1.CUR_PROG_CD IN ('RAB', 'RBA')
          AND T1.SLAB_STAT = '2'
          AND T1.ORD_FL = '1'
          AND T2.ORD_FL = '1'
          AND T1.ORD_NO LIKE :1||'%'
          AND T1.ORD_SEQ LIKE :2||'%'
          AND T1.SLAB_NO LIKE :3||'%'
          AND T1.STL_GRD LIKE :4||'%'
          AND T1.SLAB_WTH BETWEEN :5 AND :6
          AND T2.ORD_THK BETWEEN :7 AND :8
          AND T3.DEL_TO_DATE BETWEEN :9 AND :10		-- 交货日期（到达）
          AND (:11 IS NULL OR T3.RCVORD_CLF IN (                -- 参数：交货区分
                      SELECT COLUMN_VALUE FROM TABLE(CAST(XB_PACKAGE.GF_TABSTR(:12) AS TABSTR_T))))
          --AND T3.PRODNM_CD LIKE :13||'%'
          AND (:13 IS NULL OR T3.PRODNM_CD IN (                -- 参数：交货区分
                        SELECT COLUMN_VALUE FROM TABLE(CAST(XB_PACKAGE.GF_TABSTR(:14) AS TABSTR_T))))
          
     /*     
  UNION ALL
  SELECT 2 LVL, B.ORD_NO, B.ORD_SEQ, '',
            B.SLAB_NO, b.ORD_THK, b.ORD_WTH, b.ORD_LEN, b.ORD_WGT, COIL_INDIA, COIL_OUTDIA,
            '', '', '', '', '', SLAB_CUT_SEQ
            ,t3.DEL_TO_DATE
            ,t3.RCVORD_CLF
            ,t3.PRODNM_CD
          FROM TBG02_SLAB_COMM A, TBG02_SLAB_COMM_D B,tbe02_ord_prc t3
          WHERE A.CUR_PROG_CD IN ('RAB', 'RBA')
            AND A.SLAB_STAT = '2'
            AND A.ORD_FL = '1'
            AND B.ORD_FL = '1'
            AND A.SUB_SLAB_NO = B.SLAB_NO
            AND   a.ORD_NO = T3.ORD_NO
            AND   a.ORD_SEQ = T3.ORD_SEQ
            AND A.ORD_NO LIKE :15||'%'
            AND A.ORD_SEQ LIKE :16||'%'
            AND A.SLAB_NO LIKE :17||'%'
            AND A.STL_GRD LIKE :18||'%'
            AND a.SLAB_THK BETWEEN :19 AND :20
            AND T3.ORD_THK BETWEEN :21 AND :22
            AND T3.DEL_TO_DATE BETWEEN :23 AND :24		-- 交货日期（到达）
            AND (:25 IS NULL OR T3.RCVORD_CLF IN (                -- 参数：交货区分
                        SELECT COLUMN_VALUE FROM TABLE(CAST(XB_PACKAGE.GF_TABSTR(:26) AS TABSTR_T))))
            AND (:27 IS NULL OR T3.PRODNM_CD IN (                -- 参数：交货区分
                        SELECT COLUMN_VALUE FROM TABLE(CAST(XB_PACKAGE.GF_TABSTR(:28) AS TABSTR_T))))            
            --AND T3.PRODNM_CD LIKE :26||'%'
            */
            )
            ORDER BY SLAB_NO, SLAB_CUT_SEQ
            
            
		]]>
	</query> 
	<query id="UIE031080.CoilComm.Select" desc="" fetchSize="10">
		<![CDATA[
			SELECT LVL "LEVEL", ORD_NO, ORD_SEQ,
	    		'钢卷' MAT_NAME,
	    		DECODE(LVL, 1, OLD_SAMPL_NO, CUT_SEQ) DSP_NO,
	    		COIL_NO MAT_NO,				-- 钢卷号
	    		STL_GRD,					-- 钢号
	    		COIL_THK MAT_THK,			-- 钢卷厚度
	    		COIL_WTH MAT_WTH,			-- 钢卷宽度
	    		COIL_LEN MAT_LEN,			-- 钢卷长度
	   			ACT_WGT MAT_WGT,			-- 钢卷重量
	    		COIL_INDIA,					-- 钢卷内径
	    		COIL_OUTDIA,				-- 钢卷外径
	    		CUR_LOAD_LOC,				-- 现在堆置位置
	    		COIL_CRE_DTIME MAT_DTIME,	-- 钢卷生成时间
	    		TOT_DEC_GRD MAT_GRD,		-- 综合判定等级
	    		XB_PACKAGE.GF_COMNNAME_FIND('E01002', CUR_PROG_CD) CUR_PROG_CD,	-- 现在进度 CODE
	    		XB_PACKAGE.GF_COMNNAME_FIND('E01002', BEF_PROG_CD) BEF_PROG_CD	-- 之前进度 CODE
			FROM (
				SELECT  1 LVL, '' ORD_NO, '' ORD_SEQ, STL_GRD, OLD_SAMPL_NO,
					COIL_NO, COIL_THK, COIL_WTH, COIL_LEN, ACT_WGT, COIL_INDIA, COIL_OUTDIA,
					CUR_LOAD_LOC, COIL_CRE_DTIME, TOT_DEC_GRD,
					CUR_PROG_CD, BEF_PROG_CD, '00' CUT_SEQ
				FROM TBH02_COIL_COMM
				WHERE CUR_PROG_CD = 'SFA'
				  AND SHIP_DIRNO IS NULL
		      	  AND COIL_STAT = '2'
		      	  AND ORD_FL = '1'
	      		  AND ((ORD_NO = ? AND ORD_SEQ = ?)					-- 参数：合同号、订单号
		      		OR (COIL_NO IN (
			      		SELECT COIL_NO FROM TBH02_COIL_COMM_D
			      		WHERE ORD_FL = '1'
			      		  AND ORD_NO = ? AND ORD_SEQ = ?)))			-- 参数：合同号、订单号
				UNION ALL
					SELECT 2 LVL, B.ORD_NO, B.ORD_SEQ, '', '',
					B.COIL_NO, ORD_THK, ORD_WTH, ORD_LEN, ORD_WGT, ORD_INDIA, ORD_OUTDIA,
					'', '', '', '', '', CUT_SEQ
				FROM TBH02_COIL_COMM A, TBH02_COIL_COMM_D B
				WHERE A.CUR_PROG_CD = 'SFA'
				  AND A.SHIP_DIRNO IS NULL
		      	  AND A.COIL_STAT = '2'
				  AND A.ORD_FL = '1'
				  AND A.COIL_NO = B.COIL_NO
				  AND A.COIL_NO IN (
					SELECT COIL_NO FROM TBH02_COIL_COMM_D
					WHERE ORD_NO = ? AND ORD_SEQ = ?))
			ORDER BY COIL_NO, CUT_SEQ
		]]>
	</query> 
</queryMap>