XinYu_RZMES/.svn/pristine/1a/1adb43e9e2b4909b7477ed422dc14a88d41b2ef0.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="SHIPPING">
	<query id="FAHUO00_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_BJ
           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="FAHUO00_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_BJ     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="FAHUO00_03.select" desc=" 预装清除钢卷公共表 " fetchSize="10">
        <![CDATA[
			UPDATE TBH02_COIL_COMM_BJ 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="FAHUO00_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="FAHUO00_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_BJ     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>