XinYu_RZMES/.svn/pristine/d9/d9ae5860b49c30f75ae58b2b31683cdcc10412b4.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="SHIPPING">
	<query id="FAHUO_01.select" desc="  " fetchSize="10">
		<![CDATA[
 	   SELECT 'N' 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.DLIV_DIR_DATE PLANDATE, --计划时间
           '热轧线' PL,  --产线
           (SELECT T.SM_CFNM
           FROM tbz00_commcd T
           WHERE T.LG_CD = 'A01004'
           AND T.SM_CD = B.PRDNM_CD) PRODNM_CD,--产品类型
           B.SHIP_DIR_DTIME 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,
               PRDNM_CD
            FROM TBH02_COIL_COMM
           GROUP BY DLIV_DIRNO,PRDNM_CD,PRODNM_CD) B
       WHERE A.DLIV_DIRNO = B.DLIV_DIRNO
         AND A.DLIV_DIR_DATE = ?  --做主要查询条件
         AND A.DLIV_TP = ?     --  'T' 火车   'C' 汽车
         AND B.ORD_NO LIKE ?||'%'
         AND A.ISDELETED <> '1'
     ORDER BY ACT_WGT
        ]]>
	</query>
	 <query id="FAHUO_02.select" desc="  " fetchSize="10">
        <![CDATA[
SELECT    '' CHK,A.OLD_SAMPL_NO,A.SPEC_STL_GRD,A.COIL_THK||'*'||A.COIL_WTH T_W,A.INSTR_COIL_THK||'*'||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,'SED','判定待机','SRC','充当待机','SFA','发货待机','SFB','运送待机','SFF','发货完成',null) 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
          FROM TBH02_COIL_COMM A,TBG02_CHARGE_COMM b,TBJ01_DLIV_DIR C,TBG02_CHARGE_COMM_D D 
          WHERE 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>
	<query id="FAHUO_03.select" desc=" 预装清除钢卷公共表 " fetchSize="10">
        <![CDATA[
			UPDATE TBH02_COIL_COMM A SET 
				   A.BEF_LOAD_LOC = A.CUR_LOAD_LOC,
				   A.BEF_LOAD_LOC_DTIME = A.CUR_LOAD_LOC_DTIME,
				   A.CUR_LOAD_LOC = 'YZ',
				   A.CUR_LOAD_LOC_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
				   WHERE A.OLD_SAMPL_NO = ? 
        ]]>
    </query>
	<query id="FAHUO_04.select" desc=" 预装清除钢卷垛位表 " fetchSize="10">
        <![CDATA[
			UPDATE TBH05_COIL_YARD A SET 
				   A.COIL_NO = '',
				   A.REG_ID = '',
				   A.REG_DTIME = ''
				   
				   WHERE A.COIL_NO = ? 
        ]]>
    </query>
    <query id="FAHUO_05.select" desc="  " fetchSize="10">
        <![CDATA[
SELECT    '' CHK,A.OLD_SAMPL_NO,A.SPEC_STL_GRD,A.COIL_THK||'*'||A.COIL_WTH T_W,A.INSTR_COIL_THK||'*'||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,'SED','判定待机','SRC','充当待机','SFA','发货待机','SFB','运送待机','SFF','发货完成',null) 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
          FROM TBH02_COIL_COMM A,TBG02_CHARGE_COMM b,TBJ01_DLIV_DIR C,TBG02_CHARGE_COMM_D D 
          WHERE 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 in (SELECT COLUMN_VALUE FROM TABLE(SELECT xb_package.GF_TABSTR(:1,'|') from dual))  
                order by a.DLIV_DIRNO,A.OLD_SAMPL_NO
        ]]>
    </query>
</queryMap>