XinYu_RZMES/.svn/pristine/59/59277dd2aa3b5f2c9ad93f07fccdf7473ddec1aa.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
   <query id="UIG050900_01.select" desc="  " fetchSize="10">
		<![CDATA[
			SELECT A.ORD_NO,
               A.ORD_SEQ,
               A.ORD_WGT / 1000 ORD_WGT,
               C.ACT_WGT,
               A.SPEC_STL_GRD STL_GRD,
               TO_CHAR(A.ORD_THK || '*' || A.ORD_WTH) COIL_THK, 
               P.ORD_PROG_CD,
              (select cust_nm from tbz00_customer where cust_cd=B.CUST_CD)CUST_CD           -- 客户代码 B.CUST_CD,
               ,(select cust_nm from tbz00_customer where cust_cd=B.ORDCUST_CD)ORDCUST_CD  -- 订单代码B.ORDCUST_CD,
              , (select sm_cfnm from tbz00_commcd where sm_cd= A.GET_ON_PCD  and lg_cd='A01013')GET_ON_PCD, 
              ( SELECT SM_CFNM FROM TBZ00_COMMCD WHERE SM_CD= A.DEST_PCD AND LG_CD='A01009')DEST_PCD           -- 目的地 A.DEST_PCD, 
              ,A.GET_OFF_PCD,
              (select sm_cfnm from tbz00_commcd where sm_cd= B.PRDNM_CD  and lg_cd='A01004' ) PRODNM_CD  --B.PRDNM_CD ,
               ,A.ORD_DEVLMT_DATE DEVLMT_DTIME,
             (select sm_cfnm from tbz00_commcd where lg_cd='A01015' and sm_cd= A.EXLV_LINE_CD)EXLV_LINE_CD  ,--  A.EXLV_LINE_CD,
               A.DLIV_TP,
               CASE
                 WHEN NVL(A.BY_SEA_YN, 'N') = 'Y' THEN
                  'TRUE'
                 ELSE
                  'FALSE'
               END BY_SEA_YN,
               (select sm_cfnm from tbz00_commcd where sm_cd= B.RCVORD_TP and lg_cd='A01001' ) RCVORD_TP         -- 订单接受区分B.RCVORD_TP,
              ,(SELECT SM_CFNM FROM TBZ00_COMMCD WHERE SM_CD= A.ARRIVAL_CD AND LG_CD='A01009')ARRIVAL_CD,
               CASE
                 WHEN P.ORD_PROG_CD IN ('A', 'C', 'D', 'E', 'H', 'F') THEN
                  (P.ORD_WGT - P.SHIP_END_WGT) / 1000
                 ELSE
                  0 
               END LEV_WGT,
               nvl(D.PLAN_WGT,0) PLAN_WGT,
			   A.ORD_PLAN_DATA  --新增订单预排期 
          FROM TBA01_ORD_LINE A,
               TBA01_ORD_COMM B,
               TBE02_ORD_PRC P,
               (SELECT ORD_NO  FROM TBE02_ORD_PRC WHERE ORD_NO NOT LIKE 'TSH%'
                AND ORD_PROG_CD IN ('A', 'C', 'D', 'E', 'H', 'F')
                 GROUP BY ORD_NO) Q, (SELECT ORD_NO,ORD_SEQ, SUM(ACT_WGT / 1000) ACT_WGT, SUM(CASE
                    WHEN CUR_PROG_CD = 'SFB' THEN ACT_WGT END) / 1000 PLAN_WGT FROM TBH02_COIL_COMM
                 WHERE SHIP_PROG_CD IS NULL
                   AND DLIV_DIRNO IS NULL
                   AND COIL_STAT = '2'
        ]]>
	</query> 
	<query id="UIG050900_03.select" desc="  " fetchSize="10">
		<![CDATA[
			GROUP BY ORD_NO, ORD_SEQ)   C,
               (SELECT ORD_NO, ORD_SEQ, SUM(ACT_WGT / 1000) PLAN_WGT
                  FROM TBH02_COIL_COMM
                 WHERE CUR_PROG_CD = 'SFB'
                 GROUP BY ORD_NO, ORD_SEQ) D
         WHERE A.ORD_NO = Q.ORD_NO
           AND B.ORD_NO = Q.ORD_NO
           AND A.ORD_NO = P.ORD_NO
           AND A.ORD_SEQ = P.ORD_SEQ
           AND A.ORD_NO = D.ORD_NO(+)
           AND A.ORD_SEQ = D.ORD_SEQ(+)
           AND A.ORD_NO = C.ORD_NO(+)
           AND A.ORD_SEQ = C.ORD_SEQ(+)
           AND B.ORD_NO NOT LIKE 'TS%'
		   and C.ACT_WGT > 0	
       AND B.RCVORD_TP NOT IN ('T') --测试订单
       AND A.DLIV_TP LIKE ?||'%'
       AND A.ORD_NO||'&' LIKE ?||'%'
       AND A.SPEC_STL_GRD||'&' LIKE ?||'%'
         ORDER BY A.ORD_NO, A.ORD_SEQ
        ]]>
	</query> 
	<query id="UIG050900_02.select" desc="  " fetchSize="10">
		<![CDATA[
			SELECT C.ORD_NO||c.ord_seq ORD_NO 
      			   , C.COIL_NO
				   , DECODE(C.TOT_DEC_GRD,'1','合格')TOT_DEC_GRD
				   , TO_CHAR(F.ORD_THK||'*'||F.ORD_WTH) COIL_THK 
				   , TO_CHAR(C.COIL_THK||'*'||C.COIL_WTH) COIL_WTH 
				   , TO_CHAR(C.COIL_LEN) COIL_LTH
				   , C.ACT_WGT
				   , D.DEPARTURE_CD
				   , F.ARRIVAL_CD
				   , C.CUR_LOAD_LOC
				   , E.SHIP_DIRBY_CD
				   , E.SHIP_DIR_DATE
				   , E.SHIP_DIRNO 
				   , H.CUST_CD
				   , H.ORDCUST_CD
				   , CASE WHEN NVL(F.BY_SEA_YN,'N')='Y' THEN 'TRUE' ELSE 'FALSE' END BY_SEA_YN 
				   , D.TRAIN_DLIVNO   
				   , C.OLD_SAMPL_NO 
				   , C.SHIP_PROG_CD 
			FROM   (  SELECT * FROM TBH02_COIL_COMM 
					  WHERE  COIL_STAT='2'  
					  AND ORD_NO= ?
					  AND ORD_SEQ= ?  
					  AND    (SHIP_PROG_CD IS NULL OR SHIP_PROG_CD ='01' OR SHIP_PROG_CD='02' OR SHIP_PROG_CD='03')
				   ) C
				   ,TBJ01_DLIV_DIR D
				   ,TBJ01_SHIP_DIR E 
				   ,TBA01_ORD_LINE F 
				   ,TBA01_ORD_COMM H
			WHERE  C.SHIP_DIRNO=E.SHIP_DIRNO(+)  
			AND    C.DLIV_DIRNO=D.DLIV_DIRNO(+) 
			AND    C.ORD_NO=F.ORD_NO(+) 
			AND    C.ORD_NO=H.ORD_NO(+) 
			AND    C.ORD_SEQ=F.ORD_SEQ(+)
        ]]>
	</query>
</queryMap>