XinYu_LZMES/.svn/pristine/2c/2c3e52ea5213ea7d150f321d68356761bfaacf67.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="中间库盘入盘出">
	<query id="UIM010280_01.SELECT"  desc="查询可发量">
		<![CDATA[
		 SELECT A.ORD_NO,
		 A.ORD_SEQ,
		 A.ORD_WGT / 1000 ORD_WGT,
		 C.ACT_WGT,
		 A.SPEC_STL_GRD STL_GRD,
		TO_CHAR(to_char(A.ORD_THK,'FM990.099') || '*' || A.ORD_WTH) COIL_THK, 
		(select cust_nm from tbz00_customer where cust_cd=B.CUST_CD)CUST_CD           -- 客户代码 B.CUST_CD,
		,( SELECT SM_CFNM FROM TBZ00_COMMCD WHERE SM_CD= A.DEST_PCD AND LG_CD='A01009')DEST_PCD           -- 目的地 A.DEST_PCD, 
		,(select sm_cfnm from tbz00_commcd where sm_cd= B.PRDNM_CD  and lg_cd='A01004' ) PRODNM_CD  --B.PRDNM_CD ,
		 ,C_PKG_UIM.GET_STDDATESTR(A.ORD_DEVLMT_DATE) DEVLMT_DTIME,
		(select sm_cfnm from tbz00_commcd where lg_cd='A01015' and sm_cd= A.EXLV_LINE_CD)EXLV_LINE_CD  ,--  A.EXLV_LINE_CD,
		 A.DLIV_TP,
		 CASE
		 WHEN NVL(A.BY_SEA_YN, 'N') = 'Y' THEN
		 'TRUE'
		 ELSE
		 'FALSE'
		 END BY_SEA_YN,
		(select sm_cfnm from tbz00_commcd where sm_cd= B.RCVORD_TP and lg_cd='A01001' ) RCVORD_TP         -- 订单接受区分B.RCVORD_TP,
		 ,CASE
		WHEN P.ORD_PROG_CD IN ('A', 'C', 'D', 'E', 'H', 'F') THEN
		 (P.ORD_WGT - P.SHIP_END_WGT) / 1000
		 ELSE
		 0 
		 END LEV_WGT,
		 nvl(D.PLAN_WGT,0) PLAN_WGT,
		 C_PKG_UIM.GET_STDDATESTR(A.ORD_PLAN_DATA) ORD_PLAN_DATA --新增订单预排期 
		 FROM TBA01_ORD_LINE A,
		 TBA01_ORD_COMM B,
		 TBE02_ORD_PRC P,
		(SELECT ORD_NO  FROM TBE02_ORD_PRC WHERE ORD_NO NOT LIKE 'TSH%'
		AND ORD_PROG_CD IN ('A', 'C', 'D', 'E', 'H', 'F')
		GROUP BY ORD_NO) Q, (SELECT ORD_NO,ORD_SEQ, SUM(ACT_WGT / 1000) ACT_WGT, SUM(CASE
		WHEN CUR_PROG_CD = 'SFB' THEN ACT_WGT END) / 1000 PLAN_WGT FROM C_TBL02_COIL_COMM
		 WHERE SHIP_PROG_CD IS NULL
		 AND DLIV_DIRNO IS NULL
		 AND COIL_STAT = '2'
		 ]]>
	</query>
	<query id="UIM010280_02.SELECT"  desc="查询可发量">
		<![CDATA[
		 GROUP BY ORD_NO, ORD_SEQ)   C,
		 (SELECT ORD_NO, ORD_SEQ, SUM(ACT_WGT / 1000) PLAN_WGT
		 FROM C_TBL02_COIL_COMM
		 WHERE CUR_PROG_CD = 'DFB'
		 GROUP BY ORD_NO, ORD_SEQ) D
		 WHERE A.ORD_NO = Q.ORD_NO
		 AND B.ORD_NO = Q.ORD_NO
		 AND A.ORD_NO = P.ORD_NO
		 AND A.ORD_SEQ = P.ORD_SEQ
		 AND A.ORD_NO = D.ORD_NO(+)
		 AND A.ORD_SEQ = D.ORD_SEQ(+)
		 AND A.ORD_NO = C.ORD_NO(+)
		 AND A.ORD_SEQ = C.ORD_SEQ(+)
		 AND B.ORD_NO NOT LIKE 'TS%'
		 and C.ACT_WGT > 0  
		 AND B.RCVORD_TP NOT IN ('T') --测试订单
		 AND A.DLIV_TP LIKE ?||'%'
		 AND A.ORD_NO||'&' LIKE ?||'%'
		 AND A.SPEC_STL_GRD||'&' LIKE ?||'%'
		 ORDER BY A.ORD_NO, A.ORD_SEQ
		 ]]>
	</query>
	<query id="UIM010280_03.SELECT"  desc="查询可发量对应的钢卷">
		<![CDATA[
		  SELECT C.ORD_NO||c.ord_seq ORD_NO 
		 , DECODE(C.TOT_DEC_GRD,'1','合格')TOT_DEC_GRD
		 , TO_CHAR(to_char(F.ORD_THK,'FM990.099')||'*'||F.ORD_WTH) COIL_THK 
		 , TO_CHAR(to_char(C.COIL_THK,'FM990.099')||'*'||C.COIL_WTH) COIL_WTH 
		 , TO_CHAR(C.COIL_LEN) COIL_LTH
		 , C.ACT_WGT
		 , C.CUR_LOAD_LOC
		 , C.OLD_SAMPL_NO 
		 , C.SHIP_PROG_CD 
		 FROM   (  SELECT * FROM C_TBL02_COIL_COMM 
		 WHERE  COIL_STAT='2'  
		 AND ORD_NO= ?
		 AND ORD_SEQ= ?  
		 AND    (SHIP_PROG_CD IS NULL OR SHIP_PROG_CD ='01' OR SHIP_PROG_CD='02' OR SHIP_PROG_CD='03')
		 ) C
		 ,TBJ01_DLIV_DIR D
		 ,TBJ01_SHIP_DIR E 
		 ,TBA01_ORD_LINE F 
		 ,TBA01_ORD_COMM H
		 WHERE  C.SHIP_DIRNO=E.SHIP_DIRNO(+)  
		 AND    C.DLIV_DIRNO=D.DLIV_DIRNO(+) 
		 AND    C.ORD_NO=F.ORD_NO(+) 
		 AND    C.ORD_NO=H.ORD_NO(+) 
		 AND    C.ORD_SEQ=F.ORD_SEQ(+)
		 ]]>
	</query>
</queryMap>