XinYu_RZMES/.svn/pristine/6a/6a29817fed4bd34d91d3ca2ec02e5822fc4fdc43.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
	<query id="UIH051000_01.select" desc=" 入库查询，这里需要查询入库的历史记录 " fetchSize="10">
        <![CDATA[
      SELECT SUBSTR(A.YARD_ENTRY_DTIME,1,8) YARD_ENTRY_DTIME                --入库时间
			 ,XB_PACKAGE.GF_COMNNAME_FIND('A01004' ,D.PRDNM_CD )  PRODNM_CD
             ,A.YARD_ENTRY_USE_TIME CZSJ                                    --操作时间
             ,A.YARD_ENTRY_SHIFT SHIFT                                      --班次
             ,A.YARD_ENTRY_GROUP WKGROUP                                    --班组
             ,A.YARD_ENTRY_REG CZR                                          --操作人
             ,SUBSTR(A.OLD_SAMPL_NO,1,10)ZPH                    --轧批号
             ,A.OLD_SAMPL_NO
             ,A.SPEC_STL_GRD
             ,A.INSTR_COIL_THK ORD_THK 
             ,A.INSTR_COIL_WTH ORD_WTH 
             ,A.COIL_THK COIL_THK
             ,A.COIL_WTH  COIL_WTH 
             ,A.ACT_WGT
             ,A.CUR_LOAD_LOC             
             ,decode(A.TOT_DEC_GRD,'1','合格','2','不合格','3','外观异常','4','废品') TOT_DEC_GRD  --综合判定
             ,decode(A.INGR_DEC_GRD,'0','综合判定取消','1','合格','2','不合格')   INGR_DEC_GRD                       --成份判定
             ,decode(A.MATLQLTY_DEC_GRD,'0','无性能要','1','合格','2','不合格')  MATLQLTY_DEC_GRD                --材质判定
             ,decode(A.SIZE_DEC_RST,'1','合格','2','订单外合格','3','不合格','4','废品')      SIZE_DEC_RST                    --尺寸判定
             ,decode(A.EXTSHAPE_DEC_GRD,'1','合格','2','订单外合格','3','次品','4','废品','待判定') EXTSHAPE_DEC_GRD  --外观判定
             ,DECODE(A.WGT_DEC_RST,'1','合格','2','不合格') WGT_DEC_RST
             ,DECODE(A.QK_YN,'Y','清库卷') QK_YN  
             ,A.ORD_NO
             ,a.ORD_SEQ 
             ,(SELECT T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD = 'A01012' AND T.SM_CD = C.DLIV_TP) DLIV_TP
             ,(SELECT T.CUST_NM FROM TBZ00_CUSTOMER T WHERE T.CUST_CD = D.ORDCUST_CD) ORDCUST_CD    
             ,SUBSTR(B.SLAB_NO,1,10) CHARGE_NO
             ,B.STL_GRD
             ,a.TRNF_DTIME
             ,A.TRANS_CAR_NO
             ,A.DLIV_DIRNO
       ,DECODE(A.COIL_DEFECTIVE,'1A',null,'2A','成分不合格','2E','工序不合格','3B','尺寸不合格','3C','重量不合格','3D','外观不合格','4A','性能不合格') COIL_DEFECTIVE 
             ,A.BEF_SPEC_STL_GRD
         ,DECODE(A.BEF_MATLQLTY_DEC_GRD,'0','无性能要求','1','合格','2','不合格') BEF_MATRL_END_CAUSE_CD  --清库前性能
         ,DECODE(A.BEF_EXTSHAPE_DEC_GRD,'1','合格','2','订单外合格','3','次品','4','废品') BEF_EXTSHAPE_DEC_GRD --清库前外观
		 ,C.XSQY --销售区域  
          FROM TBH02_COIL_COMM A
           ,TBG02_SLAB_COMM B
           ,TBA01_ORD_LINE C
           ,TBA01_ORD_COMM D
          WHERE '1' = '1'
          AND   A.SLAB_NO = B.SLAB_NO(+)
          AND   A.ORD_NO = C.ORD_NO(+)
          AND   A.ORD_SEQ = C.ORD_SEQ(+)
          AND   A.ORD_NO = D.ORD_NO(+)
          AND   (SUBSTR(A.YARD_ENTRY_DTIME,1,8) >= :1)
          AND   (SUBSTR(A.YARD_ENTRY_DTIME,1,8) <= :2)
          AND   A.OLD_SAMPL_NO LIKE :3||'%'
          AND   A.COIL_THK >= NVL(:4,'000')
          AND   A.COIL_THK <= NVL(:5,'999')
          AND   A.COIL_WTH >= NVL(:6,'00000')
          AND   A.COIL_WTH <= NVL(:7,'99999')
          AND   (A.CUR_LOAD_LOC LIKE :8||'%' OR A.CUR_LOAD_LOC IS NULL)
          AND   A.SPEC_STL_GRD LIKE :9||'%'
          AND   A.YARD_ENTRY_SHIFT||'&' LIKE :10||'%'
          AND   A.YARD_ENTRY_GROUP||'&' LIKE :11||'%'
          AND   A.YARD_ENTRY_REG||'&' LIKE :12||'%'
          AND   A.TRANS_CAR_NO||'&' LIKE :13||'%' 
          AND   A.ORD_NO||'&' LIKE :14||'%'
          AND   A.ORD_SEQ||'&' LIKE :15||'%'
          AND   A.DLIV_DIRNO||'&' LIKE :l6||'%' 
          AND   D.ORDCUST_CD||'&' LIKE :17||'%'  
          AND   A.DLIV_TP||'&' LIKE :18||'%'
          AND   A.COIL_DEFECTIVE||'&' LIKE :19||'%'
          AND   A.ORD_FL||'&' LIKE :20||'%'
          AND   A.QK_YN||'&'  LIKE :21||'%' 
		  AND   INSTR(NVL(:22 , NVL(D.PRDNM_CD,'NULL')) , NVL(D.PRDNM_CD,'NULL')) > 0
		  AND   C.XSQY||'&' like :23||'%'
          --AND   A.COIL_STAT = '2'
          and   a.SPM_YN||'&' like :24||'%'
		  AND   A.COIL_IN_REG IS NULL    -- ADD BY JUNGAN 2010-12-29 不查询盘入记录
          order by a.OLD_SAMPL_NO desc 
        ]]>
    </query> 
	<query id="UIH051000_03.select" desc=" 出库查询 " fetchSize="10">
	       <![CDATA[
			SELECT SUBSTR(A.YARD_ENTRY_DTIME,1,8) YARD_ENTRY_DTIME          --入库时间
				   ,XB_PACKAGE.GF_COMNNAME_FIND('A01004' ,D.PRDNM_CD )  PRODNM_CD
			       ,A.TRNF_USE_TIME CZSJ                                    --操作时间
			       ,A.TRNF_SHIFT SHIFT                                      --班次
				   ,A.TRNF_GROUP WKGROUP                                    --班组
				   ,A.TRNF_REG CZR                                          --操作人
                   ,SUBSTR(A.OLD_SAMPL_NO,1,10)ZPH                    --轧批号
           		   ,A.OLD_SAMPL_NO
				   ,A.SPEC_STL_GRD
				   ,A.INSTR_COIL_THK ORD_THK 
             	   ,A.INSTR_COIL_WTH ORD_WTH 
             	   ,A.COIL_THK COIL_THK
             	   ,A.COIL_WTH  COIL_WTH 
					,A.ACT_WGT
				    ,A.CUR_LOAD_LOC             
				    ,decode(A.TOT_DEC_GRD,'1','合格','2','不合格','3','外观异常','4','废品') TOT_DEC_GRD  --综合判定
				     ,decode(A.INGR_DEC_GRD,'1','合格','2','不合格')   INGR_DEC_GRD                       --成份判定
					   ,decode(A.MATLQLTY_DEC_GRD,'1','合格','2','不合格')  MATLQLTY_DEC_GRD                --材质判定
					   ,decode(A.SIZE_DEC_RST,'1','合格','2','不合格')      SIZE_DEC_RST                    --尺寸判定
					   ,decode(A.EXTSHAPE_DEC_GRD,'1','合格','2','订单外合格','3','次品','4','废品','待判定') EXTSHAPE_DEC_GRD  --外观判定
			           ,DECODE(A.WGT_DEC_RST,'1','合格','2','不合格') WGT_DEC_RST
					   ,DECODE(A.QK_YN,'Y','清库卷') QK_YN	
			           ,A.ORD_NO
					   ,a.ORD_SEQ 
					   ,(SELECT T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD = 'A01012' AND T.SM_CD = C.DLIV_TP) DLIV_TP
					   ,(SELECT T.CUST_NM FROM TBZ00_CUSTOMER T WHERE T.CUST_CD = D.ORDCUST_CD) ORDCUST_CD    
					   ,SUBSTR(B.SLAB_NO,1,10) CHARGE_NO
					   ,B.STL_GRD
				       ,a.TRNF_DTIME
			           ,A.TRANS_CAR_NO
                       ,A.DLIV_DIRNO
			           ,DECODE(A.COIL_DEFECTIVE,'1A',null,'2A','成分不合格','2E','工序不合格','3B','尺寸不合格','3C','重量不合格','3D','外观不合格','4A','性能不合格') COIL_DEFECTIVE 
                       ,A.BEF_SPEC_STL_GRD
			           ,DECODE(A.BEF_MATLQLTY_DEC_GRD,'0','无性能要求','1','合格','2','不合格') BEF_MATRL_END_CAUSE_CD  --清库前性能
		               ,DECODE(A.BEF_EXTSHAPE_DEC_GRD,'1','合格','2','订单外合格','3','次品','4','废品') BEF_EXTSHAPE_DEC_GRD --清库前外观 
			           ,C.XSQY --销售区域    
					FROM TBH02_COIL_COMM A
					 ,TBG02_SLAB_COMM B
					 ,TBA01_ORD_LINE C
					 ,TBA01_ORD_COMM D
					WHERE '1' = '1'
					AND   A.SLAB_NO = B.SLAB_NO(+)
					AND   A.ORD_NO = C.ORD_NO(+)
					AND   A.ORD_SEQ = C.ORD_SEQ(+)
					AND   A.ORD_NO = D.ORD_NO(+)
					AND   (SUBSTR(A.TRNF_DTIME,1,8) >= :1) --or (a.QK_YN = 'Y' and SUBSTR(a.QK_REG_DTIME,1,8)>= :2)) 
					AND   (SUBSTR(A.TRNF_DTIME,1,8) <= :2) --or (a.QK_YN = 'Y' and SUBSTR(a.QK_REG_DTIME,1,8)<= :4))
					and   a.COIL_STAT = '3'
					AND   nvl(a.ISDIVI,1) <> '0'
			        AND   A.OLD_SAMPL_NO LIKE :3||'%'
					AND   A.COIL_THK >= NVL(:4,'000')
                    AND   A.COIL_THK <= NVL(:5,'999')
                    AND   A.COIL_WTH >= NVL(:6,'00000')
                    AND   A.COIL_WTH <= NVL(:7,'99999')
					AND   (A.CUR_LOAD_LOC LIKE :8||'%' OR A.CUR_LOAD_LOC IS NULL)
					AND   A.SPEC_STL_GRD LIKE :9||'%'
				    AND   A.TRNF_SHIFT||'&' LIKE :10||'%'
					AND   A.TRNF_GROUP||'&' LIKE :11||'%'
					AND   A.TRNF_REG||'&' LIKE :12||'%'
				    AND   A.TRANS_CAR_NO||'&' LIKE :13||'%' 
                    AND   A.ORD_NO||'&' LIKE :14||'%'
                    AND   A.ORD_SEQ||'&' LIKE :15||'%'
                    AND   A.DLIV_DIRNO||'&' LIKE :l6||'%'
			        AND   D.ORDCUST_CD||'&' LIKE :17||'%' 
			        AND   A.DLIV_TP||'&' LIKE :18||'%'
			        AND   A.COIL_DEFECTIVE||'&' LIKE :19||'%' 
			        AND   A.ORD_FL||'&' LIKE :20||'%'
                    AND   A.QK_YN||'&'  LIKE :21||'%'
				    AND   INSTR(NVL(:22 , NVL(D.PRDNM_CD,'NULL')) , NVL(D.PRDNM_CD,'NULL')) > 0
			        AND   C.XSQY||'&' like :23||'%'
			        and   a.SPM_YN||'&' like :24||'%'
					AND   (A.SHIP_PROG_CD <> '10' or A.SHIP_PROG_CD IS NULL)   -- ADD BY JUNGAN 2010-12-29 不查询盘出记录
				    order by a.OLD_SAMPL_NO desc 
        ]]>
    </query> 
	<query id="UIH051000_02.select" desc=" 统计入库钢卷个数和重量 " fetchSize="10">
		<![CDATA[
      select count(*) COIL_CNT,
             nvl(sum(a.act_wgt)/1000,0) COIL_WGT 
               FROM TBH02_COIL_COMM A,
                    TBA01_ORD_LINE C,
                    TBA01_ORD_COMM D  
                WHERE '1' = '1' 
					AND   A.ORD_NO = D.ORD_NO(+)
					AND   A.ORD_NO = C.ORD_NO(+)
					AND   A.ORD_SEQ = C.ORD_SEQ(+)
					AND   (SUBSTR(A.YARD_ENTRY_DTIME,1,8) >= :1)
					AND   (SUBSTR(A.YARD_ENTRY_DTIME,1,8) <= :2)
					AND   A.OLD_SAMPL_NO LIKE :3||'%'
					AND   A.COIL_THK >= NVL(:4,'000')
                    AND   A.COIL_THK <= NVL(:5,'999')
                    AND   A.COIL_WTH >= NVL(:6,'00000')
                    AND   A.COIL_WTH <= NVL(:7,'99999')
                    AND   (A.CUR_LOAD_LOC LIKE :8||'%' OR A.CUR_LOAD_LOC IS NULL)
 				    AND   A.SPEC_STL_GRD LIKE :9||'%'
					AND   A.YARD_ENTRY_SHIFT||'&' LIKE :10||'%'
					AND   A.YARD_ENTRY_GROUP||'&' LIKE :11||'%'
					AND   A.YARD_ENTRY_REG||'&' LIKE :12||'%'
					AND   A.TRANS_CAR_NO||'&' LIKE :13||'%' 
                    AND   A.ORD_NO||'&' LIKE :14||'%'
                    AND   A.ORD_SEQ||'&' LIKE :15||'%'
                    AND   A.DLIV_DIRNO||'&' LIKE :l6||'%'
            		AND   D.ORDCUST_CD||'&' LIKE :17||'%' 
					AND   A.DLIV_TP||'&' LIKE :18||'%'
			        AND   A.COIL_DEFECTIVE||'&' LIKE :19||'%'
			        AND   A.ORD_FL||'&' LIKE :20||'%'
                    AND   A.QK_YN||'&'  LIKE :21||'%' 
			        AND   INSTR(NVL(:22 , NVL(D.PRDNM_CD,'NULL')) , NVL(D.PRDNM_CD,'NULL')) > 0
			        AND   C.XSQY||'&' like :23||'%'
			        and   a.SPM_YN||'&' like :24||'%'
				  --AND   A.COIL_STAT = '2'
			        AND   A.COIL_IN_REG IS NULL    -- ADD BY JUNGAN 2010-12-29 不查询盘入记录
			
		]]>
	</query>
	<query id="UIH051000_04.select" desc=" 统计出库钢卷个数和重量 " fetchSize="10">
		<![CDATA[
			select count(*) COIL_CNT,
         nvl(sum(a.act_wgt)/1000,0) COIL_WGT 
         from tbh02_coil_comm a,TBA01_ORD_COMM D,TBA01_ORD_LINE C  
                 where  a.COIL_STAT = '3' 
					AND   A.ORD_NO = D.ORD_NO(+)
			        AND   A.ORD_NO = C.ORD_NO(+)
					AND   A.ORD_SEQ = C.ORD_SEQ(+)
                    AND   (SUBSTR(A.TRNF_DTIME,1,8) >= :1) --or (a.QK_YN = 'Y' and SUBSTR(a.QK_REG_DTIME,1,8)>= :2)) 
					AND   (SUBSTR(A.TRNF_DTIME,1,8) <= :2) --or (a.QK_YN = 'Y' and SUBSTR(a.QK_REG_DTIME,1,8)<= :4))
			        AND   A.OLD_SAMPL_NO LIKE :3||'%'
					AND   A.COIL_THK >= NVL(:4,'000')
                    AND   A.COIL_THK <= NVL(:5,'999')
                    AND   A.COIL_WTH >= NVL(:6,'00000')
                    AND   A.COIL_WTH <= NVL(:7,'99999')
					AND   (A.CUR_LOAD_LOC LIKE :8||'%' OR A.CUR_LOAD_LOC IS NULL)
					AND   A.SPEC_STL_GRD LIKE :9||'%'
				    AND   A.TRNF_SHIFT||'&' LIKE :10||'%'
					AND   A.TRNF_GROUP||'&' LIKE :11||'%'
					AND   A.TRNF_REG||'&' LIKE :12||'%'
				    AND   A.TRANS_CAR_NO||'&' LIKE :13||'%' 
                    AND   A.ORD_NO||'&' LIKE :14||'%'
                    AND   A.ORD_SEQ||'&' LIKE :15||'%'
                    AND   A.DLIV_DIRNO||'&' LIKE :l6||'%'
			        AND   D.ORDCUST_CD||'&' LIKE :17||'%' 
			        AND   A.DLIV_TP||'&' LIKE :18||'%'
			        AND   A.COIL_DEFECTIVE||'&' LIKE :19||'%'
			        AND   A.ORD_FL||'&' LIKE :20||'%'
                    AND   A.QK_YN||'&'  LIKE :21||'%' 
			        AND   INSTR(NVL(:22 , NVL(D.PRDNM_CD,'NULL')) , NVL(D.PRDNM_CD,'NULL')) > 0
			        AND   C.XSQY||'&' like :23||'%'
			        and   a.SPM_YN||'&' like :24||'%'
			        AND   (A.SHIP_PROG_CD <> '10' or A.SHIP_PROG_CD IS NULL)    -- ADD BY JUNGAN 2010-12-29 不查询盘出记录
			
		]]>
	</query>
	<query id="UIH051000_05.select" desc=" 获取收货单位信息 " fetchSize="10">
		<![CDATA[
			SELECT T.CUST_CD VALUE,T.CUST_NM LABEL FROM TBZ00_CUSTOMER T WHERE T.REC_TP='02' order by T.CUST_NM	
		]]>
	</query>
</queryMap>