XinYu_LZMES/.svn/pristine/35/354baa97252f29f88e1b56328daf0d1b4bbe7d77.svn-base

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

<queryMap desc="">
	<query id="UIE041070.COIL.Select" desc="" fetchSize="10">
        <![CDATA[
			SELECT MAT_TP,PROG_CD,MAT_NO,MAT_THK,MAT_WTH,MAT_LEN
			,MAT_WGT,WGT,CUR_LOAD_LOC,REG_DTIME,CD_SEQ,STL_GRD,SHIP_END_DATE
        FROM(
            SELECT 
              '酸轧基料卷' MAT_TP
              ,A.CUR_PROG_CD PROG_CD
              ,A.COIL_NO MAT_NO
              ,A.COIL_THK MAT_THK
              ,A.COIL_WTH MAT_WTH
              ,A.COIL_LEN MAT_LEN
              ,A.ACT_WGT  MAT_WGT
              ,B.WGT
              ,A.CUR_LOAD_LOC
              ,A.YARD_ENTRY_DTIME REG_DTIME
              ,C.CD_SEQ
              ,A.SPEC_STL_GRD STL_GRD
              ,'' SHIP_END_DATE
            FROM C_TBK02_COIL_COMM A
                 ,TBZ00_COMMCD C
                 ,(SELECT COIL_NO,SUM(ORD_WGT) WGT
                  FROM C_TBK02_COIL_COMM_D
                  WHERE ORD_NO = :1
                  AND   ORD_SEQ = :2
                  AND   ORD_FL = '1'
                  GROUP BY COIL_NO)B
            WHERE A.COIL_NO = B.COIL_NO
            AND   A.ORD_FL = '1'
            AND   A.COIL_STAT = '2'
            AND   NVL(:3,A.CUR_PROG_CD) = A.CUR_PROG_CD      
            AND   C.LG_CD = 'E01002'
            AND   A.CUR_PROG_CD = C.SM_CD
        
            UNION ALL 
        
            SELECT 
              '酸轧成品卷' MAT_TP
              ,A.CUR_PROG_CD PROG_CD
              ,A.COIL_NO MAT_NO
              ,A.COIL_THK MAT_THK
              ,A.COIL_WTH MAT_WTH
              ,A.COIL_LEN MAT_LEN
              ,A.ACT_WGT  MAT_WGT
              ,B.WGT
              ,A.CUR_LOAD_LOC
              ,A.YARD_ENTRY_DTIME REG_DTIME
              ,C.CD_SEQ
              ,A.SPEC_STL_GRD STL_GRD
              ,A.TRNF_DTIME SHIP_END_DATE
            FROM C_TBL02_COIL_COMM A
                 ,TBZ00_COMMCD C
                 ,(SELECT L_COIL_NO,SUM(ORD_WGT) WGT
                  FROM C_TBL02_COIL_COMM_D
                  WHERE ORD_NO = :4
                  AND   ORD_SEQ = :5
                  AND   ORD_FL = '1'
                  GROUP BY L_COIL_NO)B
            WHERE A.COIL_NO = B.L_COIL_NO
            AND   A.ORD_FL = '1'
            AND   A.COIL_STAT = '2'
            AND   NVL(:6,A.CUR_PROG_CD) = A.CUR_PROG_CD      
            AND   C.LG_CD = 'E01002'
            AND   A.CUR_PROG_CD = C.SM_CD
            
            
            union all
            
            SELECT 
              '酸轧成品卷' MAT_TP
              ,A.CUR_PROG_CD PROG_CD
              ,A.COIL_NO MAT_NO
              ,A.COIL_THK MAT_THK
              ,A.COIL_WTH MAT_WTH
              ,A.COIL_LEN MAT_LEN
              ,A.ACT_WGT  MAT_WGT
              ,B.WGT
              ,A.CUR_LOAD_LOC
              ,A.YARD_ENTRY_DTIME REG_DTIME
              ,C.CD_SEQ
              ,A.SPEC_STL_GRD STL_GRD
              ,A.TRNF_DTIME SHIP_END_DATE
            FROM C_TBL02_COIL_COMM A
                 ,TBZ00_COMMCD C
                 ,(SELECT L_COIL_NO,SUM(ORD_WGT) WGT
                  FROM C_TBL02_COIL_COMM_D
                  WHERE ORD_NO = :7
                  AND   ORD_SEQ = :8
                  AND   ORD_FL = '1'
                  GROUP BY L_COIL_NO)B
            WHERE A.COIL_NO = B.L_COIL_NO
            AND   A.ORD_FL = '1'
            AND   A.COIL_STAT = '3'
            AND   A.CUR_PROG_CD = NVL(:9,'DFF')     
            AND   C.LG_CD = 'E01002'
            AND   A.CUR_PROG_CD = C.SM_CD
            
            union all
            
            SELECT 
             DECODE(SUBSTR(A.OLD_SAMPL_NO,16),'00','连退成品卷','重卷成品卷') MAT_TP
              ,A.CUR_PROG_CD PROG_CD
              ,A.OLD_SAMPL_NO MAT_NO
              ,A.COIL_THK MAT_THK
              ,A.COIL_WTH MAT_WTH
              ,A.COIL_LEN MAT_LEN
              ,A.ACT_WGT  MAT_WGT
              ,B.WGT
              ,A.CUR_LOAD_LOC
              ,A.YARD_ENTRY_DTIME REG_DTIME
              ,C.CD_SEQ
              ,A.SPEC_STL_GRD STL_GRD
              ,A.TRNF_DTIME SHIP_END_DATE
            FROM C_TBC02_COIL_COMM A
                 ,TBZ00_COMMCD C
                 ,(SELECT OLD_SAMPL_NO L_COIL_NO,SUM(ACT_WGT) WGT
                  FROM C_TBC02_COIL_COMM
                  WHERE ORD_NO = :10
                  AND   ORD_SEQ = :11
                  AND   ORD_FL = '1'
                  GROUP BY OLD_SAMPL_NO )B
            WHERE A.OLD_SAMPL_NO = B.L_COIL_NO
            AND   A.ORD_FL = '1'
            AND   A.COIL_STAT = '2'
            AND   NVL(:12,A.CUR_PROG_CD) = A.CUR_PROG_CD    
            AND   C.LG_CD = 'E01002'
            AND   A.CUR_PROG_CD = C.SM_CD
            
            union all
            
            SELECT 
              DECODE(SUBSTR(A.OLD_SAMPL_NO,16),'00','连退成品卷','重卷成品卷') MAT_TP
              ,A.CUR_PROG_CD PROG_CD
              ,A.OLD_SAMPL_NO MAT_NO
              ,A.COIL_THK MAT_THK
              ,A.COIL_WTH MAT_WTH
              ,A.COIL_LEN MAT_LEN
              ,A.ACT_WGT  MAT_WGT
              ,B.WGT
              ,A.CUR_LOAD_LOC
              ,A.YARD_ENTRY_DTIME REG_DTIME
              ,C.CD_SEQ
              ,A.SPEC_STL_GRD STL_GRD
              ,A.TRNF_DTIME SHIP_END_DATE
            FROM C_TBC02_COIL_COMM A
                 ,TBZ00_COMMCD C
                 ,(SELECT OLD_SAMPL_NO L_COIL_NO,SUM(ACT_WGT) WGT
                  FROM C_TBC02_COIL_COMM
                  WHERE ORD_NO = :13
                  AND   ORD_SEQ = :14
                  AND   ORD_FL = '1'
                  GROUP BY OLD_SAMPL_NO)B
            WHERE A.OLD_SAMPL_NO = B.L_COIL_NO
            AND   A.ORD_FL = '1'
            AND   A.COIL_STAT = '3'
            AND   A.CUR_PROG_CD = NVL(:15,'DFF')     
            AND   C.LG_CD = 'E01002'
            AND   A.CUR_PROG_CD = C.SM_CD
            
            union all
            
            SELECT 
              '酸轧基料卷' MAT_TP
              ,A.CUR_PROG_CD PROG_CD
              ,A.OLD_SAMPL_NO MAT_NO
              ,A.COIL_THK MAT_THK
              ,A.COIL_WTH MAT_WTH
              ,A.COIL_LEN MAT_LEN
              ,A.ACT_WGT  MAT_WGT
              ,B.WGT
              ,A.CUR_LOAD_LOC
              ,A.YARD_ENTRY_DTIME REG_DTIME
              ,C.CD_SEQ
              ,A.SPEC_STL_GRD STL_GRD
              ,A.TRNF_DTIME SHIP_END_DATE
            FROM tbh02_coil_comm A
                 ,TBZ00_COMMCD C
                 ,(SELECT COIL_NO,SUM(ORD_WGT) WGT
                  FROM TBH02_COIL_COMM_D
                  WHERE ORD_NO = :16
                  AND   ORD_SEQ = :17
                  AND   ORD_FL = '1'
                  GROUP BY COIL_NO)B
            WHERE A.COIL_NO = B.COIL_NO
            AND   A.ORD_FL = '1'
            AND   A.COIL_STAT = '3'
            AND   A.CUR_PROG_CD = NVL(:18,'DFF')     
            AND   C.LG_CD = 'E01002'
            AND   A.CUR_PROG_CD = C.SM_CD
            )  
        ORDER BY CD_SEQ,MAT_NO
        
        	
        ]]>
    </query>
</queryMap>