XinYu_RZMES/.svn/pristine/26/26f228b8a07d107cfd9f6d9bbac5a0b2638df702.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc=" 订单进程现况查询">

	<query id="UIE012080.OrdPrc.Select" desc="" fetchSize="10">
		<![CDATA[
			
SELECT X.ORD_NO,
	   X.ORD_SEQ,
	   XB_PACKAGE.GF_COMNNAME_FIND('E01001', X.ORD_PROG_CD) ORD_PROG_CD, -- 订单进度代码
	   XB_PACKAGE.GF_CUSTOMER_FIND(X.CUST_CD) CUST_CD, -- 客户代码
	   X.SPEC_STL_GRD,
	   X.ORD_THK,
	   X.ORD_WTH,
	   XB_PACKAGE.GF_COMNNAME_FIND('A01001', X.RCVORD_CLF) RCVORD_CLF, -- 订单进度代码
	   DECODE(X.ORD_WGT, 0, '', X.ORD_WGT) ORD_WGT,
	   DECODE(X.YDZ_WGT_MAX, 0, '', X.YDZ_WGT_MAX) YDZ_WGT_MAX,
	   DECODE(X.YDZ_WGT_MIN, 0, '', X.YDZ_WGT_MIN) YDZ_WGT_MIN,
	   DECODE(M.REP_WGT, 0, '', M.REP_WGT) REP_WGT,
	   DECODE(X.SHIP_END_WGT, 0, '', SHIP_END_WGT) SHIP_END_WGT,
	   DECODE(X.QJ_WGT, 0, '', QJ_WGT) QJ_WGT,
	   DECODE(X.ORD_REM_WGT, 0, '', X.ORD_REM_WGT) ORD_REM_WGT,
	   DECODE(Y.KC_WGT, 0, '', Y.KC_WGT) KC_WGT,
	   DECODE(Y.KFL_WGT, 0, '', Y.KFL_WGT) KFL_WGT,
	   DECODE(Y.YXCDZ_WGT, 0, '', Y.YXCDZ_WGT) YXCDZ_WGT,
	   DECODE(Z.LGJH_WGT, 0, '', Z.LGJH_WGT) LGJH_WGT,
	   W.DQY_WGT,
	   W.WSY_WGT,
	   W.ZCDP_WGT,
	   W.JXDP_WGT,
	   W.BHG_WGT,
	   X.DEL_TO_DATE,
	   X.SFF_END_DTIME,
	   DECODE(K.DSN_KIND, '1', 'Y', 'N') AS INGR_YN
  FROM (SELECT A.ORD_NO, --合同号
			   A.ORD_SEQ, --订单号
			   B.SPEC_STL_GRD,
			   A.ORD_THK,
			   A.ORD_WTH,
			   A.ORD_WGT,
			   (A.WGT_ACVAL_MAX - A.ORD_WGT) YDZ_WGT_MAX, --溢短装量
			   (A.ORD_WGT - A.WGT_ACVAL_MIN) YDZ_WGT_MIN, --溢短装量下限
			   '' REP_WGT, --已充当
			   A.SHIP_END_WGT, --已发量   
			   CASE
				   WHEN (A.ORD_REM_WGT < 0) THEN
					0
				   ELSE
					A.ORD_REM_WGT
			   END ORD_REM_WGT, --板坯待设计量           
			   CASE
				   WHEN (A.ORD_WGT - A.SHIP_END_WGT < 0) THEN
					0
				   ELSE
					A.ORD_WGT - A.SHIP_END_WGT
			   END QJ_WGT, --欠交量
			   A.ORD_PROG_CD, -- 订单进度代码
			   A.CUST_CD, -- 客户代码
			   A.DEL_TO_DATE,
			   A.RCVORD_CLF,
			   DECODE(A.ORD_PROG_CD, 'G', A.MOD_DTIME, '') SFF_END_DTIME
		  FROM TBE02_ORD_PRC A, TBA01_ORD_LINE B
		 WHERE A.ORD_NO = B.ORD_NO
		   AND A.ORD_SEQ = B.ORD_SEQ) X
	   
	  ,
	   (SELECT X.ORD_NO,
			   X.ORD_SEQ,
			   SUM(X.KC_WGT) KC_WGT,
			   SUM(KFL_WGT) KFL_WGT,
			   SUM(YXCDZ_WGT) YXCDZ_WGT
		  FROM (SELECT B.ORD_NO,
					   B.ORD_SEQ,
					   B.ACT_WGT, --库存量 
					   CASE
						   WHEN (B.CUR_LOAD_LOC LIKE 'C%') THEN
							B.ACT_WGT
						   ELSE
							0
					   END KC_WGT,
					   CASE
						   WHEN (B.ORD_NO NOT LIKE 'T%' AND
								B.SHIP_PROG_CD IS NULL AND
								B.DLIV_DIRNO IS NULL AND B.COIL_STAT = '2') THEN
							B.ACT_WGT
						   ELSE
							0
					   END KFL_WGT,
					   CASE
						   WHEN (B.CUR_PROG_CD = 'SFB') THEN
							B.ACT_WGT
						   ELSE
							0
					   END YXCDZ_WGT
				  FROM TBH02_COIL_COMM B, TBH02_COIL_COMM_D C
				 WHERE B.ORD_FL = '1'
				   AND B.COIL_STAT = '2'
				   AND B.COIL_NO = C.COIL_NO
				   AND B.ORD_FL = C.ORD_FL) X
		 GROUP BY (X.ORD_NO, X.ORD_SEQ)) Y
	   
	  ,
	   (SELECT B.ORD_NO, B.ORD_SEQ, SUM(B.ORD_WGT) LGJH_WGT --炼钢计划量
		  FROM TBF01_SPEC_SLAB B, TBF01_SPEC_SLAB_D C
		 WHERE B.ORD_FL = '1'
		   AND B.SLAB_EDT_SEQ = C.SLAB_EDT_SEQ
		   AND B.ORD_FL = C.ORD_FL
		 GROUP BY (B.ORD_NO, B.ORD_SEQ)) Z
	   
	  ,
	   (SELECT CC.ORD_NO,
			   CC.ORD_SEQ,
			   SUM(DECODE(LC.SMP_PROG_CD, 'A', TO_CHAR(CC.ACT_WGT), '')) DQY_WGT --待取样
			  ,
			   SUM(CASE
					   WHEN (LC.SMP_PROG_CD = 'A' OR LC.SMP_PROG_CD = 'B') AND
							LC.SMP_SEND_DTIME IS NULL THEN
						TO_CHAR(CC.ACT_WGT)
					   ELSE
						''
				   END) WSY_WGT --未送样
			  ,
			   SUM(DECODE(LC.SMP_PROG_CD, 'E', '', CC.ACT_WGT)) ZCDP_WGT --材质待判
			  ,
			   SUM(CASE
					   WHEN SQ.MS_QLTY_CD = 'TT' AND LC.SMP_PROG_CD != 'E' THEN
						TO_CHAR(CC.ACT_WGT)
					   ELSE
						''
				   END) JXDP_WGT --金相待判
			  ,
			   SUM(DECODE(CC.TOT_DEC_GRD, '1', '', TO_CHAR(CC.ACT_WGT))) BHG_WGT --不合格量
		/*/COUNT(LC.SMP_NO)*/
		  FROM TBH02_COIL_COMM CC,
			   TBB02_LOT_COM   LC,
			   TBB01_SEQ_QLTY  SQ,
			   TBB02_INS_QLTY  IQ
		 WHERE CC.SAMPL_NO = LC.SMP_NO
		   AND LC.SMP_NO = IQ.SMP_NO
		   AND CC.SAMPL_CUT_LOC = LC.SMP_CUT_LOC
		   AND LC.SMP_CUT_LOC = IQ.SMP_CUT_LOC
		   AND SQ.QLTY_CD = IQ.QLTY_CD
		   AND CC.ORD_FL = '1'
		 GROUP BY CC.ORD_NO, CC.ORD_SEQ) W,
	   (SELECT ORD_NO, ORD_SEQ, SUM(REP_WGT) REP_WGT
		  FROM (SELECT B.ORD_NO, B.ORD_SEQ, SUM(B.ACT_WGT) REP_WGT
				  FROM TBH02_COIL_COMM B, TBH02_COIL_COMM_D C
				 WHERE B.ORD_FL = '1'
				   AND B.COIL_NO = C.COIL_NO
				   AND B.ORD_FL = C.ORD_FL
				   AND B.ORD_NO <> NVL(B.ORG_ORD_NO, 'XX')
				   AND B.ORD_SEQ <> NVL(B.ORG_ORD_SEQ, 'XX')
				 GROUP BY B.ORD_NO, B.ORD_SEQ
				
				UNION ALL
				
				SELECT B.ORD_NO, B.ORD_SEQ, SUM(B.SLAB_WGT) REP_WGT
				  FROM TBG02_SLAB_COMM B, TBG02_SLAB_COMM C
				 WHERE B.ORD_FL = '1'
				   AND B.SLAB_NO = C.SLAB_NO
				   AND B.ORD_FL = C.ORD_FL
				   AND B.ORD_NO <> NVL(B.ORG_ORD_NO, 'XX')
				   AND B.ORD_SEQ <> NVL(B.ORG_ORD_SEQ, 'XX')
				 GROUP BY B.ORD_NO, B.ORD_SEQ)
		 GROUP BY (ORD_NO, ORD_SEQ)) M,
	   (SELECT DISTINCT ORD_NO, ORD_SEQ, DSN_KIND
		  FROM TBB01_ORD_INGR
		 WHERE DSN_KIND = '1') K
 WHERE X.ORD_NO = Y.ORD_NO(+)
   AND X.ORD_SEQ = Y.ORD_SEQ(+)
   AND X.ORD_NO = Z.ORD_NO(+)
   AND X.ORD_SEQ = Z.ORD_SEQ(+)
   AND X.ORD_NO = W.ORD_NO(+)
   AND X.ORD_SEQ = W.ORD_SEQ(+)
   AND X.ORD_NO = M.ORD_NO(+)
   AND X.ORD_SEQ = M.ORD_SEQ(+)
   AND X.ORD_NO = K.ORD_NO(+)
   AND X.ORD_SEQ = K.ORD_SEQ(+)
   AND X.ORD_NO LIKE ? || '%'
   AND X.ORD_SEQ LIKE ? || '%'
   AND (? IS NULL OR
	   X.ORD_PROG_CD IN
	   (SELECT COLUMN_VALUE
		   FROM TABLE(CAST(XB_PACKAGE.GF_TABSTR(?) AS TABSTR_T)))) -- 参数：订单进度代码
   AND X.CUST_CD LIKE ? || '%'
   AND X.SPEC_STL_GRD LIKE ? || '%'
   AND X.ORD_THK BETWEEN ? AND ?
   AND X.ORD_WTH BETWEEN ? AND ?
   AND X.DEL_TO_DATE BETWEEN ? AND ?
   AND RCVORD_CLF LIKE ? || '%'			
 ORDER BY X.ORD_NO, X.ORD_SEQ
			
		]]>
	</query> 
</queryMap>