XinYu_LZMES/.svn/pristine/db/dbd86cb6b15ca5b70a6229263ec8e49b610ee9e3.svn-base

<?xml version="1.0" encoding='UTF-8'?>

<queryMap desc="">

<query id="UIF080001.Query_RollPlan_Mill" desc='得到轧辊单元计划的明细信息'>
						
					SELECT A.ROLL_MANA_NO, -- 轧辊号
					       A.ROLL_SLAB_SEQ, -- 轧辊内顺序
					       A.SLAB_NO, -- 板坯号
					       A.COIL_NO, --钢卷号    
					       A.SLAB_THK, --板坯厚度
					       A.SLAB_WTH, --板坯宽度
					       A.SLAB_LEN, --板坯长度
					       A.SLAB_WGT, --板坯重量  
					       A.ORD_THK, --订单厚度
					       A.ORD_WTH, --订单宽度
					       A.ORD_WGT, --订单重量
					       A.HOT_THK_AIM, --热厚度AIM
					       A.HOT_THK_MIN, --热厚度MIN
					       A.HOT_THK_MAX, --热厚度MAX
					       A.WTH_AIM, --宽度AlM
					       A.WTH_MIN, --宽度MIN
					       A.WTH_MAX, --宽度MAX  
					       A.MILL_LEN, --轧制长度
					       A.MILL_WGT, --轧制重量
					       A.SPEC_STL_GRD STL_GRD, --钢码
					       DECODE(GF_NIB028720(A.SLAB_NO, A.ORD_NO, A.ORD_SEQ),
					              'YY',
					              '合格',
					              '不合格') QLFD_YN,
					       DECODE(DECODE(A.STATUS_CD,
					                     'B',
					                     DECODE(B.CUR_PROG_CD,
					                            'RBB',
					                            '加热炉出炉待机',
					                            T1.SM_CFNM),
					                     T1.SM_CFNM),
					              '',
					              '板坯切割待机',
					              DECODE(A.STATUS_CD,
					                     'B',
					                     DECODE(B.CUR_PROG_CD,
					                            'RBB',
					                            '加热炉出炉待机',
					                            T1.SM_CFNM),
					                     T1.SM_CFNM)) CUR_PROG_CD, -- 当前进程   
					       T2.SM_CFNM STATUS_CD, --   状态区分
					       B.CUR_LOAD_LOC, -- 堆置位置
					       T3.SM_CFNM HCR_CLF, -- 热送状态
					       A.ORD_NO, --   合同号
					       A.ORD_SEQ, --   订单号
					       A.SPEC_ABBSYM, --  标准号
					       T4.SM_CFNM ORD_USEAGE_CD, --   订单用途代码
					       A.DELV_REQ_DATE, --   交货期
					       T5.SM_CFNM ROUTE, --    去向
					       E.EMGMTR_NMG_CLF, --  紧急材
					       A.FUR_IN_PRE_DTIME --    计划加热炉装炉日期
					      ,
					       T6.SM_CFNM DLIV_TP -- 发货方式
					      ,
					       DECODE(K.DSN_KIND, '1', 'Y', 'N') AS INGR_YN,
					       DECODE(QL.DSN_KIND, '1', 'Y', 'N') AS QLTY_YN --材质特殊要求
					      ,
					       DECODE(DE.DSN_KIND, '1', 'Y', 'N') AS DEL_YN --交付条件特殊要求 
					      ,
					       CASE
					         WHEN (A.SLAB_WTH - A.WTH_AIM) &lt;0 THEN
					          '不合格'
					         ELSE
					          '合格'
					       END SLAB_PROBLEM --板坯宽度是否比订单宽度小检查
					  FROM TBF02_SPEC_MILL A,
					       TBG02_SLAB_COMM B,
					       tbf02_spec_roll R,
					       (SELECT T.SM_CD, T.SM_CFNM
					          FROM TBZ00_COMMCD T
					         WHERE T.LG_CD = 'E01002') T1,
					       (SELECT T.SM_CD, T.SM_CFNM
					          FROM TBZ00_COMMCD T
					         WHERE T.LG_CD = 'Z01001') T2,
					       (SELECT T.SM_CD, T.SM_CFNM
					          FROM TBZ00_COMMCD T
					         WHERE T.LG_CD = 'E01012') T3,
					       (SELECT T.SM_CD, T.SM_CFNM
					          FROM TBZ00_COMMCD T
					         WHERE T.LG_CD = 'A01007') T4,
					       (SELECT T.SM_CD, T.SM_CFNM
					          FROM TBZ00_COMMCD T
					         WHERE T.LG_CD = 'B01004') T5,
					       TBE02_ORD_PRC E,
					       (SELECT SM_CD, SM_CFNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01012') T6,
					       TBA01_ORD_LINE A01,
					       (SELECT DISTINCT ORD_NO, ORD_SEQ, DSN_KIND
					          FROM TBB01_ORD_INGR
					         WHERE DSN_KIND = '1') K,
					       (SELECT DISTINCT ORD_NO, ORD_SEQ, DSN_KIND
					          FROM TBB01_ORD_QLTY
					         WHERE DSN_KIND = '1') QL --材质特殊要求
					      ,
					       (SELECT DISTINCT ORD_NO, ORD_SEQ, DSN_KIND
					          FROM TBB01_ORD_DEL
					         WHERE DSN_KIND = '1') DE --交付条件特殊要求
					 WHERE A.ORD_NO = A01.ORD_NO(+)
					   AND A.ORD_SEQ = A01.ORD_SEQ(+)
					   AND A01.DLIV_TP = T6.SM_CD(+)
					   AND A.ORD_NO = E.ORD_NO
					   AND A.ORD_SEQ = E.ORD_SEQ
					   AND A.ROLL_MANA_NO = R.ROLL_MANA_NO
					   AND A.ORD_NO = K.ORD_NO(+)
					   AND A.ORD_SEQ = K.ORD_SEQ(+)
					   AND A.ORD_NO = QL.ORD_NO(+)
					   AND A.ORD_SEQ = QL.ORD_SEQ(+)
					   AND A.ORD_NO = DE.ORD_NO(+)
					   AND A.ORD_SEQ = DE.ORD_SEQ(+)
					   --AND A.STATUS_CD IN ('A', 'B') --处于指示和作业
					   AND   R.STATUS_CD ='A' --处于指示和作业
					   AND A.SLAB_NO = B.SLAB_NO(+)
					   AND B.CUR_PROG_CD = T1.SM_CD(+)
					   AND A.STATUS_CD = T2.SM_CD
					   AND A.HCR_CLF = T3.SM_CD
					   AND A.ORD_USEAGE_CD = T4.SM_CD(+)
					   AND A.ROUTE = T5.SM_CD
					  
					 ORDER BY A.ROLL_MANA_NO ASC, A.ROLL_SLAB_SEQ ASC
</query>

<query id="UIF042040.Query_RollPlan_Roll" desc='得到轧辊单元计划的信息'>
	SELECT ROLL_MANA_NO,SUM(1) COIL_NUM FROM C_TBF03_SPEC_MILL WHERE STATUS_CD IN('A','B','C') GROUP BY ROLL_MANA_NO ORDER BY ROLL_MANA_NO
</query>
<query id="UIF042040.RollPlan_Edit" desc="调整轧辊单元计划" >
		<![CDATA[
			{call C_PKG_UIF.C_PRD_ROLLUNIT_EDIT(?,?,?,?,?,?)}
		]]>
</query>


<query id="UIF080001.RollUpt" desc="调整轧辊单元计划" >
		<![CDATA[
			{call AUPRZ_MILLSTATUS(?,?)}
		]]>
</query>



<query id="UIF042040.Query_RollPlan_Mill01" desc='得到轧辊单元计划的明细信息'>
	SELECT F.ROLL_MANA_NO,
	F.ROLL_COIL_SEQ,
	F.COIL_NO,
	F.C_COIL_NO,
	A.COIL_THK,
	A.COIL_WTH,
	A.COIL_LEN,
	A.COIL_INDIA,
	A.COIL_OUTDIA,
	A.ACT_WGT,
	C.C_ORD_THK,
	--C.C_ORD_THK_MIN,
	--C.C_ORD_THK_MAX,
	C.C_ORD_WTH,
	--C.C_ORD_WTH_MIN,
	--C.C_ORD_WTH_MAX,
   (DECODE(C.SUAN_THK_MIN,NULL, C.C_ORD_THK_MIN, C.SUAN_THK_MIN)+
   DECODE(C.SUAN_THK_MAX,NULL, C.C_ORD_THK_MAX,C.SUAN_THK_MAX))/2 M_THK   ,
   DECODE(C.SUAN_THK_MIN,NULL, C.C_ORD_THK_MIN, C.SUAN_THK_MIN) C_ORD_THK_MIN,
   DECODE(C.SUAN_THK_MAX,NULL, C.C_ORD_THK_MAX,C.SUAN_THK_MAX) C_ORD_THK_MAX,
   DECODE(C.SUAN_WTH_MIN, NULL,C.C_ORD_WTH_MIN,C.SUAN_WTH_MIN) C_ORD_WTH_MIN,
   DECODE(C.SUAN_WTH_MAX,NULL, C.C_ORD_WTH_MAX,C.SUAN_WTH_MAX) C_ORD_WTH_MAX,
	C.C_ORD_LEN,
	C.C_ORD_INDIA,
	C.C_ORD_OUTDIA,
	C.C_EXTSHAPE_REQ,
	B.SM_CSNM CUR_PROG_CD,
	F.STATUS_CD,
	A.CUR_LOAD_LOC,
	C.ACID_PROD_PATH,
	C.ORD_NO,
	C.ORD_SEQ,
	C.C_SPEC_STL_GRD,
	C.C_SPEC_ABBSYM,
	H.SM_CFNM ORD_USEAGE_CD,
	C.DEL_TO_DATE,
	D.CUST_NM ORDCUST_CD,
	E.SM_CFNM DEST_CD,
	ROUND(SYSDATE-TO_DATE(A.RECEIVE_TIME,'YYYY-MM-DD HH24:MI:SS'),1) COLDTIM,
	C.ORD_UNIT_WGT,
	C.ORD_UNIT_WGT_MIN,
	C.ORD_UNIT_WGT_MAX,
	C.C_PRODNM_CD,
	F.C_COIL_ID,
	I.SM_CFNM PACK_METHOD,
	DECODE(C.EDGE_PROC_YN,'Y','√',NULL) EDGE_PROC_YN,
	--(C.C_ORD_THK_MIN+C.C_ORD_THK_MAX)/2 M_THK   ,
	XB_PACKAGE.GF_COMNNAME_FIND('H02001' , A.CRK_CD1)||CASE WHEN A.CRK_CD2 IS NOT NULL THEN '+' ELSE ' ' END ||XB_PACKAGE.GF_COMNNAME_FIND('H02001' , A.CRK_CD2)    COIL_CRK  
	,C.MK_CONTENT    --20121205
	,L.C_ORD_INDIA JQU --卷曲内经
    ,L.HARSHNESS       --粗燥度
    ,L.APPLICATION   --用途
    ,L.L_BMD   GM    --光面、麻面
    ,L.OIL_DEF       --涂油代码
    ,PKG_QUALITY_COMM.FZ00_COMM('A01010' ,L.PAKMTH_TP ) BAO  --包装方式              
	FROM 
	C_TBK02_COIL_COMM A,
	TBE02_ORD_PRC C,
	TBZ00_COMMCD B,
	TBZ00_CUSTOMER D,
	TBZ00_COMMCD E,
	C_TBF03_SPEC_MILL F,
	C_TBF03_SPEC_ROLL G,
	TBZ00_COMMCD H,
	TBZ00_COMMCD I,
	TBA01_ORD_LINE  L    
	WHERE F.ORD_NO=C.ORD_NO 
	AND F.ORD_SEQ=C.ORD_SEQ 
	and F.ORD_NO=L.ORD_NO 
	AND F.ORD_SEQ=L.ORD_SEQ 
	AND A.CUR_PROG_CD=B.SM_CD 
	AND C.ORDCUST_CD=D.CUST_CD 
	AND F.ROLL_MANA_NO=G.ROLL_MANA_NO 
	AND I.LG_CD='A01010' 
	AND C.PACK_METHOD=I.SM_CD(+)  
    AND C.C_ORD_USEAGE_CD=H.SM_CD(+) 
    AND C.DEST_CD=E.SM_CD(+) 
    AND A.COIL_NO=F.COIL_NO 
    AND G.STATUS_CD&lt;>'D' 
    AND F.STATUS_CD IN('A','B','C','D','F') 
    ORDER BY F.COIL_EDT_SEQ
</query>

</queryMap>