XinYu_RZMES/.svn/pristine/35/35e0e6ae34caa4e63117259c72f08fd45addff4e.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="订单标准录入现况">
    <query id="UIB010340.SELECT" desc="" fetchSize="10">  
        <![CDATA[
		WITH TEMP AS (SELECT :1 SPEC_ABB FROM DUAL )
		SELECT DISTINCT SPEC.SPEC_ABBSYM
						,SPEC.SPEC_STL_GRD
						,DECODE(B01SI.SPEC_STL_GRD,'','','Y') SI
						,DECODE(B01SQ.SPEC_STL_GRD,'','','Y') SQ
						,DECODE(B01KP.SPEC_STL_GRD,'','','Y') KP
						,DECODE(B01KD.SPEC_STL_GRD,'','','Y') KD
						,DECODE(A01OL.SPEC_STL_GRD,'','','Y') OL
						,(CASE WHEN ((B01SI.SPEC_STL_GRD IS NULL OR B01SQ.SPEC_STL_GRD IS NULL OR
						B01KP.SPEC_STL_GRD IS NULL OR B01KD.SPEC_STL_GRD IS NULL)
						AND A01OL.SPEC_STL_GRD IS NOT NULL) THEN '2' 
						ELSE (CASE WHEN (B01SI.SPEC_STL_GRD IS NULL OR B01SQ.SPEC_STL_GRD IS NULL OR
						B01KP.SPEC_STL_GRD IS NULL OR B01KD.SPEC_STL_GRD IS NULL)
						THEN '1' ELSE '0' END)
						END) FLAG
		FROM 
		(SELECT DISTINCT SPEC_ABBSYM,SPEC_STL_GRD FROM 
			(SELECT DISTINCT A01OL.SPEC_ABBSYM,A01OL.SPEC_STL_GRD FROM TBZ00_COMMCD ZOOC,TBA01_ORD_LINE A01OL,TEMP
			WHERE ZOOC.SM_CD = A01OL.SPEC_ABBSYM AND ZOOC.LG_CD = 'B01001' AND ZOOC.SM_CD LIKE TEMP.SPEC_ABB||'%' 
			--订单表中的标准号，标准钢号
			UNION ALL 
			SELECT DISTINCT B01SI.SPEC_ABBSYM,B01SI.SPEC_STL_GRD FROM TBZ00_COMMCD ZOOC,TBB01_SPEC_INGR B01SI,TEMP
			WHERE ZOOC.SM_CD = B01SI.SPEC_ABBSYM AND ZOOC.LG_CD = 'B01001' AND ZOOC.SM_CD LIKE TEMP.SPEC_ABB||'%'
			--标准成分中的标准号，标准钢号 
			UNION ALL
			SELECT DISTINCT B01SQ.SPEC_ABBSYM,B01SQ.SPEC_STL_GRD FROM TBZ00_COMMCD ZOOC,TBB01_SPEC_QLTY B01SQ,TEMP
			WHERE ZOOC.SM_CD = B01SQ.SPEC_ABBSYM AND ZOOC.LG_CD = 'B01001' AND ZOOC.SM_CD LIKE TEMP.SPEC_ABB||'%'
			--标准材质表中的标准号，标准钢号
			UNION ALL
			SELECT DISTINCT B01KP.SPEC_ABBSYM,B01KP.SPEC_STL_GRD FROM TBZ00_COMMCD ZOOC,TBB01_KEY_PROD B01KP,TEMP
			WHERE ZOOC.SM_CD = B01KP.SPEC_ABBSYM AND ZOOC.LG_CD = 'B01001' AND ZOOC.SM_CD LIKE TEMP.SPEC_ABB||'%'
			--设计规范表中的标准号，标准钢号
			UNION ALL
			SELECT DISTINCT B01KD.SPEC_ABBSYM,B01KD.SPEC_STL_GRD FROM TBZ00_COMMCD ZOOC,TBB01_KEY_DEL B01KD,TEMP
			WHERE ZOOC.SM_CD = B01KD.SPEC_ABBSYM AND ZOOC.LG_CD = 'B01001' AND ZOOC.SM_CD LIKE TEMP.SPEC_ABB||'%'
			--标准交付条件表中的标准号，标准钢号
			)
		) SPEC,TBB01_SPEC_INGR B01SI,
		(SELECT SPEC_ABBSYM,SPEC_STL_GRD FROM TBB01_SPEC_QLTY  WHERE QLTY_CD = 'SAMP' 
		AND (( SMP_LOT_CD IS NOT NULL AND SMP_LEN_LOC IS NOT NULL 
		AND SMP_WTH_LOC IS NOT NULL) OR SMP_LOT_CD ='00')) B01SQ,
		TBB01_KEY_PROD B01KP,TBB01_KEY_DEL B01KD,TBA01_ORD_LINE A01OL
		WHERE SPEC.SPEC_ABBSYM = B01SI.SPEC_ABBSYM(+) AND SPEC.SPEC_STL_GRD = B01SI.SPEC_STL_GRD(+) 
		AND SPEC.SPEC_ABBSYM   = B01SQ.SPEC_ABBSYM(+) AND SPEC.SPEC_STL_GRD = B01SQ.SPEC_STL_GRD(+)
		AND SPEC.SPEC_ABBSYM   = B01KP.SPEC_ABBSYM(+) AND SPEC.SPEC_STL_GRD = B01KP.SPEC_STL_GRD(+)
		AND SPEC.SPEC_ABBSYM   = B01KD.SPEC_ABBSYM(+) AND SPEC.SPEC_STL_GRD = B01KD.SPEC_STL_GRD(+)
		AND SPEC.SPEC_ABBSYM   = A01OL.SPEC_ABBSYM(+) AND SPEC.SPEC_STL_GRD = A01OL.SPEC_STL_GRD(+)
		ORDER BY SPEC.SPEC_ABBSYM,FLAG DESC
		]]>   
    </query> 
										<query id="UIB010340.ORD.SELECT" desc="" fetchSize="10">  
											<![CDATA[	
											SELECT A01RL.ORD_NO||'-'||A01RL.ORD_SEQ ORD FROM tba01_ord_line A01RL 
											WHERE A01RL.SPEC_ABBSYM = :1 AND A01RL.SPEC_STL_GRD = :2 and ROWNUM <= 2
											]]>   
										</query> 
    <query id="UIB010340.ORDLIST.SELECT" desc="" fetchSize="10">  
        <![CDATA[
		SELECT A01RL.ORD_NO,A01RL.ORD_SEQ  FROM tba01_ord_line A01RL 
		WHERE A01RL.SPEC_ABBSYM = :1 AND A01RL.SPEC_STL_GRD = :2
		]]>   
    </query>
    <query id="UIB010340.ABBSYM.SELECT" desc="" fetchSize="10">  
        <![CDATA[
		SELECT '' VALUE,
			   '--全部--' LABEL
		FROM dual
		UNION ALL			
		SELECT DISTINCT SM_CD VALUE,
			   CASE WHEN SPEC_ABBSYM IS NULL THEN SM_CD ELSE '(有)'||SM_CD END LABEL 
		FROM 
		(   SELECT DISTINCT A01OL.SPEC_ABBSYM FROM TBZ00_COMMCD ZOOC,TBA01_ORD_LINE A01OL
			WHERE ZOOC.SM_CD = A01OL.SPEC_ABBSYM AND ZOOC.LG_CD = 'B01001' 
			--订单表中的标准号，标准钢号
			UNION ALL
			SELECT DISTINCT B01SI.SPEC_ABBSYM FROM TBZ00_COMMCD ZOOC,TBB01_SPEC_INGR B01SI
			WHERE ZOOC.SM_CD = B01SI.SPEC_ABBSYM AND ZOOC.LG_CD = 'B01001' 
			--标准成分中的标准号 
			UNION ALL
			SELECT DISTINCT B01SQ.SPEC_ABBSYM FROM TBZ00_COMMCD ZOOC,TBB01_SPEC_QLTY B01SQ
			WHERE ZOOC.SM_CD = B01SQ.SPEC_ABBSYM AND ZOOC.LG_CD = 'B01001' 
			--标准材质表中的标准号
			UNION ALL
			SELECT DISTINCT B01KP.SPEC_ABBSYM FROM TBZ00_COMMCD ZOOC,TBB01_KEY_PROD B01KP
			WHERE ZOOC.SM_CD = B01KP.SPEC_ABBSYM AND ZOOC.LG_CD = 'B01001' 
			--设计规范表中的标准号
			UNION ALL
			SELECT DISTINCT B01KD.SPEC_ABBSYM FROM TBZ00_COMMCD ZOOC,TBB01_KEY_DEL B01KD
			WHERE ZOOC.SM_CD = B01KD.SPEC_ABBSYM AND ZOOC.LG_CD = 'B01001'
			--标准交付条件表中的标准号
		) A ,
		(
		SELECT SM_CD FROM TBZ00_COMMCD WHERE LG_CD = 'B01001'
		) B
		WHERE A.SPEC_ABBSYM(+) = B.SM_CD 
		]]>   
    </query>						
</queryMap>