XinYu_RZMES/.svn/pristine/c0/c0128c3c7a63c50348bbebeb3fb332fc3af2ae8d.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
	<query id="UIJ010041_01.select" desc=" 查询运送指示信息 " fetchSize="10">
		<![CDATA[
/*********************************************************************************************
SELECT '' CHK,
       B.SHIP_DIRNO,
       B.GET_ON_PCD,
       B.DEST_PCD,
       B.EXLV_LINE_CD,
       B.GET_OFF_PCD,
       B.DLIV_TP,
       D.SM_CFNM AS DLIV_TP_NM,
       B.SHIP_COMP_CD,
       C.SHIP_COMP_NM SM_CFNM,
       B.TRAIN_DLIVNO,
       B.DLIV_DIRNO,
       NVL(B.CNT,0) AS CNT,
       NVL(B.ACT_WGT,0) AS ACT_WGT,
       B.ALLOC_SEQ,
       B.TRANS_CAR_NO,
	   B.LANE_TP,
	   B.ENTERANCE_SEQ  
FROM(SELECT *
     FROM TBJ01_SHIP_DIR
     WHERE 1 = 1
     AND SHIP_PROG_CD = :1               -- SHIP_PROG_CD = '03':瞒樊硅瞒措扁
     AND SHIP_DIRNO BETWEEN NVL(:2,'00000101')||'0000' AND NVL(:3,'99991231')||'9999'
     AND SHIP_DIRNO LIKE :4||'%') A       
   ,(SELECT X.*, Y.CNT CNT, Y.ACT_WGT ACT_WGT
     FROM TBJ01_DLIV_DIR X
        ,(SELECT DLIV_DIRNO
               , COUNT(*)            AS CNT
               , NVL(SUM(ACT_WGT),0) AS ACT_WGT
          FROM TBH02_COIL_COMM
          WHERE 1 = 1
          AND CUR_PROG_CD like :5||'%'            -- CUR_PROG_CD = 'SFB'(款价措扁)
          AND SHIP_DIRNO BETWEEN NVL(:6,'00000101')||'0000' AND NVL(:7,'99991231')||'9999'
          GROUP BY DLIV_DIRNO) Y
     WHERE X.DLIV_DIRNO = Y.DLIV_DIRNO
     AND X.DLIV_TP LIKE :8||'%') B
   ,(SELECT *
     FROM TBJ00_SHIP_COMP
     WHERE SHIP_COMP_CD LIKE :9||'%') C
   ,(SELECT SM_CD, SM_CFNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01012') D
WHERE A.SHIP_DIRNO = B.SHIP_DIRNO(+)
AND B.SHIP_COMP_CD = C.SHIP_COMP_CD
AND D.SM_CD = B.DLIV_TP
AND B.DLIV_DIR_DATE BETWEEN NVL(:10,' ') AND NVL(:11,' ')
AND NVL(A.SHIP_DIRNO,' ') LIKE :12||'%'
AND NVL(B.DLIV_TP,' ') LIKE :13||'%'
AND NVL(B.SHIP_COMP_CD,' ') LIKE :14||'%'
AND NVL(B.LANE_TP,' ') LIKE :15||'%'
AND B.ENTERANCE_SEQ LIKE :16||'%'
*******************************************************************************************************/
SELECT '' CHK,
       B.SHIP_DIRNO,
       B.GET_ON_PCD,
       B.DEST_PCD,
	   (SELECT sm_cfnm   FROM TBZ00_COMMCD      WHERE LG_CD = 'A01009'     AND b.DEST_PCD = SM_CD) DEST_PCD_DESC,
       B.EXLV_LINE_CD,
	   (SELECT x.SM_CFNM   FROM TBZ00_COMMCD   x   WHERE LG_CD = 'A01015' AND b.EXLV_LINE_CD = SM_CD) EXLV_LINE_CD_DESC,
	   (select CUST_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = b.CUST_CD and REC_TP='01') CUST_NM, 
       (select CUST_NM ORD_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = b.ORD_CUST_CD and REC_TP='02') ORD_NM,
       B.GET_OFF_PCD,
       B.DLIV_TP,
       D.SM_CFNM AS DLIV_TP_NM,
       B.SHIP_COMP_CD,
       C.SHIP_COMP_NM SM_CFNM,
       B.TRAIN_DLIVNO,
       B.DLIV_DIRNO,
       NVL(B.CNT,0) AS CNT,
       NVL(B.ACT_WGT,0) AS ACT_WGT,
       B.ALLOC_SEQ,
       B.TRANS_CAR_NO,
       B.LANE_TP,
       B.ENTERANCE_SEQ,
	   B.ORD_NO,
       B.ORD_SEQ,
       B.SPEC_STL_GRD,
       B.INSTR_COIL_THK||'*'||B.INSTR_COIL_WTH T_W  ,
       Z.MK_COIL   
FROM(SELECT *
     FROM TBJ01_SHIP_DIR
     WHERE 1 = 1
     AND SHIP_PROG_CD = :1               -- SHIP_PROG_CD = '03':?樊硅?措扁
     AND SHIP_DIRNO BETWEEN NVL(:2,'00000101')||'0000' AND NVL(:3,'99991231')||'9999'
     AND SHIP_DIRNO LIKE :4||'%') A       
   ,(SELECT X.*, Y.CNT CNT, Y.ACT_WGT ACT_WGT,Y.ORD_NO,Y.ORD_SEQ,Y.SPEC_STL_GRD,Y.INSTR_COIL_THK,Y.INSTR_COIL_WTH 
     FROM TBJ01_DLIV_DIR X
        ,(SELECT DLIV_DIRNO
               , COUNT(*)            AS CNT
               , NVL(SUM(ACT_WGT),0) AS ACT_WGT 
			   , Max(ORD_NO)         AS ORD_NO
               , Min(ORD_SEQ)        AS ORD_SEQ
               , MAX(SPEC_STL_GRD)   AS SPEC_STL_GRD
               , MAX(INSTR_COIL_THK) AS INSTR_COIL_THK
               , MAX(INSTR_COIL_WTH) AS INSTR_COIL_WTH
          FROM TBH02_COIL_COMM
          WHERE 1 = 1
          AND CUR_PROG_CD like :5||'%'            -- CUR_PROG_CD = 'SFB'(款价措扁)
          AND SHIP_DIRNO BETWEEN NVL(:6,'00000101')||'0000' AND NVL(:7,'99991231')||'9999'
          GROUP BY DLIV_DIRNO) Y
     WHERE X.DLIV_DIRNO = Y.DLIV_DIRNO
     AND X.DLIV_TP LIKE :8||'%'
     AND NVL(X.LANE_TP,'*') LIKE :9||'%'
     AND NVL(X.ENTERANCE_SEQ,0) = NVL(:10,0)) B
   ,(SELECT *
     FROM TBJ00_SHIP_COMP
     WHERE SHIP_COMP_CD LIKE :11||'%') C
   ,(SELECT SM_CD, SM_CFNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01012' AND SM_CD LIKE :12||'%') D
   ,TBA01_ORD_LINE Z
WHERE A.SHIP_DIRNO = B.SHIP_DIRNO(+)
AND B.SHIP_COMP_CD = C.SHIP_COMP_CD(+)
AND B.ORD_NO=Z.ORD_NO
AND B.ORD_SEQ=Z.ORD_SEQ
AND D.SM_CD = B.DLIV_TP 
AND B.ISDELETED = 0 
AND B.ISCANCEL = 0
ORDER BY B.ALLOC_SEQ,B.DLIV_DIRNO 
		]]>
	</query>
	<query id="UIJ010041_02.select" desc=" 这里的发货状态同一批次一起改变，所以取钢卷公共表中的状态 " fetchSize="10">
		<![CDATA[
SELECT '' CHK,
	   A.OLD_SAMPL_NO,
       A.COIL_NO,
       DECODE(A.TOT_DEC_GRD,'1','合格','2','次品',' ') TOT_DEC_GRD,
	   DECODE(A.MATRL_END_CAUSE_CD,'1','合格','2','次品',' ') MATRL_END_CAUSE_CD,
       a.instr_coil_wth COIL_WTH,
       a.instr_coil_thk COIL_THK,
	   a.ORD_NO,
       a.ORD_SEQ,
	   a.spec_stl_grd,
       A.COIL_OUTDIA,
       NVL(A.ACT_WGT,0) ACT_WGT,
       NVL(A.CAL_WGT,0) CAL_WGT,
       A.CUR_LOAD_LOC,
       B.DEST_PCD,
       B.ALLOC_SEQ,
	   B.TRANS_CAR_NO,
       B.DLIV_DIRNO,
	   B.SHIP_DIRNO,
	   (SELECT sm_cfnm   FROM TBZ00_COMMCD      WHERE LG_CD = 'A01009'     AND b.DEST_PCD = SM_CD) DEST_PCD_DESC,
       (SELECT x.SM_CFNM   FROM TBZ00_COMMCD   x   WHERE LG_CD = 'A01015'     AND b.EXLV_LINE_CD = SM_CD) EXLV_LINE_CD_DESC,
	   (select CUST_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = B.CUST_CD and REC_TP='01') CUST_NM, 
       (select CUST_NM ORD_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = B.ORD_CUST_CD and REC_TP='02') ORD_NM,
	   A.HCOLOUR_REM    
FROM TBH02_COIL_COMM A,TBJ01_DLIV_DIR B,TBJ01_SHIP_DIR C
WHERE A.DLIV_DIRNO = B.DLIV_DIRNO
AND B.SHIP_DIRNO = C.SHIP_DIRNO
AND A.SHIP_PROG_CD = '04'
AND B.DLIV_TP  = ? 
ORDER BY B.ALLOC_SEQ
		]]>
	</query>
	<query id="UIJ010041_03.select" desc="  " fetchSize="10">
		<![CDATA[
			SELECT SLAB_NO,BEF_PROG_CD,ORD_NO,ORD_SEQ,ACT_WGT FROM tbh02_coil_comm WHERE OLD_SAMPL_NO = ?
		]]>
	</query>
	<query id="UIJ010041_init_01" desc=" 初始化运输方式 " fetchSize="10">
		<![CDATA[
			SELECT SM_CD VALUE, SM_CFNM LABEL FROM TBZ00_COMMCD WHERE LG_CD = 'A01012'
		]]>
	</query>
	<query id="UIJ010041_init_02" desc=" 初始化运输单位 " fetchSize="10">
		<![CDATA[
			/***** select a.sm_cfnm LABEL,a.sm_cd VALUE from tbz00_commcd a where lg_cd = 'J01002'  *****/
            SELECT SHIP_COMP_NM AS LABEL, SHIP_COMP_CD AS VALUE
			FROM TBJ00_SHIP_COMP
			ORDER BY 1
			/***** ORDER BY 1 : SHIP_COMP_NM ASC
			 ***** ORDER BY 2 : SHIP_COMP_CD ASC
			 *****/
		]]>
	</query>
	<query id="UIJ010041_select_ship" desc=" 查询钢卷公共表中的SHIP_PROG_CD状态。看是否全部改变 " fetchSize="10">
		<![CDATA[
			select a.SHIP_PROG_CD from tbh02_coil_comm a  where a.ship_dirno = ? 
		]]>
	</query>
	<query id="UIJ010041_select_loc" desc=" 查询钢卷公共表中的垛位来区分是否退账面 " fetchSize="10">
		<![CDATA[
			select a.CUR_LOAD_LOC from tbh02_coil_comm a  where a.OLD_SAMPL_NO = ? 
		]]>
	</query>
	<query id="UIJ010041_01.update" desc=" 只更新排车顺序和车厢号 " fetchSize="10">
		<![CDATA[
			update tbj01_dliv_dir
			set 
			ALLOC_SEQ = ?,
			TRANS_CAR_NO = ? 
			where DLIV_DIRNO = ? 
		]]>
	</query>
	<query id="UIJ010041_02.update" desc=" 更新钢卷公共表,把进程屏蔽。跳过行车 " fetchSize="10">
		<![CDATA[
			update TBH02_COIL_COMM
			set 
			TRANS_CAR_NO = ?,
			TRAIN_STEP_NO = ?,
			DLIV_DIRNO = ?,
			SHIP_DIRNO = ?,
			ACT_WGT    = ?,
			SHIP_PROG_CD = '06',
			CUR_LOAD_LOC = 'C'    
			--CUR_PROG_CD = 'SFB',
			--CUR_PROG_CD_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
			--CUR_PROG_CD_PGM = 'UIJ010040',
			--BEF_PROG_CD = CUR_PROG_CD,
			--BEF_PROG_CD_DTIME = CUR_PROG_CD_DTIME,
			--BEF_PROG_CD_PGM = CUR_PROG_CD_PGM 
			where OLD_SAMPL_NO =? 
			
		]]>
	</query>
	<query id="UIJ010041_03.update" desc=" 更新发货表的状态标志字段3-4 " fetchSize="10">
		<![CDATA[
			update tbj01_ship_dir
			set 
			ship_prog_cd = '04' 
			where ship_dirno =?
			
		]]>
	</query>
	<query id="ExecuteProcedure_NIB029020" desc="" fetchSize="10">
		<![CDATA[				
		call NIB029021(:1, :2, :3, :4)
		/*******
		 :1 - P_SHIP_DIRNO
		 :2 - P_CRET_NO
		 :3 - P_RETURN_CD
		 :4 - P_RETURN_MSG
		 *******/
		]]>
	</query>
	<query id="UIJ010041_05.update" desc=" 更新发货表的状态标志字段4-5 " fetchSize="10">
		<![CDATA[
			update tbj01_ship_dir
			set 
			ship_prog_cd = '05' 
			where ship_dirno =?
			
		]]>
	</query>
	<query id="UIJ010041_04.update" desc=" 更新钢卷公共表中的车辆号和排车顺序号 " fetchSize="10">
		<![CDATA[
			update TBH02_COIL_COMM
			set 
			TRAIN_STEP_NO = ?,
			TRANS_CAR_NO = ?,
			SHIP_PROG_CD = '04'   
			where DLIV_DIRNO =?
			
		]]>
	</query>
	<query id="UIJ010041_06.update" desc=" 只更新排车顺序和车厢号 " fetchSize="10">
		<![CDATA[
			update tbj01_dliv_dir
			set 
			ALLOC_SEQ = ?,
			TRANS_CAR_NO = ? 
			where DLIV_DIRNO = ? 
		]]>
	</query>
	<query id="UIJ010041_07.update" desc=" 只更重量 " fetchSize="10">
		<![CDATA[
	   UPDATE TBH02_COIL_COMM T
       SET T.ACT_WGT =
       (SELECT X.ACT_WGT
          FROM (SELECT A.ACT_WGT, A.DEAL_TIME,A.OLD_SAMPL_NO
                  FROM TBH05_COIL_WEIGHT A
                 ORDER BY A.CSJ DESC) X
         WHERE ROWNUM = 1
         AND X.OLD_SAMPL_NO=T.OLD_SAMPL_NO)
         WHERE T.OLD_SAMPL_NO = ?
		]]>
	</query>
	<query id="UIJ010041_08.update" desc=" 清除冷轧垛位表垛位 " fetchSize="10">
		<![CDATA[
	   update c_tbk08_coil_yard t set t.COIL_NO='' where t.COIL_NO=?
		]]>
	</query>
	<query id="UIJ010041_09.update" desc=" 清除冷轧垛位 " fetchSize="10">
		<![CDATA[
	    UPDATE C_TBK02_COIL_COMM T
   			SET T.CUR_LOAD_LOC      = '',
       			T.CUR_PROG_CD_PGM   = 'UIJ010041',
       			T.CUR_PROG_CD_DTIME = TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
 		WHERE T.OLD_SAMPL_NO = ?
		]]>
	</query>
</queryMap>