| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116 |
- <?xml version="1.0" encoding='UTF-8'?>
- <queryMap desc="STEEL MAKING QUERY ">
- <query id="UIG020600_01.select" desc=" " fetchSize="10">
- <![CDATA[
- SELECT SUBSTR(A.HM_STR_DTIME,1,8) CHARGE_START_DTIME
- ,SUBSTR(B.CASTING_END_DTIME,1,8) CHARGE_END_DTIME
- ,B.CHARGE_NO
- ,B.CAST_NO
- ,B.STL_GRD
- ,C.PLAN_SLAB_CNT
- ,C.PLAN_SLAB_WGT
- ,NVL(C.SHRT_PLAN_SLAB_CNT, 0) SHRT_PLAN_SLAB_CNT --计划生产块数
- ,NVL(C.SHRT_PLAN_SLAB_WGT / 1000, 0) SHRT_PLAN_SLAB_WGT --计划产量
- ,NVL(C.LONG_PLAN_SLAB_CNT, 0) LONG_PLAN_SLAB_CNT --计划生产块数
- ,NVL(C.LONG_PLAN_SLAB_WGT / 1000, 0) LONG_PLAN_SLAB_WGT --计划产量
- ,D.SLAB_CNT
- ,NVL(D.SLAB_WGT,0) SLAB_WGT
- ,E.TOTAL_SLAB_CNT
- ,E.SURP_SLAB_CNT
- ,E.DIFF_SLAB_CNT
- ,E.TW_SLAB_CNT
- ,E.SHAP_SLAB_CNT
- ,E.SIZE_SLAB_CNT
- ,E.INGR_SLAB_CNT
- ,NVL(ROUND(E.TOTAL_NORD_SLAB_CNT / D.SLAB_CNT * 100,2),0) NORD_SLAB_CNT_RATE
- ,NVL(DECODE(C.PLAN_SLAB_WGT,'0','0',ROUND(D.SLAB_WGT/C.PLAN_SLAB_WGT*100,2)),0) SLAB_WGT_RATE
- ,H.YDZ_WGT_MIN||'-'||H.YDZ_WGT_MAX YDZ_WGT
-
- ,H.DEL_TO_DATE
-
- ,H.RCVORD_CLF
-
- ,H.ORD_NO ORD_NOS
- FROM TBF01_SPEC_CHARGE A
- ,TBG02_CHARGE_COMM B
- ,(SELECT T.CHARGE_MANA_NO
- ,COUNT(T.SLAB_MANA_NO) PLAN_SLAB_CNT
- ,SUM(T.SLAB_WGT) PLAN_SLAB_WGT
- ,COUNT(CASE WHEN T.SLAB_LEN <= (SELECT TTT.SM_CD FROM TBZ00_COMMCD TTT
- WHERE TTT.LG_CD LIKE 'D01003'
- AND TTT.CD_SEQ = '2')
- THEN T.CHARGE_MANA_NO
- END ) SHRT_PLAN_SLAB_CNT
- ,SUM(CASE WHEN T.SLAB_LEN <= (SELECT TTT.SM_CD FROM TBZ00_COMMCD TTT
- WHERE TTT.LG_CD LIKE 'D01003'
- AND TTT.CD_SEQ = '2')
- THEN T.SLAB_WGT
- END ) SHRT_PLAN_SLAB_WGT
- ,COUNT(CASE WHEN T.SLAB_LEN > (SELECT TTT.SM_CD FROM TBZ00_COMMCD TTT
- WHERE TTT.LG_CD LIKE 'D01003'
- AND TTT.CD_SEQ = '2')
- THEN T.CHARGE_MANA_NO
- END ) LONG_PLAN_SLAB_CNT
- ,SUM(CASE WHEN T.SLAB_LEN > (SELECT TTT.SM_CD FROM TBZ00_COMMCD TTT
- WHERE TTT.LG_CD LIKE 'D01003'
- AND TTT.CD_SEQ = '2')
- THEN T.SLAB_WGT
- END ) LONG_PLAN_SLAB_WGT
- FROM TBF01_SPEC_SLAB T GROUP BY T.CHARGE_MANA_NO) C
- ,(SELECT SUBSTR(T.SLAB_NO,1,10) CHARGE_NO,COUNT(T.SLAB_NO) SLAB_CNT,SUM(T.SLAB_WGT) SLAB_WGT
- FROM TBG02_SLAB_COMM T
- WHERE T.SLAB_STAT <> '1'
- AND NVL(T.MATRL_END_CAUSE_CD, '1') <> '5'
- GROUP BY SUBSTR(T.SLAB_NO,1,10))D
- ,(SELECT SUBSTR(T.SLAB_NO ,1,10) CHARGE_NO
- ,COUNT(T.SLAB_NO) TOTAL_SLAB_CNT
- ,COUNT(CASE WHEN T.ORD_FL = '2' AND NVL(T.MATRL_END_CAUSE_CD, '1') <> '5' AND T.SLAB_RT = 'H' and t.SLAB_STAT <> '1' THEN T.SLAB_NO END) TOTAL_NORD_SLAB_CNT
- ,COUNT(CASE WHEN T.ORD_FL = '2' AND NVL(T.MATRL_END_CAUSE_CD, '1') <> '5' and t.SLAB_STAT <> '1' THEN DECODE(T.SURPLMATL_YN,'S',T.SLAB_NO) END) SURP_SLAB_CNT
- ,COUNT(CASE WHEN T.ORD_FL = '2' AND NVL(T.MATRL_END_CAUSE_CD, '1') <> '5' and t.SLAB_STAT <> '1' THEN DECODE(T.DIFF_STL_GRD_STA_END_YN,'2',T.SLAB_NO)END) DIFF_SLAB_CNT
- ,COUNT(CASE WHEN T.ORD_FL = '2' AND NVL(T.MATRL_END_CAUSE_CD, '1') <> '5' and t.SLAB_STAT <> '1' THEN DECODE(T.TW_SLAB_YN,'1',T.SLAB_NO,'2',T.SLAB_NO)END) TW_SLAB_CNT
- ,COUNT(CASE WHEN T.ORD_FL = '2' AND NVL(T.MATRL_END_CAUSE_CD, '1') <> '5' and t.SLAB_STAT <> '1' THEN DECODE(T.EXTSHAPE_GRD,'2',T.SLAB_NO)END) SHAP_SLAB_CNT
- ,COUNT(CASE WHEN T.ORD_FL = '2' AND NVL(T.MATRL_END_CAUSE_CD, '1') <> '5' and t.SLAB_STAT <> '1' THEN DECODE(T.SLAB_SIZE_DEC_GRD,'2',T.SLAB_NO)END) SIZE_SLAB_CNT
- ,COUNT(CASE WHEN T.ORD_FL = '2' AND NVL(T.MATRL_END_CAUSE_CD, '1') <> '5' and t.SLAB_STAT <> '1' THEN DECODE(T.SLAB_INGR_DEC_GRD,'2',T.SLAB_NO,'',T.SLAB_NO)END) INGR_SLAB_CNT
- FROM TBG02_SLAB_COMM T
- GROUP BY SUBSTR(T.SLAB_NO,1,10))E
- ,( --合同号
- select charge_no, wmsys.wm_concat(ord_no) ord_no
- ,min(ydz_wgt_max)/1000 ydz_wgt_max
- ,max(ydz_wgt_min)/1000 ydz_wgt_min
- ,min(DEL_TO_DATE) del_to_date
- ,DECODE(MIN(RCVORD_CLF),'0','出口','否')RCVORD_CLF
- from (select distinct substr(t.slab_no, 1, 10) charge_no
- ,(tt.WGT_ACVAL_MAX - tt.ORD_WGT) YDZ_WGT_MAX --溢短装量
- ,(tt.ORD_WGT - tt.WGT_ACVAL_MIN) YDZ_WGT_MIN --溢短装量下限
- ,t.ord_no || t.ord_seq ord_no
- ,tt.DEL_TO_DATE
- ,CASE WHEN tt.RCVORD_CLF = 'W'
- THEN 0
- ELSE 1
- END RCVORD_CLF
- from tbg02_slab_comm t
- ,tbe02_ord_prc tt
- where t.ORD_NO || t.ORD_SEQ is not null
- and t.ORD_NO = tt.ORD_NO
- and t.ORD_SEQ = tt.ORD_SEQ
- AND NVL(T.ORD_NO,' ') LIKE ?||'%' --：3
- AND NVL(T.ORD_SEQ,' ') LIKE ?||'%'
- )
- group by charge_no) H
- WHERE '1' = '1'
- AND A.CHARGE_MANA_NO = B.PLAN_CHARGE_NO
- AND A.CHARGE_MANA_NO = C.CHARGE_MANA_NO
- AND B.CHARGE_NO = D.CHARGE_NO
- AND B.CHARGE_NO = E.CHARGE_NO
- AND B.CHARGE_NO = H.CHARGE_NO(+)
- and decode(?,NULL,0,SUBSTR(H.DEL_TO_DATE,1,8)) BETWEEN NVL(?,0) AND NVL(?,0)
- --AND SUBSTR(H.DEL_TO_DATE, 1, 8) >= nvl(?, '10000000')
- --AND SUBSTR(H.DEL_TO_DATE, 1, 8) <= nvl(?, '50000000')
- AND SUBSTR(B.CASTING_END_DTIME,1,8) >= nvl(?, '10000000')
- AND SUBSTR(B.CASTING_END_DTIME,1,8) <= nvl(?, '50000000')
- AND SUBSTR(A.HM_STR_DTIME, 1, 8) >= NVL(?, '10000000') --:21
- AND SUBSTR(A.HM_STR_DTIME, 1, 8) <= NVL(?, '50000000') --:22
- ORDER BY B.CHARGE_NO
- ]]>
- </query>
- </queryMap>
|
