XinYu_RZMES/.svn/pristine/e9/e9506d92ccc018573f01e2b426d73d638732dc3b.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="降级余材处理">
	<query id="UIK010060.OrdPrc.Select" desc="" fetchSize="10">
		<![CDATA[
			SELECT
				A.ORD_NO,				-- 合同号
				A.ORD_SEQ,			-- 订单号
				A.CUST_CD,			-- 客户代码
				A.PRODNM_CD, --热轧交货状态
           		A.C_PRODNM_CD,  --冷轧交货状态
           		A.PRD_TY ,      --产品类型
				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', 'F')
			  --AND (A.ORD_PRC_WGT > 0 OR A.PROD_END_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.ORD_NO ASC, A.ORD_SEQ ASC 
		]]>
	</query> 
	<query id="UIK010060.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
			FROM (
				SELECT 1 LVL, '' ORD_NO, '' ORD_SEQ, STL_GRD,
					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
				FROM TBG02_SLAB_COMM
				WHERE CUR_PROG_CD IN ('RAB', 'RBA')
		      	  AND SLAB_STAT = '2'
				  AND ORD_FL = '1'
				  AND ((ORD_NO = ? AND ORD_SEQ = ?)				-- 参数：合同号、订单号
		      		OR (SLAB_NO IN (
		      			SELECT SLAB_NO FROM TBG02_SLAB_COMM_D
						WHERE ORD_FL = '1'
						  AND ORD_NO = ? AND ORD_SEQ = ?)))		-- 参数：合同号、订单号
				UNION ALL
				SELECT 2 LVL, B.ORD_NO, B.ORD_SEQ, '',
					B.SLAB_NO, ORD_THK, ORD_WTH, ORD_LEN, ORD_WGT, COIL_INDIA, COIL_OUTDIA,
					'', '', '', '', '', SLAB_CUT_SEQ
				FROM TBG02_SLAB_COMM A, TBG02_SLAB_COMM_D B
				WHERE A.CUR_PROG_CD IN ('RAB', 'RBA')
				  AND A.SLAB_STAT = '2'
				  AND A.ORD_FL = '1'
				  AND A.SLAB_NO = B.SLAB_NO
				  AND A.SLAB_NO IN (
					SELECT SLAB_NO FROM TBG02_SLAB_COMM_D
					WHERE ORD_FL = '1'
					  AND ORD_NO = ? AND ORD_SEQ = ?))			-- 参数：合同号、订单号
			ORDER BY SLAB_NO, SLAB_CUT_SEQ
		]]>
	</query> 
	<query id="UIK010060.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_BJ
				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_BJ 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*/
			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,
               T.SPEC_STL_GRD STL_GRD,
               T.OLD_SAMPL_NO,
               T.COIL_NO,
               T.COIL_THK,
               T.COIL_WTH,
               T.COIL_LEN,
               T.ACT_WGT,
               T.COIL_INDIA,
               T.COIL_OUTDIA,
               T.CUR_LOAD_LOC,
               T.COIL_CRE_DTIME,
               T.TOT_DEC_GRD,
               T.CUR_PROG_CD,
               T.BEF_PROG_CD,
               '00' CUT_SEQ
          FROM TBH02_COIL_COMM t,KCJ3_STUFFLIST t1
         WHERE T.CUR_PROG_CD = 'SGF'
           AND T.SHIP_DIRNO IS NULL
           AND T.OLD_SAMPL_NO=T1.OLD_SAMPL_NO
           AND T1.STORAGESTATUS!='501001'
           AND T.COIL_STAT = '3'
           AND T.ORD_FL = '1'
           AND ((T.ORD_NO = ? AND T.ORD_SEQ = ?) -- 参数：合同号、订单号
               OR (T.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 = 'SGF'
           AND A.SHIP_DIRNO IS NULL
           AND A.COIL_STAT = '3'
           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> 
	
	<query id="UIK010060.CoilCommBJ.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,
               T.SPEC_STL_GRD STL_GRD,
               T.OLD_SAMPL_NO,
               T.COIL_NO,
               T.COIL_THK,
               T.COIL_WTH,
               T.COIL_LEN,
               T.ACT_WGT,
               T.COIL_INDIA,
               T.COIL_OUTDIA,
               T.CUR_LOAD_LOC,
               T.COIL_CRE_DTIME,
               T.TOT_DEC_GRD,
               T.CUR_PROG_CD,
               T.BEF_PROG_CD,
               '00' CUT_SEQ
          FROM TBH02_COIL_COMM t,KCJ3_STUFFLIST t1
         WHERE T.CUR_PROG_CD = 'SFF'
            AND T.ORD_NO IN( 'W322021063100','W322021063101')
           AND T.OLD_SAMPL_NO=T1.OLD_SAMPL_NO
           AND T1.STORAGESTATUS!='501001'
           AND T.COIL_STAT = '3'
           AND T.ORD_FL = '1'
           AND ((T.ORD_NO = ? AND T.ORD_SEQ = ?) -- 参数：合同号、订单号
               OR (T.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 = 'SFF'
     	   AND A.ORD_NO IN( 'W322021063100','W322021063101')
           AND A.COIL_STAT = '3'
           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>