XinYu_LZMES/.svn/pristine/07/07d4ab871d8bcb4c7b97fdfb1e627a9969c3413f.svn-base

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

<queryMap desc="">

<query id="UIF052020.Query_RollPlan_Mill" desc='得到轧辊单元计划的明细信息'>
	SELECT F.CAL_NO ROLL_MANA_NO,
	F.CAL_COIL_SEQ ROLL_COIL_SEQ,
	F.C_COIL_NO COIL_NO,
	F.L_COIL_NO C_COIL_NO,
	A.H_COIL_NO1,
    A1.CJ_REMARK,
    A1.SPM_RMK,
    A1.DEAL_REMARK,
    A1.CPCJ_REMARK,
	A.COIL_THK,
	A.COIL_WTH,
	A.COIL_LEN,
	A.COIL_INDIA,
	A.COIL_OUTDIA,
	A.ACT_WGT,
	Q.C_ORD_INDIA INDIA,--冷轧订单内径
    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,
    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.MILL_DTIME,'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.L_COIL_ID 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
    ,decode(F.CAL_TYPE,'1','正常退火','2','返修','3','过渡') CAL_TYPE
   ,(nvl(F.CAL_ID,0)+1) CAL_CNT 
   ,XB_PACKAGE.GF_COMNNAME_FIND('B02001' , A.CRK_CD1)||CASE WHEN A.CRK_CD2 IS NOT NULL THEN '+' ELSE ' ' END ||XB_PACKAGE.GF_COMNNAME_FIND('B02001' , A.CRK_CD2)    COIL_CRK
   ,(SELECT  CC.CAL_TYPE_TEMP  FROM  C_TBC02_COIL_COMM CC WHERE CC.OLD_SAMPL_NO = F.L_COIL_NO AND ROWNUM = 1 )      CAL_TYPE_TEMP
   ,C.MK_CONTENT --20121205
   ,LWC.VALUE_C
   ,LWC.VALUE_Mn
   ,LWC.VALUE_Si
   ,LWC.VALUE_Ti
   ,A.L3_ROUGH
   ,Q.OIL_DEF
   ,F.CAL_MEMO
    FROM 
    C_TBL02_COIL_COMM A,
    tbh02_coil_comm A1 ,
    TBE02_ORD_PRC C,
    TBZ00_COMMCD B,
    TBZ00_CUSTOMER D,
    TBZ00_COMMCD E,
    L_TBF03_SPEC_MILL F,
    L_TBF03_SPEC_CAL G,
    TBZ00_COMMCD H,
    TBZ00_COMMCD I,
    tba01_ord_line Q
    ,(select FF.C_COIL_NO COIL_NO_LWC,
              max(DECODE(WI.CHEM_CD, 'C', WI.CHEM_VAL)) VALUE_C,
              max(DECODE(WI.CHEM_CD, 'Mn', WI.CHEM_VAL)) VALUE_Mn,
              max(DECODE(WI.CHEM_CD, 'Si', WI.CHEM_VAL)) VALUE_Si,
              max(DECODE(WI.CHEM_CD, 'Ti', WI.CHEM_VAL)) VALUE_Ti
           from C_TBL02_COIL_COMM AC, TBB02_WORK_INGR WI,TBG02_CHARGE_COMM_D CD,
                TBE02_ORD_PRC     CC,
                L_TBF03_SPEC_MILL FF,
                L_TBF03_SPEC_CAL  GG
           where  FF.ORD_NO = CC.ORD_NO
                  AND FF.ORD_SEQ = CC.ORD_SEQ
                  AND FF.CAL_NO = GG.CAL_MANA_NO
                  AND AC.COIL_NO = FF.C_COIL_NO
                  AND GG.STATUS_CD != 'D'
                  AND FF.STATUS_CD IN ('A', 'B', 'C', 'D', 'F')
                  AND WI.CHARGE_NO = CD.CHARGE_NO 
                  AND WI.PROC_CD || WI.CHEM_SEQ = CD.LST_INGR_CD
                  AND  SUBSTR(AC.SLAB_NO, 1, 10) = WI.CHARGE_NO 
                  AND WI.CHEM_CD IN ('C','Mn','Si','Ti')
                  GROUP BY FF.C_COIL_NO) LWC    
    WHERE F.ORD_NO=C.ORD_NO 
    AND F.ORD_SEQ=C.ORD_SEQ 
    AND F.ORD_NO = Q.ORD_NO
   	AND F.ORD_SEQ = Q.ORD_SEQ
    AND A.CUR_PROG_CD=B.SM_CD(+) 
    AND C.ORDCUST_CD=D.CUST_CD 
    AND F.CAL_NO=G.CAL_MANA_NO 
    AND A.COIL_NO = LWC.COIL_NO_LWC(+)
    AND A.H_COIL_NO1 = A1.OLD_SAMPL_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.C_COIL_NO 
    AND G.STATUS_CD &lt;> 'D' 
    AND F.STATUS_CD IN('A','B','C','D','F') 
    ORDER BY F.CAL_NO,F.CAL_COIL_SEQ
</query>

<query id="UIF052020.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="UIF052020.RollPlan_Edit" desc="调整轧辊单元计划" >
		<![CDATA[
			{call C_PKG_UIF_CAL.C_PRD_CALUNIT_EDIT(?,?,?,?,?,?)}
		]]>
</query>
</queryMap>