XinYu_RZMES/.svn/pristine/1d/1d618d112882722e98f380563a0d7d1fe86af35a.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="用途转换处理">
	<query id="UIE031060.OrdPrc.Select" desc="" fetchSize="10">
		<![CDATA[
			SELECT
				A.ORD_NO,								-- 合同号
				A.ORD_SEQ,							-- 订单号
				A.CUST_CD,							-- 客户代码
				A.PRODNM_CD,							-- 产品代码
				A.STL_GRD,							-- 钢号
				A.SPEC_ABBSYM,						-- 标准号
				A.ORD_USEAGE_CD,						-- 订单用途
				A.ORD_THK,							-- 订单厚度
				A.ORD_WTH,							-- 订单宽度
				A.ORD_LEN,							-- 订单长度
				A.ORD_INDIA,							-- 订单内径
				A.ORD_OUTDIA,							-- 订单外径
				A.ORD_UNIT_WGT_MIN,					-- 订单重量下限
				A.ORD_UNIT_WGT_MAX,					-- 订单重量上限
				NVL(A.EMGMTR_NMG_CLF, 'N') EMGMTR_NMG_CLF,	-- 紧急材代码
				A.DEL_TO_DATE,						-- 交货期结束
				A.RCVORD_CLF,							-- 接受订单区分
				A.ORD_WGT,							-- 订单重量
				A.ORD_PRC_WGT,						-- 生产进程重量
				A.ORD_REM_WGT,						-- 订单余重量
				A.SHIP_END_WGT						-- 发货完重量
			   ,T2.SM_CFNM DLIV_TP
			   ,DECODE(K.DSN_KIND, '1', 'Y', 'N') AS INGR_YN
            FROM  TBE02_ORD_PRC   A
                 ,TBA01_ORD_LINE  B
                 ,(SELECT   SM_CD, SM_CFNM FROM TBZ00_COMMCD  WHERE LG_CD = 'A01012') T2
			     ,(SELECT DISTINCT ORD_NO, ORD_SEQ, DSN_KIND
                     FROM TBB01_ORD_INGR
                    WHERE DSN_KIND = '1') K
            WHERE A.ORD_NO        = B.ORD_NO(+)
              AND A.ORD_SEQ       = B.ORD_SEQ(+)
			  AND A.ORD_NO        = K.ORD_NO(+)
              AND A.ORD_SEQ       = K.ORD_SEQ(+)  
              AND B.DLIV_TP       =  T2.SM_CD(+)   
			  AND A.ORD_PROG_CD IN ('D', 'E', 'H')
			  AND A.ORD_REM_WGT > 0
			  AND A.PRODNM_CD LIKE ?||'%'				-- 产品代码
			  AND A.CUST_CD||'&' LIKE ?||'%'			-- 客户代码
			  AND A.STL_GRD LIKE ?||'%'				-- 钢号
			  AND A.ORD_NO LIKE ?||'%'				-- 合同号
			  AND A.ORD_SEQ LIKE ?||'%'				-- 订单号
			  AND A.RCVORD_CLF||'&' LIKE ?||'%'		-- 订单区分
			  AND A.DEL_TO_DATE BETWEEN ? AND ?		-- 交货日期（到达）
			  AND A.ORD_USEAGE_CD||'&' LIKE ?||'%'	-- 订单用途
			  AND A.SPEC_ABBSYM||'&' LIKE ?||'%'		-- 标准号
			  AND A.ORD_THK BETWEEN ? AND ?			-- 订单厚度
			  AND A.ORD_WTH BETWEEN ? AND ?			-- 订单宽度
			ORDER  BY A.DEL_TO_DATE,A.ORD_NO ASC, A.ORD_SEQ ASC 
        ]]>
    </query> 
	<query id="UIE031060.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,			 		-- 牌号
	      		SPEC_ABBSYM,				-- 标准号
	      		ORD_USE_CD,			   		-- 订单用途
	      		SLAB_THK MAT_THK,			-- 板坯厚度
	      		SLAB_WTH MAT_WTH,			-- 板坯宽度
	      		SLAB_LEN MAT_LEN,			-- 板坯长度
	      		SLAB_WGT MAT_WGT,			-- 板坯重量
	      		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
				MAT_REP_WGT
			FROM (
				SELECT 1 LVL, A.ORD_NO, A.ORD_SEQ, A.STL_GRD, A.SPEC_ABBSYM, A.ORD_USE_CD,
					SLAB_NO, SLAB_THK, SLAB_WTH, 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,
					(A.SLAB_WGT * C.PROC_RATE / 100) MAT_REP_WGT
				FROM TBG02_SLAB_COMM A, TBE02_ORD_PRC B, (
					SELECT PROC_RATE FROM TBE01_PROC_RATE WHERE PROC_CD = 'R') C
				WHERE A.CUR_PROG_CD = 'RBA'
		          AND A.SLAB_STAT = '2'
				  AND B.ORD_NO = ?
				  AND B.ORD_SEQ = ?
		          AND A.ORD_USE_CD = B.ORD_USEAGE_CD		-- 相同订单用途
		          AND A.SPEC_ABBSYM = B.SPEC_ABBSYM			-- 相同标准
				  AND A.SLAB_WTH BETWEEN B.ORD_WTH AND B.ORD_WTH + 100
		          AND (A.SLAB_WGT * C.PROC_RATE / 100) BETWEEN B.ORD_UNIT_WGT_MIN AND B.ORD_UNIT_WGT_MAX
				  AND ((A.ORD_NO||A.ORD_SEQ <> B.ORD_NO||B.ORD_SEQ)
					OR (A.SLAB_NO NOT IN (
					  	SELECT SLAB_NO FROM TBG02_SLAB_COMM_D
					  	WHERE ORD_NO = B.ORD_NO AND ORD_SEQ = B.ORD_SEQ)))
				  AND ('Y' <> NVL(?, 'N') OR A.STL_GRD = B.STL_GRD)									-- 同牌号
				  AND ('Y' <> NVL(?, 'N') OR GF_NIB028720(A.SLAB_NO, B.ORD_NO, B.ORD_SEQ) = 'YY')	-- 检查成分项目
				UNION ALL
				SELECT 2 LVL, D.ORD_NO, D.ORD_SEQ, '', '', '',
					D.SLAB_NO, D.ORD_THK, D.ORD_WTH, D.ORD_LEN, D.ORD_WGT, D.COIL_INDIA, D.COIL_OUTDIA,
					'', '', '', '', '', SLAB_CUT_SEQ, NULL
				FROM TBG02_SLAB_COMM A, TBE02_ORD_PRC B, (
					SELECT PROC_RATE FROM TBE01_PROC_RATE WHERE PROC_CD = 'R') C, TBG02_SLAB_COMM_D D
				WHERE A.CUR_PROG_CD = 'RBA'
		          AND A.SLAB_STAT = '2'
				  AND A.ORD_FL = '1'
				  AND B.ORD_NO = ?
				  AND B.ORD_SEQ = ?
		          AND A.ORD_USE_CD = B.ORD_USEAGE_CD		-- 相同订单用途
		          AND A.SPEC_ABBSYM = B.SPEC_ABBSYM			-- 相同标准 
				  AND A.SLAB_WTH BETWEEN B.ORD_WTH AND B.ORD_WTH + 100
		          AND (A.SLAB_WGT * C.PROC_RATE / 100) BETWEEN B.ORD_UNIT_WGT_MIN AND B.ORD_UNIT_WGT_MAX
		          AND A.SLAB_NO = D.SLAB_NO
				  AND ((A.ORD_NO||A.ORD_SEQ <> B.ORD_NO||B.ORD_SEQ)
					OR (A.SLAB_NO NOT IN (
					  	SELECT SLAB_NO FROM TBG02_SLAB_COMM_D
					  	WHERE ORD_NO = B.ORD_NO AND ORD_SEQ = B.ORD_SEQ)))
				  AND ('Y' <> NVL(?, 'N') OR A.STL_GRD = B.STL_GRD)									-- 同牌号
				  AND ('Y' <> NVL(?, 'N') OR GF_NIB028720(A.SLAB_NO, B.ORD_NO, B.ORD_SEQ) = 'YY'))	-- 检查成分项目
			ORDER BY SLAB_NO, SLAB_CUT_SEQ
		]]>
    </query> 
	<query id="UIE031060.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,			 		-- 牌号
	      		SPEC_ABBSYM,				-- 标准号
	      		ORD_USE_CD,			   		-- 订单用途
	      		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
				ACT_WGT MAT_REP_WGT
			FROM (
				SELECT 1 LVL, A.ORD_NO, A.ORD_SEQ, A.STL_GRD, A.SPEC_ABBSYM, A.ORD_USE_CD, 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 A, TBE02_ORD_PRC B
			     -------------------------------------------
			      --MODIFY BY SJW 091010
			      --DESC:  成品同牌号更改为spec_stl_grd
			     ,TBA01_ORD_LINE C
			     -------------------------------------------
				WHERE A.CUR_PROG_CD = 'SFA'
		          AND A.COIL_STAT = '2'
				  -----------------------------------------------------------
				   --MODIFY BY SJW 091010
				   --DESC:  成品同牌号更改为spec_stl_grd
				  AND B.ORD_NO = C.ORD_NO					-- 合同号
				  AND B.ORD_SEQ = C.ORD_SEQ						-- 订单号
				  -----------------------------------------------------------
				  AND B.ORD_NO = ?
				  AND B.ORD_SEQ = ?
				  AND ((A.ORD_NO||A.ORD_SEQ <> B.ORD_NO||B.ORD_SEQ)
					OR (A.COIL_NO NOT IN (
					  	SELECT COIL_NO FROM TBH02_COIL_COMM_D
					  	WHERE ORD_NO = B.ORD_NO AND ORD_SEQ = B.ORD_SEQ)))
				  --AND ('Y' <> NVL(?, 'N') OR A.STL_GRD = B.STL_GRD)	
				  ------------------------------------------------------------------------------------------
				  --MODIFY BY SJW 091010
				  --DESC:  成品同牌号更改为spec_stl_grd
				  AND ('Y' <> NVL(?, 'N') OR A.SPEC_STL_GRD = C.SPEC_STL_GRD)						-- 同牌号
				  ------------------------------------------------------------------------------------------								-- 同牌号
				  AND GF_NIB028770(A.COIL_NO, B.ORD_NO, B.ORD_SEQ) = 'YY'							-- 检查尺寸通过
				  AND ('Y' <> NVL(?, 'N') OR GF_NIB028720(A.SLAB_NO, B.ORD_NO, B.ORD_SEQ) = 'YY')	-- 检查成分项目
				  AND ('Y' <> NVL(?, 'N') OR GF_NIB028750(A.COIL_NO, B.ORD_NO, B.ORD_SEQ) = 'YY')	-- 检查材质项目
				UNION ALL
				SELECT 2 LVL, C.ORD_NO, C.ORD_SEQ, '', '', '', '',
					C.COIL_NO, C.ORD_THK, C.ORD_WTH, C.ORD_LEN, C.ORD_WGT, C.ORD_INDIA, C.ORD_OUTDIA,
					'', '', '', '', '', CUT_SEQ
				FROM TBH02_COIL_COMM A, TBE02_ORD_PRC B, TBH02_COIL_COMM_D C
			      -------------------------------------------
			      --MODIFY BY SJW 091010
			      --DESC:  成品同牌号更改为spec_stl_grd
			     ,TBA01_ORD_LINE D
			     -------------------------------------------
				WHERE A.CUR_PROG_CD = 'SFA'
		          AND A.COIL_STAT = '2'
				  AND A.ORD_FL = '1'
				  -----------------------------------------------------------
				   --MODIFY BY SJW 091010
				   --DESC:  成品同牌号更改为spec_stl_grd
				  AND B.ORD_NO = D.ORD_NO					-- 合同号
				  AND B.ORD_SEQ = D.ORD_SEQ						-- 订单号
				  -----------------------------------------------------------
				  AND B.ORD_NO = ?
				  AND B.ORD_SEQ = ?
				  AND A.COIL_NO = C.COIL_NO
				  AND ((A.ORD_NO||A.ORD_SEQ <> B.ORD_NO||B.ORD_SEQ)
					OR (A.COIL_NO NOT IN (
					  	SELECT COIL_NO FROM TBH02_COIL_COMM_D
					  	WHERE ORD_NO = B.ORD_NO AND ORD_SEQ = B.ORD_SEQ)))
				  --AND ('Y' <> NVL(?, 'N') OR A.STL_GRD = B.STL_GRD)
				  ------------------------------------------------------------------------------------------
				  --MODIFY BY SJW 091010
				  --DESC:  成品同牌号更改为spec_stl_grd
				  AND ('Y' <> NVL(?, 'N') OR A.SPEC_STL_GRD = D.SPEC_STL_GRD)						-- 同牌号
				  ------------------------------------------------------------------------------------------									-- 同牌号
				  AND GF_NIB028770(A.COIL_NO, B.ORD_NO, B.ORD_SEQ) = 'YY'							-- 检查尺寸通过
				  AND ('Y' <> NVL(?, 'N') OR GF_NIB028720(A.SLAB_NO, B.ORD_NO, B.ORD_SEQ) = 'YY')	-- 检查成分项目
				  AND ('Y' <> NVL(?, 'N') OR GF_NIB028750(A.COIL_NO, B.ORD_NO, B.ORD_SEQ) = 'YY'))	-- 检查材质项目
			ORDER BY COIL_NO, CUT_SEQ
		]]>
    </query> 
</queryMap>