B.DEL_TO_DATE OR (SYSDATE > TO_DATE(B.DEL_TO_DATE, 'YYYYMMDD') AND B.ORD_PROG_CD IN ('D', 'E', 'F'))) GROUP BY (A.ORD_NO, A.ORD_SEQ, B.ORD_PROG_CD)*/ ---desc:钢卷公共表没数据时 超交货期不能正确显示----------------------------------------------- ---MOD: WXY 2010-07-14---------------------------------- SELECT b.ORD_NO, b.ORD_SEQ, SUM(A.ACT_WGT) WGT_IN_DEL, --'是' AS DEL_TO_DATE_YN CASE WHEN B.SHIP_END_DATE IS NULL AND SYSDATE > TO_DATE(B.DEL_TO_DATE, 'YYYYMMDD') THEN '是' WHEN TO_DATE(B.SHIP_END_DATE, 'YYYYMMDD') > TO_DATE(B.DEL_TO_DATE, 'YYYYMMDD') THEN '是' ELSE '否' END DEL_TO_DATE_YN FROM TBH02_COIL_COMM A, TBE02_ORD_PRC B WHERE A.ORD_NO(+) = B.ORD_NO AND A.ORD_SEQ(+) = B.ORD_SEQ AND nvl(A.ORD_FL,'1') = '1' AND (SUBSTR(A.TRNF_DTIME,1,8) > B.DEL_TO_DATE OR (SYSDATE > TO_DATE(B.DEL_TO_DATE, 'YYYYMMDD') AND B.ORD_PROG_CD IN ('D', 'E', 'F' , 'G'))) --and a.SHIP_PROG_CD = '08' --and a.CUR_PROG_CD = 'SFF' --AND A.COIL_STAT = '3' GROUP BY (b.ORD_NO, b.ORD_SEQ , B.ORD_PROG_CD , B.SHIP_END_DATE ,B.DEL_TO_DATE ) ------------------------------------------------------------------------------------------------------------------- ) M -- 超交货期量表 , (SELECT A.ORD_NO,A.ORD_SEQ,MIN(A.SLAB_CUT_DTIME) SLAB_CUT_DTIME FROM TBG02_SLAB_COMM A GROUP BY (A.ORD_NO,A.ORD_SEQ)) N --炼钢时间(准确的说是该订单第一块板坯的切割时间) ,(SELECT T.UPD_AFT_ORD_NO,T.UPD_AFT_ORD_SEQ,MIN(T.REG_DTIME) E_SLAB_REP_DTIME FROM TBE04_REP_HIS T WHERE T.REP_TYP = 'A' --(余才到订单才) AND T.MAT_TYPE = 'TM' --板坯 GROUP BY (T.UPD_AFT_ORD_NO,T.UPD_AFT_ORD_SEQ)) O --板坯充当最小时间 ,(SELECT T.UPD_AFT_ORD_NO,T.UPD_AFT_ORD_SEQ,MIN(T.REG_DTIME) E_COIL_REP_DTIME FROM TBE04_REP_HIS T WHERE T.REP_TYP = 'A' --(余才到订单才) AND T.MAT_TYPE = 'AR' --钢卷 GROUP BY (T.UPD_AFT_ORD_NO,T.UPD_AFT_ORD_SEQ)) P --钢卷充当最小时间 ,(SELECT T.ORD_NO,T.ORD_SEQ, MIN(T.SLAB_STOCK_ENT_DTIME) SLAB_STOCK_ENT_DTIME FROM TBG02_SLAB_COMM T GROUP BY (T.ORD_NO,T.ORD_SEQ)) Q -- 热轧库入库时间 ,(SELECT A.ORD_NO,A.ORD_SEQ, MIN(B.EXTRACT_DTIME) EXTRACT_DTIME FROM TBG02_SLAB_COMM A, TBH02_REHEATFUR B WHERE A.SLAB_NO = B.SLAB_NO GROUP BY (A.ORD_NO,A.ORD_SEQ)) R --轧制日期(加热炉出炉时间) WHERE A.ORD_NO LIKE :1||'%' -- 参数：合同号 AND A.ORD_SEQ LIKE :2||'%' -- 参数：订单号 AND A.ORD_NO = B.ORD_NO(+) AND A.ORD_SEQ = B.ORD_SEQ(+) AND A.ORD_NO = C.ORD_NO(+) AND A.ORD_SEQ = C.ORD_SEQ(+) AND A.ORD_NO = K.ORD_NO(+) AND A.ORD_SEQ = K.ORD_SEQ(+) AND A.ORD_NO = M.ORD_NO(+) AND A.ORD_SEQ = M.ORD_SEQ(+) AND A.ORD_NO = N.ORD_NO(+) AND A.ORD_SEQ = N.ORD_SEQ(+) AND A.ORD_NO = O.UPD_AFT_ORD_NO(+) AND A.ORD_SEQ = O.UPD_AFT_ORD_SEQ(+) AND A.ORD_NO = P.UPD_AFT_ORD_NO(+) AND A.ORD_SEQ = P.UPD_AFT_ORD_SEQ(+) AND A.ORD_NO = Q.ORD_NO(+) AND A.ORD_SEQ = Q.ORD_SEQ(+) AND A.ORD_NO = R.ORD_NO(+) AND A.ORD_SEQ = R.ORD_SEQ(+) AND A.PRODNM_CD LIKE :3||'%' -- 参数：产品代码 AND (:4 IS NULL OR A.ORD_PROG_CD IN ( -- 参数：订单进度代码 SELECT COLUMN_VALUE FROM TABLE(CAST(XB_PACKAGE.GF_TABSTR(:5) AS TABSTR_T)))) AND A.ABNOR_ORD_CD||'&' LIKE :6||'%' -- 参数：异常订单类型代码 AND A.CUST_CD||'&' LIKE :7||'%' -- 参数：客户代码 AND A.STL_GRD||'&' LIKE :8||'%' -- 参数：厂内牌号 AND A.DEL_TO_DATE BETWEEN :9 AND :10 -- 参数：交货日期（到达） AND (:11 IS NULL OR A.RCVORD_CLF IN ( -- 参数：交货区分 SELECT COLUMN_VALUE FROM TABLE(CAST(XB_PACKAGE.GF_TABSTR(:12) AS TABSTR_T)))) AND NVL(B.ORD_PLAN_DATA,A.DEL_TO_DATE) BETWEEN NVL(:13,'0000000') AND NVL(:14,'99999999') AND B.DLIV_TP||'&' LIKE :15||'%' --运输方式，查询条件TL 091209 AND B.SPEC_STL_GRD||'&' LIKE :16||'%' AND A.KEEPWARM_YN||'&' LIKE :17||'%' AND A.ORD_THK BETWEEN NVL(:18,0) AND NVL(:19,99999999) AND A.ORD_WTH BETWEEN NVL(:20,0) AND NVL(:21,99999999) AND C.REG_DTIME BETWEEN :22||'000000' AND :23||'235959' AND DECODE(M.DEL_TO_DATE_YN, '是' ,'是','否')||'&' LIKE :24||'%' AND INSTR ( DECODE(:25 , NULL , A.ORD_PROG_CD , :26) , A.ORD_PROG_CD) >0 AND C.SPEC_ABBSYM LIKE :27||'%' ORDER BY A.ORD_NO, A.ORD_SEQ ]]>