XinYu_LZMES/.svn/pristine/76/76012909427cad8c7f92bc78fb8ff524bcd3efb6.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="发运计划查询">
	<query id="UIJ030010_01.SELECT"  desc="查询发运计划">
		<![CDATA[
		SELECT '' CHK,
		--(SELECT TBZ00_COMMCD.CD_DESC
		--FROM TBZ00_COMMCD
		--WHERE TBZ00_COMMCD.SM_CD = A.DEST_PCD
		--AND ROWNUM = 1) YWD,  --运往地
		--A.DLIV_DIR_DATE, 		
		SUBSTR(A.ORDERNO, 1, LENGTH(A.ORDERNO) - 3) PACTNO, --合同号
		ORDERNO, --计划号
		A.ISOUT,  --是否出口
		to_char(to_date(A.DLIV_DIR_DATE,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') PLANDATE, --计划时间
		'冷轧线' PL,  --产线
		to_char(to_date(B.SHIP_DIR_DTIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') REALSENDTIME, --实际发货时间
		B.TRANS_CAR_NO,  --车牌号
		A.DLIV_DIRNO,  --出库指示号
		(SELECT CUST_NM
		FROM TBZ00_CUSTOMER
		WHERE CUST_CD = A.CUST_CD
		AND ROWNUM = 1) BUYERCODE, --客户名称
		'承运' TRANSTYPE,   --合同性质
		(SELECT CUST_NM
		FROM TBZ00_CUSTOMER
		WHERE CUST_CD = ORD_CUST_CD
		AND ROWNUM = 1) INCEPTCORPCODE, --收货单位,
		--(SELECT SM_CFNM FROM TBZ00_COMMCD WHERE SM_CD = A.CATEGORY)STL_GRD, --钢种,
		B.SPEC_STL_GRD, --牌号,
		B.ACT_WGT,--重量
		'冷轧厂' LOADPOSITION,--装车点
		B.INSTR_COIL_THK,    --订单厚度
		B.INSTR_COIL_WTH,    --订单宽度
		B.CNT,               -- 总件数
		B.CUR_LOAD_LOC,      --垛位信息
		(SELECT T.MK_CONTENT
          FROM TBE02_ORD_PRC T
         WHERE T.ORD_NO || T.ORD_SEQ = A.ORDERNO) REMARK,
		(SELECT TBZ00_COMMCD.SM_CFNM
		FROM TBZ00_COMMCD
		WHERE TBZ00_COMMCD.SM_CD = A.TRAIN_DLIVNO
		AND ROWNUM = 1) MOTORMANNAME,--承运单位
		(SELECT TBZ00_COMMCD.SM_CFNM
		FROM TBZ00_COMMCD
		WHERE TBZ00_COMMCD.SM_CD = A.DEST_PCD
		AND ROWNUM = 1) STATIONCODE, --到站
		A.BY_SEA_YN,  --是否水运
		--这里通过状态标志位来区分是否火运批车（01为下车02为待批车03为已批车）。这里汽运无批车情况
		DECODE(B.SHIP_PROG_CD,'01','销售下车','02','待批车','03','已批车/排车','04','待装车','05','行车作业结束','06','发货待机','07','发货结束','08','明细发行结束','09','板加移送') SHIP_PROG_CD
		FROM TBJ01_DLIV_DIR A,
		(SELECT MIN(TRANS_CAR_NO) TRANS_CAR_NO,
		MIN(SPEC_STL_GRD) SPEC_STL_GRD,
		SUM(ACT_WGT) ACT_WGT,
		DLIV_DIRNO,
		MIN(SHIP_DIR_DTIME) SHIP_DIR_DTIME,
		MIN(INSTR_COIL_THK) INSTR_COIL_THK,
		MIN(INSTR_COIL_WTH) INSTR_COIL_WTH,
		COUNT(OLD_SAMPL_NO) CNT,
		MIN(SHIP_PROG_CD) SHIP_PROG_CD,
		MIN(CUR_LOAD_LOC) CUR_LOAD_LOC,
		MIN(ORD_NO) ORD_NO
		 FROM C_TBL02_COIL_COMM
		GROUP BY DLIV_DIRNO ) B
		WHERE A.DLIV_DIRNO = B.DLIV_DIRNO
		AND A.DLIV_DIR_DATE between ? and ?  --做主要查询条件
		AND A.DLIV_TP = ?     --  'T' 火车   'C' 汽车
		AND B.ORD_NO LIKE ?||'%'
		AND A.ISDELETED <> '1'
		ORDER BY ACT_WGT
		 ]]>
	</query>
	<query id="UIJ030011_01.SELECT"  desc="查询发运计划">
		<![CDATA[
		SELECT '' CHK,
		--(SELECT TBZ00_COMMCD.CD_DESC
		--FROM TBZ00_COMMCD
		--WHERE TBZ00_COMMCD.SM_CD = A.DEST_PCD
		--AND ROWNUM = 1) YWD,  --运往地
		--A.DLIV_DIR_DATE, 		
		SUBSTR(A.ORDERNO, 1, LENGTH(A.ORDERNO) - 3) PACTNO, --合同号
		ORDERNO, --计划号
		A.ISOUT,  --是否出口
		to_char(to_date(A.DLIV_DIR_DATE,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') PLANDATE, --计划时间
		'冷轧线' PL,  --产线
		to_char(to_date(B.SHIP_DIR_DTIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') REALSENDTIME, --实际发货时间
		B.TRANS_CAR_NO,  --车牌号
		A.DLIV_DIRNO,  --出库指示号
		(SELECT CUST_NM
		FROM TBZ00_CUSTOMER
		WHERE CUST_CD = A.CUST_CD
		AND ROWNUM = 1) BUYERCODE, --客户名称
		'承运' TRANSTYPE,   --合同性质
		(SELECT CUST_NM
		FROM TBZ00_CUSTOMER
		WHERE CUST_CD = ORD_CUST_CD
		AND ROWNUM = 1) INCEPTCORPCODE, --收货单位,
		--(SELECT SM_CFNM FROM TBZ00_COMMCD WHERE SM_CD = A.CATEGORY)STL_GRD, --钢种,
		B.SPEC_STL_GRD, --牌号,
		B.ACT_WGT,--重量
		'冷轧厂' LOADPOSITION,--装车点
		B.INSTR_COIL_THK,    --订单厚度
		B.INSTR_COIL_WTH,    --订单宽度
		B.CNT,               -- 总件数
		B.CUR_LOAD_LOC,      --垛位信息
		(SELECT TBZ00_COMMCD.SM_CFNM
		FROM TBZ00_COMMCD
		WHERE TBZ00_COMMCD.SM_CD = A.TRAIN_DLIVNO
		AND ROWNUM = 1) MOTORMANNAME,--承运单位
		(SELECT TBZ00_COMMCD.SM_CFNM
		FROM TBZ00_COMMCD
		WHERE TBZ00_COMMCD.SM_CD = A.DEST_PCD
		AND ROWNUM = 1) STATIONCODE, --到站
		A.BY_SEA_YN,  --是否水运
		--这里通过状态标志位来区分是否火运批车（01为下车02为待批车03为已批车）。这里汽运无批车情况
		DECODE(B.SHIP_PROG_CD,'01','销售下车','02','待批车','03','已批车/排车','04','待装车','05','行车作业结束','06','发货待机','07','发货结束','08','明细发行结束','09','板加移送') SHIP_PROG_CD
		FROM TBJ01_DLIV_DIR A,
		(SELECT MIN(TRANS_CAR_NO) TRANS_CAR_NO,
		MIN(SPEC_STL_GRD) SPEC_STL_GRD,
		SUM(ACT_WGT) ACT_WGT,
		DLIV_DIRNO,
		MIN(SHIP_DIR_DTIME) SHIP_DIR_DTIME,
		MIN(INSTR_COIL_THK) INSTR_COIL_THK,
		MIN(INSTR_COIL_WTH) INSTR_COIL_WTH,
		COUNT(OLD_SAMPL_NO) CNT,
		MIN(SHIP_PROG_CD) SHIP_PROG_CD,
		MIN(CUR_LOAD_LOC) CUR_LOAD_LOC,
		MIN(ORD_NO) ORD_NO
		 FROM C_TBL02_COIL_COMM
		 WHERE LINE_TP='S'
		GROUP BY DLIV_DIRNO ) B
		WHERE A.DLIV_DIRNO = B.DLIV_DIRNO
		AND A.DLIV_DIR_DATE between ? and ?  --做主要查询条件
		AND A.DLIV_TP = ?     --  'T' 火车   'C' 汽车
		AND B.ORD_NO LIKE ?||'%'
		AND A.ISDELETED <> '1'
		ORDER BY ACT_WGT
		 ]]>
	</query>
	<query id="UIJ030010_02.SELECT"  desc="查询发运计划对应的钢卷信息">
		<![CDATA[
		SELECT    '' CHK,A.COIL_NO,F.C_ORD_INDIA,A.SPEC_STL_GRD,to_char(A.COIL_THK,'FM990.099')||'*'||A.COIL_WTH T_W,to_char(A.INSTR_COIL_THK,'FM990.099')||'*'||A.INSTR_COIL_WTH IT_IW,    
	    DECODE(NVL(A.ACT_WGT,0),0,A.CAL_WGT,A.ACT_WGT) AS ACT_WGT,A.ORD_NO,A.ORD_SEQ,A.CUR_LOAD_LOC,
	    DECODE(A.TOT_DEC_GRD,'1','合格','2','不合格','待判定') as TOT_DEC_GRD,
	    decode(A.CUR_PROG_CD,'CRC','酸轧卷充当待机','DBA','重卷指示待机','DBB','重卷作业待机'
		,'DCA','包装指示待机','DCB','包装作业待机','DED','综合判定待机','DFA','发货指示待机'
		,'DFB','发货待机','CCA','连退作业指示待机','CCB','连退作业待机','CCC','连退卷取待机'
         ) CUR_PROG_CD,
	    D.CHARGE_NO,
	    B.CAS_END_DTIME,
	     A.MILL_DTIME,
	    (SELECT sm_cfnm   FROM TBZ00_COMMCD      WHERE LG_CD = 'A01009'     AND C.DEST_PCD = SM_CD) DEST_PCD_DESC,
	    (select CUST_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = C.CUST_CD and REC_TP='01') CUST_NM, 
	    (select CUST_NM ORD_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = C.ORD_CUST_CD and REC_TP='02') ORD_NM,
	    A.HCOLOUR_REM,
	    A.SPEC_ABBSYM,
	    A.DLIV_DIRNO ,
	    XB_PACKAGE.GF_COMNNAME_FIND('A01004' , A.PRODNM_CD )    PRODNM_CD,
	    C.DLIV_TP,
	    CHKCOIL_CUT(A.OLD_SAMPL_NO,'S') IS_CUT   --添加判断钢卷是否符合订单切边的要求函数 20201212
	    FROM C_TBL02_COIL_COMM A,TBG02_CHARGE_COMM b,TBJ01_DLIV_DIR C,TBG02_CHARGE_COMM_D D,tbh02_coil_comm E,TBA01_ORD_LINE      F
	    WHERE A.H_COIL_NO1 = E.OLD_SAMPL_NO
	    AND substr(E.SLAB_NO,0,10) = D.CHARGE_NO 
	    AND F.ORD_NO = A.ORD_NO
   		AND F.ORD_SEQ = A.ORD_SEQ
	    AND A.DLIV_DIRNO = C.DLIV_DIRNO
	    AND substr(B.CHARGE_NO,0,9) = SUBSTR(D.CHARGE_NO,0,9)
	    AND A.DLIV_DIRNO = ? 
		 ]]>
	</query>
	<query id="UIJ050010_01.SELECT"  desc="查询发运计划">
		<![CDATA[
		SELECT '' CHK,
		--(SELECT TBZ00_COMMCD.CD_DESC
		--FROM TBZ00_COMMCD
		--WHERE TBZ00_COMMCD.SM_CD = A.DEST_PCD
		--AND ROWNUM = 1) YWD,  --运往地
		--A.DLIV_DIR_DATE, 		
		SUBSTR(A.ORDERNO, 1, LENGTH(A.ORDERNO) - 3) PACTNO, --合同号
		ORDERNO, --计划号
		A.ISOUT,  --是否出口
		A.REMARK, --销售备注
		to_char(to_date(A.DLIV_DIR_DATE,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') PLANDATE, --计划时间
		'连退线' PL,  --产线
		to_char(to_date(B.SHIP_DIR_DTIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') REALSENDTIME, --实际发货时间
		B.TRANS_CAR_NO,  --车牌号
		A.DLIV_DIRNO,  --出库指示号
		(SELECT CUST_NM
		FROM TBZ00_CUSTOMER
		WHERE CUST_CD = A.CUST_CD
		AND ROWNUM = 1) BUYERCODE, --客户名称
		'承运' TRANSTYPE,   --合同性质
		(SELECT CUST_NM
		FROM TBZ00_CUSTOMER
		WHERE CUST_CD = ORD_CUST_CD
		AND ROWNUM = 1) INCEPTCORPCODE, --收货单位,
		--(SELECT SM_CFNM FROM TBZ00_COMMCD WHERE SM_CD = A.CATEGORY)STL_GRD, --钢种,
		B.SPEC_STL_GRD, --牌号,
		B.ACT_WGT,--重量
		'冷轧厂' LOADPOSITION,--装车点
		B.INSTR_COIL_THK,    --订单厚度
		B.INSTR_COIL_WTH,    --订单宽度
		B.CNT,               -- 总件数
		B.CUR_LOAD_LOC,      --垛位信息
		(SELECT TBZ00_COMMCD.SM_CFNM
		FROM TBZ00_COMMCD
		WHERE TBZ00_COMMCD.SM_CD = A.TRAIN_DLIVNO
		AND ROWNUM = 1) MOTORMANNAME,--承运单位
		(SELECT TBZ00_COMMCD.SM_CFNM
		FROM TBZ00_COMMCD
		WHERE TBZ00_COMMCD.SM_CD = A.DEST_PCD
		AND ROWNUM = 1) STATIONCODE, --到站
		A.BY_SEA_YN,  --是否水运
		--这里通过状态标志位来区分是否火运批车（01为下车02为待批车03为已批车）。这里汽运无批车情况
		DECODE(B.SHIP_PROG_CD,'01','销售下车','02','待批车','03','已批车/排车','04','待装车','05','行车作业结束','06','发货待机','07','发货结束','08','明细发行结束','09','板加移送') SHIP_PROG_CD
		FROM TBJ01_DLIV_DIR A,
		(SELECT MIN(TRANS_CAR_NO) TRANS_CAR_NO,
		MIN(SPEC_STL_GRD) SPEC_STL_GRD,
		SUM(ACT_WGT) ACT_WGT,
		DLIV_DIRNO,
		MIN(SHIP_DIR_DTIME) SHIP_DIR_DTIME,
		MIN(INSTR_COIL_THK) INSTR_COIL_THK,
		MIN(INSTR_COIL_WTH) INSTR_COIL_WTH,
		COUNT(OLD_SAMPL_NO) CNT,
		MIN(SHIP_PROG_CD) SHIP_PROG_CD,
		MIN(CUR_LOAD_LOC) CUR_LOAD_LOC,
		MIN(ORD_NO) ORD_NO
		 FROM C_TBC02_COIL_COMM
		GROUP BY DLIV_DIRNO ) B
		WHERE A.DLIV_DIRNO = B.DLIV_DIRNO
		AND A.DLIV_DIR_DATE between ? and ?  --做主要查询条件
		AND A.DLIV_TP = ?     --  'T' 火车   'C' 汽车
		AND B.ORD_NO LIKE ?||'%'
		AND A.ISDELETED <> '1'
		ORDER BY ACT_WGT
		 ]]>
	</query>
	
	<query id="UIJ050010_02.SELECT"  desc="查询发运计划对应的钢卷信息">
		<![CDATA[
		SELECT    '' CHK,F.C_ORD_INDIA,A.OLD_SAMPL_NO,A.SPEC_STL_GRD,to_char(A.COIL_THK,'FM990.099')||'*'||A.COIL_WTH T_W,to_char(A.INSTR_COIL_THK,'FM990.099')||'*'||A.INSTR_COIL_WTH IT_IW,    
	    DECODE(NVL(A.ACT_WGT,0),0,A.CAL_WGT,A.ACT_WGT) AS ACT_WGT,A.ORD_NO,A.ORD_SEQ,A.CUR_LOAD_LOC,
	    DECODE(A.TOT_DEC_GRD,'1','合格','2','不合格','待判定') as TOT_DEC_GRD,
	    decode(A.CUR_PROG_CD,'CRC','酸轧卷充当待机','DBA','重卷指示待机','DBB','重卷作业待机'
		,'DCA','包装指示待机','DCB','包装作业待机','DED','综合判定待机','DFA','发货指示待机'
		,'DFB','发货待机','CCA','连退作业指示待机','CCB','连退作业待机','CCC','连退卷取待机'
         ) CUR_PROG_CD,
	    D.CHARGE_NO,
	    B.CAS_END_DTIME,
	     A.MILL_DTIME,
	    (SELECT sm_cfnm   FROM TBZ00_COMMCD      WHERE LG_CD = 'A01009'     AND C.DEST_PCD = SM_CD) DEST_PCD_DESC,
	    (select CUST_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = C.CUST_CD and REC_TP='01') CUST_NM, 
	    (select CUST_NM ORD_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = C.ORD_CUST_CD and REC_TP='02') ORD_NM,
	    A.HCOLOUR_REM,
	    A.SPEC_ABBSYM,
	    A.DLIV_DIRNO ,
	    XB_PACKAGE.GF_COMNNAME_FIND('A01004' , A.PRODNM_CD )    PRODNM_CD,
	    C.DLIV_TP,
	    decode(a.PACKAGE_LEVEL,1,'不包装',2,'简易包装',3,'普通包装',4,'精包装') PKGLEL,--包装等级(1-不包;2-简包;3-普包;4-精包)
       	PKG_QUALITY_COMM.FZ00_COMM('A01010', f.PAKMTH_TP) PAKMTH_TP,
	    E.C_ORD_THK_MIN ,
	    E.C_ORD_THK_MAX ,
	    E.C_ORD_WTH_MIN ,
	    E.C_ORD_WTH_MAX,
	    CHKCOIL_CUT(A.OLD_SAMPL_NO,'L') IS_CUT   --添加判断钢卷是否符合订单切边的要求函数 20201212
	    FROM C_TBC02_COIL_COMM A,TBG02_CHARGE_COMM b,TBJ01_DLIV_DIR C,TBG02_CHARGE_COMM_D D,TBE02_ORD_PRC E,TBA01_ORD_LINE      F
	    WHERE 
	      E.ORD_NO=A.ORD_NO  
	    AND E.ORD_SEQ=A.ORD_SEQ
	    AND F.ORD_NO = A.ORD_NO
   		AND F.ORD_SEQ = A.ORD_SEQ
	    AND substr(A.SLAB_NO,0,10) = D.CHARGE_NO
	    AND A.DLIV_DIRNO = C.DLIV_DIRNO
	    AND substr(B.CHARGE_NO,0,9) = SUBSTR(D.CHARGE_NO,0,9)
	    AND A.DLIV_DIRNO = ? 
		 ]]>
	</query>
</queryMap>