wudonglin
/
XinYu_RZMES


			
				
					
						
						
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152
							<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="热连轧非计划表">
  
	<query id="UIB030206_01.SELECT" desc="物料进程">
SELECT     
 CCM.SPEC_STL_GRD 
,SUM(NVL(SPM.ORD_WGT , 0))  - SUM(DECODE(RHF.MISSNO_CLF,1,SPM.ORD_WGT , 2 , SPM.ORD_WGT , 0 ))  - SUM(DECODE(RHF.MISSNO_CLF,3,SPM.ORD_WGT , 0))                                         XIAOJI                    --坯料投入量：组批坯料-加回-轧回
,SUM(NVL(SPM.ORD_WGT , 0))                                          PILIAOTOURU               --组批坯料：计划投入量
,SUM(CASE WHEN CCM.MISSNO_CLF_CD IS NULL THEN CCM.ACT_WGT ELSE 0 END) HEJI
,SUM(DECODE(RHF.MISSNO_CLF,1,SPM.ORD_WGT , 2 , SPM.ORD_WGT , 0 ))   JIAHUI                    --1\2加回,没有重量数据，以理论重量计算
,SUM(DECODE(RHF.MISSNO_CLF,3,SPM.ORD_WGT , 0))                      ZHAHUI                    --3札回,没有重量数据，以理论重量计算
,SUM(CASE WHEN MIL.MISSNO_CLF_CD = 'C' AND MIL.MISSNO_BAR_WTH IS NOT NULL AND MIL.MISSNO_BAR_THK IS NOT NULL THEN SPM.ORD_WGT ELSE 0 END )   ZHONGBAN         --中板              
,SUM(CASE WHEN MIL.MISSNO_CLF_CD = 'C' AND MIL.MISSNO_BAR_WTH IS  NULL AND MIL.MISSNO_BAR_THK IS  NULL THEN SPM.ORD_WGT ELSE 0 END )         FEIGANG          --废钢
,SUM(CASE WHEN CCM.COIL_STAT != '1' AND CCM.TOT_DEC_GRD IS NOT NULL THEN CCM.ACT_WGT ELSE 0 END )            ORD_FL2          --非计划品统计用检：成品卷-未检品
,SUM(CASE WHEN ( CCM.COIL_STAT = '2' OR CCM.COIL_STAT = '3' ) AND CCM.MISSNO_CLF_CD IS NULL  THEN CCM.ACT_WGT ELSE 0 END)                           SUM_COUNT        --现场钢板/钢卷合计：成品卷（库存卷+销售卷+未检品）    
,SUM(CASE WHEN CCM.TOT_DEC_GRD IS NULL AND CCM.COIL_STAT = '2' THEN CCM.ACT_WGT ELSE 0 END)   TOT_DEC_GRD0   
,SUM(DECODE(CCM.COIL_DEFECTIVE , '1A' , CCM.ACT_WGT , 0))           COIL_DEFECTIVE1A
--库存待处置品(A)，被降为非计划的卷，无标准号和订单用途，暂不做处理（是未处理品，若要强行区分钢制和轧制方面的原因，数据将大量确实）
--,SUM(CASE WHEN CCM.COIL_STAT = '2' AND  CCM.TOT_DEC_GRD IS NOT  NULL AND CCM.COIL_DEFECTIVE IS NULL THEN CCM.ACT_WGT ELSE 0 END)   A1
--,SUM(CASE WHEN CCM.COIL_STAT = '2' AND  CCM.TOT_DEC_GRD IS NOT  NULL AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 2 THEN CCM.ACT_WGT ELSE 0 END)   A4
--库存废品(B)
--,SUM(CASE WHEN CCM.COIL_STAT = '2' AND (CCM.EXTSHAPE_DEC_GRD = '3' OR CCM.EXTSHAPE_DEC_GRD = '4') AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 1 THEN CCM.ACT_WGT ELSE 0 END)     B3  --外观
--,SUM(CASE WHEN CCM.COIL_STAT = '2' AND (CCM.EXTSHAPE_DEC_GRD = '3' OR CCM.EXTSHAPE_DEC_GRD = '4') AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 2 THEN CCM.ACT_WGT ELSE 0 END)     B7 --外观
--库存次品(C)
,(SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '2A'  THEN CCM.ACT_WGT ELSE 0 END )+SUM(CASE WHEN CCM.COIL_STAT = '2' AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 1 AND CCM.COIL_DEFECTIVE = '3D'  THEN 1 ELSE 0 END ) ) C1
,SUM(CASE WHEN CCM.COIL_STAT = '2'  AND CCM.COIL_DEFECTIVE = '2A'  THEN CCM.ACT_WGT ELSE 0 END ) C2   --成分
,SUM(CASE WHEN CCM.COIL_STAT = '2'  AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 1 AND CCM.COIL_DEFECTIVE = '3D'  THEN CCM.ACT_WGT ELSE 0 END ) C3   --外观
,(SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '4A' THEN CCM.ACT_WGT ELSE 0 END  )+SUM(CASE WHEN CCM.COIL_STAT = '2' AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 2 AND CCM.COIL_DEFECTIVE = '3D'  THEN 1 ELSE 0 END ))  C5
,SUM(CASE WHEN CCM.COIL_STAT = '2'  AND CCM.COIL_DEFECTIVE = '4A' THEN CCM.ACT_WGT ELSE 0 END  ) C6   --材质
,SUM(CASE WHEN CCM.COIL_STAT = '2'  AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 2 AND CCM.COIL_DEFECTIVE = '3D'  THEN CCM.ACT_WGT ELSE 0 END ) C7   --外观
--库存合格品(D)
--,SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '1A' THEN CCM.ACT_WGT ELSE 0 END)                                    D1 
,SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '1A' AND CCM.NONORD_CAUSE_CD = '2A' THEN CCM.ACT_WGT ELSE 0 END )                                             D2
,SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '1A' AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 1 AND CCM.NONORD_CAUSE_CD = '3D' THEN CCM.ACT_WGT ELSE 0 END )   D3--外观
,SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '1A' AND CCM.NONORD_CAUSE_CD = '4A' THEN CCM.ACT_WGT ELSE 0 END )                                             D7 --材质           
,SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '1A' AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 2 AND CCM.NONORD_CAUSE_CD = '3D' THEN CCM.ACT_WGT ELSE 0 END )   D9--外观 
,SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '1A' AND CCM.NONORD_CAUSE_CD = '3C' THEN CCM.ACT_WGT ELSE 0 END )                                             D11 --重量
,SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '1A' AND CCM.NONORD_CAUSE_CD = '3B' THEN CCM.ACT_WGT ELSE 0 END )                                             D10 --尺寸
----废钢出库废品(E) 暂无
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND CCM.MISSNO_CLF_CD IS NOT NULL THEN CCM.ACT_WGT ELSE 0 END) E1
--废钢出库次品(F)
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND (CCM.EXTSHAPE_DEC_GRD = '3' OR CCM.EXTSHAPE_DEC_GRD = '4') AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 1 THEN CCM.ACT_WGT ELSE 0 END ) F3 
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND (CCM.EXTSHAPE_DEC_GRD = '3' OR CCM.EXTSHAPE_DEC_GRD = '4') AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 2 THEN CCM.ACT_WGT ELSE 0 END  ) F7
--销售次品(G)，系统中在对于做了发车计划的卷，不会取消计划，若发现不合格，仅给出不合格信息。
--     ,SUM(CASE WHEN CCM.COIL_STAT = '3' AND  (CCM.TOT_DEC_GRD != '1' AND CCM.TOT_DEC_GRD IS NOT NULL)  THEN CCM.ACT_WGT ELSE 0 END )  G1
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND  CCM.TOT_DEC_GRD ='2'  AND CCM.INGR_DEC_GRD = '2'  THEN CCM.ACT_WGT ELSE 0 END)           G2
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND  CCM.TOT_DEC_GRD = '3' OR CCM.TOT_DEC_GRD = '4' AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 1 THEN CCM.ACT_WGT ELSE 0 END) G3
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND  CCM.TOT_DEC_GRD ='2'  AND CCM.MATLQLTY_DEC_GRD = '2'  THEN CCM.ACT_WGT ELSE 0 END)           G6
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND  CCM.TOT_DEC_GRD = '3' OR CCM.TOT_DEC_GRD = '4' AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 2 THEN CCM.ACT_WGT ELSE 0 END) G7
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND  CCM.TOT_DEC_GRD ='2'  AND CCM.SIZE_DEC_RST = '2'  THEN CCM.ACT_WGT ELSE 0 END)                                       G8
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND  CCM.TOT_DEC_GRD ='2'  AND CCM.WGT_DEC_RST = '2'  THEN CCM.ACT_WGT ELSE 0 END)                                       G9
----销售合格品(H)，系统中在对于做了发车计划的卷，不会取消计划，若发现不合格，仅给出不合格信息。
--,SUM(CASE WHEN CCM.TOT_DEC_GRD = '1' AND CCM.COIL_STAT = '3'  THEN CCM.ACT_WGT ELSE 0 END)                                      H1
--计划订单材(I),在库和不在库的都算
,SUM(CASE WHEN CCM.TOT_DEC_GRD = '1' AND ( SUBSTR(CCM.ORD_NO , 0,2) = '31' OR SUBSTR(CCM.ORD_NO , 0,4) = 'XGEC' ) THEN CCM.ACT_WGT ELSE 0 END) I1  --计划订单小计
,SUM(CASE WHEN CCM.TOT_DEC_GRD = '1' AND SUBSTR(CCM.ORD_NO , 0,2) = '31' THEN CCM.ACT_WGT ELSE 0 END) I2  --内贸合同
,SUM(CASE WHEN CCM.TOT_DEC_GRD = '1' AND SUBSTR(CCM.ORD_NO , 0,2) = 'XG' THEN CCM.ACT_WGT ELSE 0 END) I3  --外贸合同
--非计划订单材（J），在库和不在库的都算
,SUM(CASE WHEN CCM.ORD_FL = '1' AND( SUBSTR(CCM.ORD_NO , 0,2) = '36' OR SUBSTR(CCM.ORD_NO , 0,2) = '37' ) AND CCM.COIL_STAT != '1' THEN CCM.ACT_WGT ELSE 0 END )  J1  --小计
,SUM(CASE WHEN CCM.ORD_FL = '1' AND SUBSTR(CCM.ORD_NO , 0,2) = '36' AND CCM.COIL_STAT != '1' THEN CCM.ACT_WGT ELSE 0 END )  J2 --36合同
,SUM(CASE WHEN CCM.ORD_FL = '1' AND SUBSTR(CCM.ORD_NO , 0,2) = '37' AND CCM.COIL_STAT != '1' THEN CCM.ACT_WGT ELSE 0 END )  J3
--其它
,(SUM(NVL(CCM.ACT_WGT , 0)) -SUM(CASE WHEN CCM.EXTSHAPE_DEC_GRD = '4' OR CCM.EXTSHAPE_DEC_GRD = '3' THEN CCM.ACT_WGT ELSE 0 END) - SUM(CASE WHEN CCM.MISSNO_CLF_CD IS NOT NULL AND CCM.EXTSHAPE_DEC_GRD IS NULL THEN CCM.ACT_WGT ELSE 0 END))/(SUM(NVL(SPM.ORD_WGT , 0))  - SUM(DECODE(RHF.MISSNO_CLF,1,SPM.ORD_WGT , 2 , SPM.ORD_WGT , 0 ))  - SUM(DECODE(RHF.MISSNO_CLF,3,SPM.ORD_WGT , 0)))  CHENCAILV  -- (轧制合计-外观次、废品-轧废)/坯料投入量
,SUM(CASE WHEN (CCM.COIL_STAT = '2' AND CCM.ORD_FL = '2' )  OR (CCM.COIL_STAT = '3' AND (  SUBSTR(CCM.ORD_NO , 0,2)= '37'  OR SUBSTR(CCM.ORD_NO , 0,2)= '36'   ) ) THEN CCM.ACT_WGT ELSE 0 END)  FEIJIHUA  --非计划，在库的非计划+已销售的非计划订单
,1-SUM(CASE WHEN CCM.ORD_FL = '1' AND (SUBSTR(CCM.ORD_NO , 0,2) = '31' OR SUBSTR(CCM.ORD_NO , 0,2) = 'XG') THEN CCM.ACT_WGT ELSE 0 END )/SUM(CASE WHEN (CCM.EXTSHAPE_DEC_GRD != '4' AND CCM.EXTSHAPE_DEC_GRD IS NOT NULL) AND CCM.COIL_STAT != '1' THEN CCM.ACT_WGT ELSE 0 END )    FEIJIHUALV   --1-计划总量/非计划统计用检验量；计划总量指内贸、外贸合同的合格总量;无正式销售订单的也属于非计划
,SUM(CASE WHEN SPM.ORD_NO = CCM.ORD_NO  AND SPM.ORD_SEQ = CCM.ORD_SEQ AND CCM.COIL_NO = SPM.COIL_NO AND (SUBSTR(CCM.ORD_NO , 0,2) = '31' OR SUBSTR(CCM.ORD_NO , 0,2) = 'XG' OR SUBSTR(CCM.ORD_NO , 0,2) = '37' ) AND CCM.ORD_FL = '1' AND CCM.TOT_DEC_GRD = '1' THEN CCM.ACT_WGT ELSE 0 END )/(SUM(CCM.ACT_WGT)+0.000001)   YIHEGELV  -- 一次合格率
FROM        TBH02_COIL_COMM  CCM  , TBH02_RHF_MISSNO RHF  , TBH02_MILL_RESULT    MIL , TBF02_SPEC_MILL SPM , TBH02_REHEATFUR WORI  
WHERE       CCM.COIL_NO       =        RHF.COIL_NO(+)
AND         CCM.COIL_NO       =        MIL.COIL_NO(+)
AND         CCM.COIL_NO       =        SPM.COIL_NO
AND         CCM.COIL_NO       =        WORI.COIL_NO
AND         CCM.SPEC_STL_GRD    IS  NOT   NULL  
AND         SUBSTR(WORI.EXTRACT_DTIME , 1,8 )   BETWEEN  ?  AND ?  
GROUP BY    CCM.SPEC_STL_GRD
ORDER BY    CCM.SPEC_STL_GRD
	</query>
	
	<query id="UIB030206_02.SELECT" desc="">
SELECT     
 WORI.EXTRACT_WKGROUP
,SUM(NVL(SPM.ORD_WGT , 0))  - SUM(DECODE(RHF.MISSNO_CLF,1,SPM.ORD_WGT , 2 , SPM.ORD_WGT , 0 ))  - SUM(DECODE(RHF.MISSNO_CLF,3,SPM.ORD_WGT , 0))                                         XIAOJI                    --坯料投入量：组批坯料-加回-轧回
,SUM(NVL(SPM.ORD_WGT , 0))                                          PILIAOTOURU               --组批坯料：计划投入量
,SUM(CASE WHEN CCM.MISSNO_CLF_CD IS NULL THEN CCM.ACT_WGT ELSE 0 END) HEJI
,SUM(DECODE(RHF.MISSNO_CLF,1,SPM.ORD_WGT , 2 , SPM.ORD_WGT , 0 ))   JIAHUI                    --1\2加回,没有重量数据，以理论重量计算
,SUM(DECODE(RHF.MISSNO_CLF,3,SPM.ORD_WGT , 0))                      ZHAHUI                    --3札回,没有重量数据，以理论重量计算
,SUM(CASE WHEN MIL.MISSNO_CLF_CD = 'C' AND MIL.MISSNO_BAR_WTH IS NOT NULL AND MIL.MISSNO_BAR_THK IS NOT NULL THEN SPM.ORD_WGT ELSE 0 END )   ZHONGBAN         --中板              
,SUM(CASE WHEN MIL.MISSNO_CLF_CD = 'C' AND MIL.MISSNO_BAR_WTH IS  NULL AND MIL.MISSNO_BAR_THK IS  NULL THEN SPM.ORD_WGT ELSE 0 END )         FEIGANG          --废钢
,SUM(CASE WHEN CCM.COIL_STAT != '1' AND CCM.TOT_DEC_GRD IS NOT NULL THEN CCM.ACT_WGT ELSE 0 END )            ORD_FL2          --非计划品统计用检：成品卷-未检品
,SUM(CASE WHEN ( CCM.COIL_STAT = '2' OR CCM.COIL_STAT = '3' ) AND CCM.MISSNO_CLF_CD IS NULL  THEN CCM.ACT_WGT ELSE 0 END)                           SUM_COUNT        --现场钢板/钢卷合计：成品卷（库存卷+销售卷+未检品）    
,SUM(CASE WHEN CCM.TOT_DEC_GRD IS NULL AND CCM.COIL_STAT = '2' THEN CCM.ACT_WGT ELSE 0 END)   TOT_DEC_GRD0   
,SUM(DECODE(CCM.COIL_DEFECTIVE , '1A' , CCM.ACT_WGT , 0))           COIL_DEFECTIVE1A
--库存待处置品(A)，被降为非计划的卷，无标准号和订单用途，暂不做处理（是未处理品，若要强行区分钢制和轧制方面的原因，数据将大量确实）
--,SUM(CASE WHEN CCM.COIL_STAT = '2' AND  CCM.TOT_DEC_GRD IS NOT  NULL AND CCM.COIL_DEFECTIVE IS NULL THEN CCM.ACT_WGT ELSE 0 END)   A1
--,SUM(CASE WHEN CCM.COIL_STAT = '2' AND  CCM.TOT_DEC_GRD IS NOT  NULL AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 2 THEN CCM.ACT_WGT ELSE 0 END)   A4
--库存废品(B)
--,SUM(CASE WHEN CCM.COIL_STAT = '2' AND (CCM.EXTSHAPE_DEC_GRD = '3' OR CCM.EXTSHAPE_DEC_GRD = '4') AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 1 THEN CCM.ACT_WGT ELSE 0 END)     B3  --外观
--,SUM(CASE WHEN CCM.COIL_STAT = '2' AND (CCM.EXTSHAPE_DEC_GRD = '3' OR CCM.EXTSHAPE_DEC_GRD = '4') AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 2 THEN CCM.ACT_WGT ELSE 0 END)     B7 --外观
--库存次品(C)
,(SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '2A'  THEN CCM.ACT_WGT ELSE 0 END )+SUM(CASE WHEN CCM.COIL_STAT = '2' AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 1 AND CCM.COIL_DEFECTIVE = '3D'  THEN 1 ELSE 0 END ) ) C1
,SUM(CASE WHEN CCM.COIL_STAT = '2'  AND CCM.COIL_DEFECTIVE = '2A'  THEN CCM.ACT_WGT ELSE 0 END ) C2   --成分
,SUM(CASE WHEN CCM.COIL_STAT = '2'  AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 1 AND CCM.COIL_DEFECTIVE = '3D'  THEN CCM.ACT_WGT ELSE 0 END ) C3   --外观
,(SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '4A' THEN CCM.ACT_WGT ELSE 0 END  )+SUM(CASE WHEN CCM.COIL_STAT = '2' AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 2 AND CCM.COIL_DEFECTIVE = '3D'  THEN 1 ELSE 0 END ))  C5
,SUM(CASE WHEN CCM.COIL_STAT = '2'  AND CCM.COIL_DEFECTIVE = '4A' THEN CCM.ACT_WGT ELSE 0 END  ) C6   --材质
,SUM(CASE WHEN CCM.COIL_STAT = '2'  AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 2 AND CCM.COIL_DEFECTIVE = '3D'  THEN CCM.ACT_WGT ELSE 0 END ) C7   --外观
--库存合格品(D)
--,SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '1A' THEN CCM.ACT_WGT ELSE 0 END)                                    D1 
,SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '1A' AND CCM.NONORD_CAUSE_CD = '2A' THEN CCM.ACT_WGT ELSE 0 END )                                             D2
,SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '1A' AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 1 AND CCM.NONORD_CAUSE_CD = '3D' THEN CCM.ACT_WGT ELSE 0 END )   D3--外观
,SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '1A' AND CCM.NONORD_CAUSE_CD = '4A' THEN CCM.ACT_WGT ELSE 0 END )                                             D7 --材质           
,SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '1A' AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 2 AND CCM.NONORD_CAUSE_CD = '3D' THEN CCM.ACT_WGT ELSE 0 END )   D9--外观 
,SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '1A' AND CCM.NONORD_CAUSE_CD = '3C' THEN CCM.ACT_WGT ELSE 0 END )                                             D11 --重量
,SUM(CASE WHEN CCM.COIL_STAT = '2' AND CCM.COIL_DEFECTIVE = '1A' AND CCM.NONORD_CAUSE_CD = '3B' THEN CCM.ACT_WGT ELSE 0 END )                                             D10 --尺寸
----废钢出库废品(E) 暂无
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND CCM.MISSNO_CLF_CD IS NOT NULL THEN CCM.ACT_WGT ELSE 0 END) E1
--废钢出库次品(F)
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND (CCM.EXTSHAPE_DEC_GRD = '3' OR CCM.EXTSHAPE_DEC_GRD = '4') AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 1 THEN CCM.ACT_WGT ELSE 0 END ) F3 
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND (CCM.EXTSHAPE_DEC_GRD = '3' OR CCM.EXTSHAPE_DEC_GRD = '4') AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 2 THEN CCM.ACT_WGT ELSE 0 END  ) F7
--销售次品(G)，系统中在对于做了发车计划的卷，不会取消计划，若发现不合格，仅给出不合格信息。
--     ,SUM(CASE WHEN CCM.COIL_STAT = '3' AND  (CCM.TOT_DEC_GRD != '1' AND CCM.TOT_DEC_GRD IS NOT NULL)  THEN CCM.ACT_WGT ELSE 0 END )  G1
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND  CCM.TOT_DEC_GRD ='2'  AND CCM.INGR_DEC_GRD = '2'  THEN CCM.ACT_WGT ELSE 0 END)           G2
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND  CCM.TOT_DEC_GRD = '3' OR CCM.TOT_DEC_GRD = '4' AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 1 THEN CCM.ACT_WGT ELSE 0 END) G3
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND  CCM.TOT_DEC_GRD ='2'  AND CCM.MATLQLTY_DEC_GRD = '2'  THEN CCM.ACT_WGT ELSE 0 END)           G6
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND  CCM.TOT_DEC_GRD = '3' OR CCM.TOT_DEC_GRD = '4' AND PKG_QUALITY_COMM.FIB030010(CCM.CRK_CD1) = 2 THEN CCM.ACT_WGT ELSE 0 END) G7
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND  CCM.TOT_DEC_GRD ='2'  AND CCM.SIZE_DEC_RST = '2'  THEN CCM.ACT_WGT ELSE 0 END)                                       G8
--,SUM(CASE WHEN CCM.COIL_STAT = '3' AND  CCM.TOT_DEC_GRD ='2'  AND CCM.WGT_DEC_RST = '2'  THEN CCM.ACT_WGT ELSE 0 END)                                       G9
----销售合格品(H)，系统中在对于做了发车计划的卷，不会取消计划，若发现不合格，仅给出不合格信息。
--,SUM(CASE WHEN CCM.TOT_DEC_GRD = '1' AND CCM.COIL_STAT = '3'  THEN CCM.ACT_WGT ELSE 0 END)                                      H1
--计划订单材(I),在库和不在库的都算
,SUM(CASE WHEN CCM.TOT_DEC_GRD = '1' AND ( SUBSTR(CCM.ORD_NO , 0,2) = '31' OR SUBSTR(CCM.ORD_NO , 0,4) = 'XGEC' ) THEN CCM.ACT_WGT ELSE 0 END) I1  --计划订单小计
,SUM(CASE WHEN CCM.TOT_DEC_GRD = '1' AND SUBSTR(CCM.ORD_NO , 0,2) = '31' THEN CCM.ACT_WGT ELSE 0 END) I2  --内贸合同
,SUM(CASE WHEN CCM.TOT_DEC_GRD = '1' AND SUBSTR(CCM.ORD_NO , 0,2) = 'XG' THEN CCM.ACT_WGT ELSE 0 END) I3  --外贸合同
--非计划订单材（J），在库和不在库的都算
,SUM(CASE WHEN CCM.ORD_FL = '1' AND( SUBSTR(CCM.ORD_NO , 0,2) = '36' OR SUBSTR(CCM.ORD_NO , 0,2) = '37' ) AND CCM.COIL_STAT != '1' THEN CCM.ACT_WGT ELSE 0 END )  J1  --小计
,SUM(CASE WHEN CCM.ORD_FL = '1' AND SUBSTR(CCM.ORD_NO , 0,2) = '36' AND CCM.COIL_STAT != '1' THEN CCM.ACT_WGT ELSE 0 END )  J2 --36合同
,SUM(CASE WHEN CCM.ORD_FL = '1' AND SUBSTR(CCM.ORD_NO , 0,2) = '37' AND CCM.COIL_STAT != '1' THEN CCM.ACT_WGT ELSE 0 END )  J3
--其它
,(SUM(NVL(CCM.ACT_WGT , 0)) -SUM(CASE WHEN CCM.EXTSHAPE_DEC_GRD = '4' OR CCM.EXTSHAPE_DEC_GRD = '3' THEN CCM.ACT_WGT ELSE 0 END) - SUM(CASE WHEN CCM.MISSNO_CLF_CD IS NOT NULL AND CCM.EXTSHAPE_DEC_GRD IS NULL THEN CCM.ACT_WGT ELSE 0 END))/(SUM(NVL(SPM.ORD_WGT , 0))  - SUM(DECODE(RHF.MISSNO_CLF,1,SPM.ORD_WGT , 2 , SPM.ORD_WGT , 0 ))  - SUM(DECODE(RHF.MISSNO_CLF,3,SPM.ORD_WGT , 0)))  CHENCAILV  -- (轧制合计-外观次、废品-轧废)/坯料投入量
,SUM(CASE WHEN (CCM.COIL_STAT = '2' AND CCM.ORD_FL = '2' )  OR (CCM.COIL_STAT = '3' AND (  SUBSTR(CCM.ORD_NO , 0,2)= '37'  OR SUBSTR(CCM.ORD_NO , 0,2)= '36'   ) ) THEN CCM.ACT_WGT ELSE 0 END)  FEIJIHUA  --非计划，在库的非计划+已销售的非计划订单
,1-SUM(CASE WHEN CCM.ORD_FL = '1' AND (SUBSTR(CCM.ORD_NO , 0,2) = '31' OR SUBSTR(CCM.ORD_NO , 0,2) = 'XG') THEN CCM.ACT_WGT ELSE 0 END )/SUM(CASE WHEN (CCM.EXTSHAPE_DEC_GRD != '4' AND CCM.EXTSHAPE_DEC_GRD IS NOT NULL) AND CCM.COIL_STAT != '1' THEN CCM.ACT_WGT ELSE 0 END )    FEIJIHUALV   --1-计划总量/非计划统计用检验量；计划总量指内贸、外贸合同的合格总量;无正式销售订单的也属于非计划
,SUM(CASE WHEN SPM.ORD_NO = CCM.ORD_NO  AND SPM.ORD_SEQ = CCM.ORD_SEQ AND CCM.COIL_NO = SPM.COIL_NO AND (SUBSTR(CCM.ORD_NO , 0,2) = '31' OR SUBSTR(CCM.ORD_NO , 0,2) = 'XG' OR SUBSTR(CCM.ORD_NO , 0,2) = '37' ) AND CCM.ORD_FL = '1' AND CCM.TOT_DEC_GRD = '1' THEN CCM.ACT_WGT ELSE 0 END )/(SUM(CCM.ACT_WGT)+0.000001)   YIHEGELV  -- 一次合格率
FROM        TBH02_COIL_COMM  CCM  , TBH02_RHF_MISSNO RHF  , TBH02_MILL_RESULT    MIL , TBF02_SPEC_MILL SPM , TBH02_REHEATFUR WORI  
WHERE       CCM.COIL_NO       =        RHF.COIL_NO(+)
AND         CCM.COIL_NO       =        MIL.COIL_NO(+)
AND         CCM.COIL_NO       =        SPM.COIL_NO
AND         CCM.COIL_NO       =        WORI.COIL_NO
AND         CCM.SPEC_STL_GRD    IS  NOT   NULL  
AND         SUBSTR(WORI.EXTRACT_DTIME , 1,8 )   BETWEEN  ?  AND ?  
GROUP BY    WORI.EXTRACT_WKGROUP
ORDER BY    WORI.EXTRACT_WKGROUP

	</query>
</queryMap>