| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735 |
- <?xml version="1.0" encoding='UTF-8'?>
- <queryMap desc="各钢卷厚度板坯宽度物料分析现况">
- <query id="UIF022020.countSlabWthLenByK" desc="根据块数统计板坯长度宽度" fetchSize="10">
- <![CDATA[
- /*****************************UIF022020.countSlabWthLenByK************************************************/
- SELECT CASE WHEN ORD_FL IS NULL THEN '合计'
- WHEN ORD_FL IS NOT NULL THEN TO_CHAR(ORD_FL) END ORD_FL
- ,SLAB_LEN
- ,SLAB_CNT750
- ,SLAB_CNT800
- ,SLAB_CNT850
- ,SLAB_CNT900
- ,SLAB_CNT950
- ,SLAB_CNT1000
- ,SLAB_CNT1050
- ,SLAB_CNT1100
- ,SLAB_CNT1150
- ,SLAB_CNT1200
- ,SLAB_CNT1250
- ,SLAB_CNT1300
- ,SLAB_CNT1350
- ,SLAB_CNT1400
- ,SLAB_CNT1450
- ,SLAB_CNT_BIG1450
- ,SUM_CNT
- FROM
- (SELECT CASE WHEN ORD_FL IS NOT NULL THEN DECODE(ORD_FL,'1','订单物料','2','余材') END ORD_FL
- ,DECODE(SLAB_LEN,5300,'4500~5300',9600,'5300~9600',11000,'9600~11000') SLAB_LEN
- ,SLAB_CNT750
- ,SLAB_CNT800
- ,SLAB_CNT850
- ,SLAB_CNT900
- ,SLAB_CNT950
- ,SLAB_CNT1000
- ,SLAB_CNT1050
- ,SLAB_CNT1100
- ,SLAB_CNT1150
- ,SLAB_CNT1200
- ,SLAB_CNT1250
- ,SLAB_CNT1300
- ,SLAB_CNT1350
- ,SLAB_CNT1400
- ,SLAB_CNT1450
- ,SLAB_CNT_BIG1450
- ,SUM_CNT
- FROM
-
- (SELECT ORD_FL
- ,SLAB_LEN
- ,SUM(NVL(SLAB_CNT750,'')) SLAB_CNT750
- ,SUM(NVL(SLAB_CNT800,'')) SLAB_CNT800
- ,SUM(NVL(SLAB_CNT850,'')) SLAB_CNT850
- ,SUM(NVL(SLAB_CNT900,'')) SLAB_CNT900
- ,SUM(NVL(SLAB_CNT950,'')) SLAB_CNT950
- ,SUM(NVL(SLAB_CNT1000,'')) SLAB_CNT1000
- ,SUM(NVL(SLAB_CNT1050,'')) SLAB_CNT1050
- ,SUM(NVL(SLAB_CNT1100,'')) SLAB_CNT1100
- ,SUM(NVL(SLAB_CNT1150,'')) SLAB_CNT1150
- ,SUM(NVL(SLAB_CNT1200,'')) SLAB_CNT1200
- ,SUM(NVL(SLAB_CNT1250,'')) SLAB_CNT1250
- ,SUM(NVL(SLAB_CNT1300,'')) SLAB_CNT1300
- ,SUM(NVL(SLAB_CNT1350,'')) SLAB_CNT1350
- ,SUM(NVL(SLAB_CNT1400,'')) SLAB_CNT1400
- ,SUM(NVL(SLAB_CNT1450,'')) SLAB_CNT1450
- ,SUM(NVL(SLAB_CNT_BIG1450,'')) SLAB_CNT_BIG1450
- ,(SUM(NVL(SLAB_CNT750,0)) +
- SUM(NVL(SLAB_CNT800,0)) +
- SUM(NVL(SLAB_CNT850,0)) +
- SUM(NVL(SLAB_CNT900,0)) +
- SUM(NVL(SLAB_CNT950,0)) +
- SUM(NVL(SLAB_CNT1000,0)) +
- SUM(NVL(SLAB_CNT1050,0)) +
- SUM(NVL(SLAB_CNT1100,0)) +
- SUM(NVL(SLAB_CNT1150,0)) +
- SUM(NVL(SLAB_CNT1200,0)) +
- SUM(NVL(SLAB_CNT1250,0)) +
- SUM(NVL(SLAB_CNT1300,0)) +
- SUM(NVL(SLAB_CNT1350,0)) +
- SUM(NVL(SLAB_CNT1400,0)) +
- SUM(NVL(SLAB_CNT1450,0)) +
- SUM(NVL(SLAB_CNT_BIG1450,0))) SUM_CNT
-
- FROM (SELECT ORD_FL
- ,SLAB_LEN
- ,SUM(NVL(SLAB_CNT750,'')) SLAB_CNT750
- ,SUM(NVL(SLAB_CNT800,'')) SLAB_CNT800
- ,SUM(NVL(SLAB_CNT850,'')) SLAB_CNT850
- ,SUM(NVL(SLAB_CNT900,'')) SLAB_CNT900
- ,SUM(NVL(SLAB_CNT950,'')) SLAB_CNT950
- ,SUM(NVL(SLAB_CNT1000,'')) SLAB_CNT1000
- ,SUM(NVL(SLAB_CNT1050,'')) SLAB_CNT1050
- ,SUM(NVL(SLAB_CNT1100,'')) SLAB_CNT1100
- ,SUM(NVL(SLAB_CNT1150,'')) SLAB_CNT1150
- ,SUM(NVL(SLAB_CNT1200,'')) SLAB_CNT1200
- ,SUM(NVL(SLAB_CNT1250,'')) SLAB_CNT1250
- ,SUM(NVL(SLAB_CNT1300,'')) SLAB_CNT1300
- ,SUM(NVL(SLAB_CNT1350,'')) SLAB_CNT1350
- ,SUM(NVL(SLAB_CNT1400,'')) SLAB_CNT1400
- ,SUM(NVL(SLAB_CNT1450,'')) SLAB_CNT1450
- ,SUM(NVL(SLAB_CNT_BIG1450,'')) SLAB_CNT_BIG1450
- FROM (SELECT T1.ORD_FL
- ,CASE WHEN 4500 <= T1.SLAB_LEN AND T1.SLAB_LEN <= 5300 THEN '5300'
- WHEN 5300 < T1.SLAB_LEN AND T1.SLAB_LEN < 9600 THEN '9600'
- WHEN 9600 <= T1.SLAB_LEN AND T1.SLAB_LEN <= 11000 THEN '11000' END SLAB_LEN
- ,CASE WHEN 0 <= T1.SLAB_WTH AND T1.SLAB_WTH <= 750 THEN '1' END SLAB_CNT750
- ,CASE WHEN 750 < T1.SLAB_WTH AND T1.SLAB_WTH <= 800 THEN '1' END SLAB_CNT800
- ,CASE WHEN 800 < T1.SLAB_WTH AND T1.SLAB_WTH <= 850 THEN '1' END SLAB_CNT850
- ,CASE WHEN 850 < T1.SLAB_WTH AND T1.SLAB_WTH <= 900 THEN '1' END SLAB_CNT900
- ,CASE WHEN 900 < T1.SLAB_WTH AND T1.SLAB_WTH <= 950 THEN '1' END SLAB_CNT950
- ,CASE WHEN 950 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1000 THEN '1' END SLAB_CNT1000
- ,CASE WHEN 1000 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1050 THEN '1' END SLAB_CNT1050
- ,CASE WHEN 1050 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1100 THEN '1' END SLAB_CNT1100
- ,CASE WHEN 1100 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1150 THEN '1' END SLAB_CNT1150
- ,CASE WHEN 1150 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1200 THEN '1' END SLAB_CNT1200
- ,CASE WHEN 1200 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1250 THEN '1' END SLAB_CNT1250
- ,CASE WHEN 1250 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1300 THEN '1' END SLAB_CNT1300
- ,CASE WHEN 1300 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1350 THEN '1' END SLAB_CNT1350
- ,CASE WHEN 1350 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1400 THEN '1' END SLAB_CNT1400
- ,CASE WHEN 1400 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1450 THEN '1' END SLAB_CNT1450
- ,CASE WHEN T1.SLAB_WTH > 1450 THEN '1' END SLAB_CNT_BIG1450
- --------------------------------------------------------------------------------------------------------------
- --MODIFY BY SJW 090829
- --DESC :防止订单材 TBG02_SLAB_COMM_D 中没有板坯记录的情况
- --VERSION:1.9-->1.20
- FROM TBG02_SLAB_COMM T1
- ,TBG02_SLAB_COMM_D T2
- WHERE T1.SLAB_NO = T2.SLAB_NO
- AND T1.ORD_FL = T2.ORD_FL
- AND T1.ORD_NO = T2.ORD_NO
- AND T1.ORD_SEQ = T2.ORD_SEQ
- AND T1.ORD_FL = '1'
- ---------------------------------------------------------------------------------------------------------------
- AND T1.SLAB_STAT = '2'
- AND T1.SLAB_RT IN ('H','C')
- AND T1.CUR_LOAD_LOC LIKE 'Z%'
-
- AND T1.SLAB_RT LIKE ? || '%'
- AND T1.STL_GRD LIKE ? || '%'
- AND (T1.SLAB_LEN BETWEEN 4500 AND 11000)
- AND T1.CUR_PROG_CD IN ('RBA','RBB','RAB')
- AND T1.CUR_PROG_CD LIKE ? || '%'
- UNION ALL
- SELECT T1.ORD_FL
- ,CASE WHEN 4500 <= T1.SLAB_LEN AND T1.SLAB_LEN <= 5300 THEN '5300'
- WHEN 5300 < T1.SLAB_LEN AND T1.SLAB_LEN < 9600 THEN '9600'
- WHEN 9600 <= T1.SLAB_LEN AND T1.SLAB_LEN <= 11000 THEN '11000' END SLAB_LEN
- ,CASE WHEN 0 <= T1.SLAB_WTH AND T1.SLAB_WTH <= 750 THEN '1' END SLAB_CNT750
- ,CASE WHEN 750 < T1.SLAB_WTH AND T1.SLAB_WTH <= 800 THEN '1' END SLAB_CNT800
- ,CASE WHEN 800 < T1.SLAB_WTH AND T1.SLAB_WTH <= 850 THEN '1' END SLAB_CNT850
- ,CASE WHEN 850 < T1.SLAB_WTH AND T1.SLAB_WTH <= 900 THEN '1' END SLAB_CNT900
- ,CASE WHEN 900 < T1.SLAB_WTH AND T1.SLAB_WTH <= 950 THEN '1' END SLAB_CNT950
- ,CASE WHEN 950 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1000 THEN '1' END SLAB_CNT1000
- ,CASE WHEN 1000 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1050 THEN '1' END SLAB_CNT1050
- ,CASE WHEN 1050 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1100 THEN '1' END SLAB_CNT1100
- ,CASE WHEN 1100 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1150 THEN '1' END SLAB_CNT1150
- ,CASE WHEN 1150 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1200 THEN '1' END SLAB_CNT1200
- ,CASE WHEN 1200 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1250 THEN '1' END SLAB_CNT1250
- ,CASE WHEN 1250 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1300 THEN '1' END SLAB_CNT1300
- ,CASE WHEN 1300 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1350 THEN '1' END SLAB_CNT1350
- ,CASE WHEN 1350 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1400 THEN '1' END SLAB_CNT1400
- ,CASE WHEN 1400 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1450 THEN '1' END SLAB_CNT1450
- ,CASE WHEN T1.SLAB_WTH > 1450 THEN '1' END SLAB_CNT_BIG1450
- FROM TBG02_SLAB_COMM T1
- WHERE T1.ORD_FL = '2'
- AND T1.SLAB_STAT = '2'
- AND T1.SLAB_RT IN ('H','C')
- AND T1.CUR_LOAD_LOC LIKE 'Z%'
- AND T1.SLAB_RT LIKE ? || '%'
- AND T1.STL_GRD LIKE ? || '%'
- AND (T1.SLAB_LEN BETWEEN 4500 AND 11000)
- AND T1.CUR_PROG_CD IN ('RRC','RAB')
- AND T1.CUR_PROG_CD LIKE ? || '%'
- UNION ALL
- SELECT '1' ORD_FL
- ,'5300' SLAB_LEN
- ,'' SLAB_CNT750
- ,'' SLAB_CNT800
- ,'' SLAB_CNT850
- ,'' SLAB_CNT900
- ,'' SLAB_CNT950
- ,'' SLAB_CNT1000
- ,'' SLAB_CNT1050
- ,'' SLAB_CNT1100
- ,'' SLAB_CNT1150
- ,'' SLAB_CNT1200
- ,'' SLAB_CNT1250
- ,'' SLAB_CNT1300
- ,'' SLAB_CNT1350
- ,'' SLAB_CNT1400
- ,'' SLAB_CNT1450
- ,'' SLAB_CNT_BIG1450
- FROM dual
- UNION ALL
- SELECT '1' ORD_FL
- ,'11000' SLAB_LEN
- ,'' SLAB_CNT750
- ,'' SLAB_CNT800
- ,'' SLAB_CNT850
- ,'' SLAB_CNT900
- ,'' SLAB_CNT950
- ,'' SLAB_CNT1000
- ,'' SLAB_CNT1050
- ,'' SLAB_CNT1100
- ,'' SLAB_CNT1150
- ,'' SLAB_CNT1200
- ,'' SLAB_CNT1250
- ,'' SLAB_CNT1300
- ,'' SLAB_CNT1350
- ,'' SLAB_CNT1400
- ,'' SLAB_CNT1450
- ,'' SLAB_CNT_BIG1450
- FROM dual
- UNION ALL
- SELECT '1' ORD_FL
- ,'9600' SLAB_LEN
- ,'' SLAB_CNT750
- ,'' SLAB_CNT800
- ,'' SLAB_CNT850
- ,'' SLAB_CNT900
- ,'' SLAB_CNT950
- ,'' SLAB_CNT1000
- ,'' SLAB_CNT1050
- ,'' SLAB_CNT1100
- ,'' SLAB_CNT1150
- ,'' SLAB_CNT1200
- ,'' SLAB_CNT1250
- ,'' SLAB_CNT1300
- ,'' SLAB_CNT1350
- ,'' SLAB_CNT1400
- ,'' SLAB_CNT1450
- ,'' SLAB_CNT_BIG1450
- FROM dual
- UNION ALL
- SELECT '2' ORD_FL
- ,'5300' SLAB_LEN
- ,'' SLAB_CNT750
- ,'' SLAB_CNT800
- ,'' SLAB_CNT850
- ,'' SLAB_CNT900
- ,'' SLAB_CNT950
- ,'' SLAB_CNT1000
- ,'' SLAB_CNT1050
- ,'' SLAB_CNT1100
- ,'' SLAB_CNT1150
- ,'' SLAB_CNT1200
- ,'' SLAB_CNT1250
- ,'' SLAB_CNT1300
- ,'' SLAB_CNT1350
- ,'' SLAB_CNT1400
- ,'' SLAB_CNT1450
- ,'' SLAB_CNT_BIG1450
- FROM dual
- UNION ALL
- SELECT '2' ORD_FL
- ,'11000' SLAB_LEN
- ,'' SLAB_CNT750
- ,'' SLAB_CNT800
- ,'' SLAB_CNT850
- ,'' SLAB_CNT900
- ,'' SLAB_CNT950
- ,'' SLAB_CNT1000
- ,'' SLAB_CNT1050
- ,'' SLAB_CNT1100
- ,'' SLAB_CNT1150
- ,'' SLAB_CNT1200
- ,'' SLAB_CNT1250
- ,'' SLAB_CNT1300
- ,'' SLAB_CNT1350
- ,'' SLAB_CNT1400
- ,'' SLAB_CNT1450
- ,'' SLAB_CNT_BIG1450
- FROM dual
- UNION ALL
- SELECT '2' ORD_FL
- ,'9600' SLAB_LEN
- ,'' SLAB_CNT750
- ,'' SLAB_CNT800
- ,'' SLAB_CNT850
- ,'' SLAB_CNT900
- ,'' SLAB_CNT950
- ,'' SLAB_CNT1000
- ,'' SLAB_CNT1050
- ,'' SLAB_CNT1100
- ,'' SLAB_CNT1150
- ,'' SLAB_CNT1200
- ,'' SLAB_CNT1250
- ,'' SLAB_CNT1300
- ,'' SLAB_CNT1350
- ,'' SLAB_CNT1400
- ,'' SLAB_CNT1450
- ,'' SLAB_CNT_BIG1450
- FROM dual
- ) M
-
- GROUP BY ORD_FL
- ,SLAB_LEN)
-
- GROUP BY CUBE(ORD_FL,SLAB_LEN)
- HAVING SLAB_LEN IS NOT NULL
- )
- ORDER BY ORD_FL,SLAB_LEN
- )
-
- /*****************************UIF022020.countSlabWthLenByK************************************************/
-
- ]]>
- </query>
-
- <query id="UIF022020.countSlabWthLenByW" desc="根据重量统计板坯长度宽度" fetchSize="10">
- <![CDATA[
- /*****************************UIF022020.countSlabWthLenByW************************************************/
- SELECT CASE WHEN ORD_FL IS NULL THEN '合计'
- WHEN ORD_FL IS NOT NULL THEN TO_CHAR(ORD_FL) END ORD_FL
- ,SLAB_LEN
- ,SLAB_CNT750
- ,SLAB_CNT800
- ,SLAB_CNT850
- ,SLAB_CNT900
- ,SLAB_CNT950
- ,SLAB_CNT1000
- ,SLAB_CNT1050
- ,SLAB_CNT1100
- ,SLAB_CNT1150
- ,SLAB_CNT1200
- ,SLAB_CNT1250
- ,SLAB_CNT1300
- ,SLAB_CNT1350
- ,SLAB_CNT1400
- ,SLAB_CNT1450
- ,SLAB_CNT_BIG1450
- ,SUM_CNT
- FROM
- (SELECT CASE WHEN ORD_FL IS NOT NULL THEN DECODE(ORD_FL,'1','订单物料','2','余材') END ORD_FL
- ,DECODE(SLAB_LEN,5300,'4500~5300',9600,'5300~9600',11000,'9600~11000') SLAB_LEN
- ,SLAB_CNT750
- ,SLAB_CNT800
- ,SLAB_CNT850
- ,SLAB_CNT900
- ,SLAB_CNT950
- ,SLAB_CNT1000
- ,SLAB_CNT1050
- ,SLAB_CNT1100
- ,SLAB_CNT1150
- ,SLAB_CNT1200
- ,SLAB_CNT1250
- ,SLAB_CNT1300
- ,SLAB_CNT1350
- ,SLAB_CNT1400
- ,SLAB_CNT1450
- ,SLAB_CNT_BIG1450
- ,SUM_CNT
- FROM
-
- (SELECT ORD_FL
- ,SLAB_LEN
- ,SUM(NVL(SLAB_CNT750,'')) SLAB_CNT750
- ,SUM(NVL(SLAB_CNT800,'')) SLAB_CNT800
- ,SUM(NVL(SLAB_CNT850,'')) SLAB_CNT850
- ,SUM(NVL(SLAB_CNT900,'')) SLAB_CNT900
- ,SUM(NVL(SLAB_CNT950,'')) SLAB_CNT950
- ,SUM(NVL(SLAB_CNT1000,'')) SLAB_CNT1000
- ,SUM(NVL(SLAB_CNT1050,'')) SLAB_CNT1050
- ,SUM(NVL(SLAB_CNT1100,'')) SLAB_CNT1100
- ,SUM(NVL(SLAB_CNT1150,'')) SLAB_CNT1150
- ,SUM(NVL(SLAB_CNT1200,'')) SLAB_CNT1200
- ,SUM(NVL(SLAB_CNT1250,'')) SLAB_CNT1250
- ,SUM(NVL(SLAB_CNT1300,'')) SLAB_CNT1300
- ,SUM(NVL(SLAB_CNT1350,'')) SLAB_CNT1350
- ,SUM(NVL(SLAB_CNT1400,'')) SLAB_CNT1400
- ,SUM(NVL(SLAB_CNT1450,'')) SLAB_CNT1450
- ,SUM(NVL(SLAB_CNT_BIG1450,'')) SLAB_CNT_BIG1450
- ,(SUM(NVL(SLAB_CNT750,0)) +
- SUM(NVL(SLAB_CNT800,0)) +
- SUM(NVL(SLAB_CNT850,0)) +
- SUM(NVL(SLAB_CNT900,0)) +
- SUM(NVL(SLAB_CNT950,0)) +
- SUM(NVL(SLAB_CNT1000,0)) +
- SUM(NVL(SLAB_CNT1050,0)) +
- SUM(NVL(SLAB_CNT1100,0)) +
- SUM(NVL(SLAB_CNT1150,0)) +
- SUM(NVL(SLAB_CNT1200,0)) +
- SUM(NVL(SLAB_CNT1250,0)) +
- SUM(NVL(SLAB_CNT1300,0)) +
- SUM(NVL(SLAB_CNT1350,0)) +
- SUM(NVL(SLAB_CNT1400,0)) +
- SUM(NVL(SLAB_CNT1450,0)) +
- SUM(NVL(SLAB_CNT_BIG1450,0))) SUM_CNT
-
- FROM (SELECT ORD_FL
- ,SLAB_LEN
- ,SUM(NVL(SLAB_CNT750,'')) SLAB_CNT750
- ,SUM(NVL(SLAB_CNT800,'')) SLAB_CNT800
- ,SUM(NVL(SLAB_CNT850,'')) SLAB_CNT850
- ,SUM(NVL(SLAB_CNT900,'')) SLAB_CNT900
- ,SUM(NVL(SLAB_CNT950,'')) SLAB_CNT950
- ,SUM(NVL(SLAB_CNT1000,'')) SLAB_CNT1000
- ,SUM(NVL(SLAB_CNT1050,'')) SLAB_CNT1050
- ,SUM(NVL(SLAB_CNT1100,'')) SLAB_CNT1100
- ,SUM(NVL(SLAB_CNT1150,'')) SLAB_CNT1150
- ,SUM(NVL(SLAB_CNT1200,'')) SLAB_CNT1200
- ,SUM(NVL(SLAB_CNT1250,'')) SLAB_CNT1250
- ,SUM(NVL(SLAB_CNT1300,'')) SLAB_CNT1300
- ,SUM(NVL(SLAB_CNT1350,'')) SLAB_CNT1350
- ,SUM(NVL(SLAB_CNT1400,'')) SLAB_CNT1400
- ,SUM(NVL(SLAB_CNT1450,'')) SLAB_CNT1450
- ,SUM(NVL(SLAB_CNT_BIG1450,'')) SLAB_CNT_BIG1450
- FROM (SELECT T1.ORD_FL
- ,CASE WHEN 4500 <= T1.SLAB_LEN AND T1.SLAB_LEN <= 5300 THEN '5300'
- WHEN 5300 < T1.SLAB_LEN AND T1.SLAB_LEN < 9600 THEN '9600'
- WHEN 9600 <= T1.SLAB_LEN AND T1.SLAB_LEN <= 11000 THEN '11000' END SLAB_LEN
- ,CASE WHEN 0 <= T1.SLAB_WTH AND T1.SLAB_WTH <= 750 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT750
- ,CASE WHEN 750 < T1.SLAB_WTH AND T1.SLAB_WTH <= 800 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT800
- ,CASE WHEN 800 < T1.SLAB_WTH AND T1.SLAB_WTH <= 850 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT850
- ,CASE WHEN 850 < T1.SLAB_WTH AND T1.SLAB_WTH <= 900 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT900
- ,CASE WHEN 900 < T1.SLAB_WTH AND T1.SLAB_WTH <= 950 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT950
- ,CASE WHEN 950 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1000 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1000
- ,CASE WHEN 1000 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1050 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1050
- ,CASE WHEN 1050 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1100 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1100
- ,CASE WHEN 1100 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1150 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1150
- ,CASE WHEN 1150 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1200 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1200
- ,CASE WHEN 1200 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1250 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1250
- ,CASE WHEN 1250 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1300 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1300
- ,CASE WHEN 1300 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1350 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1350
- ,CASE WHEN 1350 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1400 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1400
- ,CASE WHEN 1400 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1450 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1450
- ,CASE WHEN T1.SLAB_WTH > 1450 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT_BIG1450
- --------------------------------------------------------------------------------------------------------------
- --MODIFY BY SJW 090829
- --DESC :防止订单材 TBG02_SLAB_COMM_D 中没有板坯记录的情况
- --VERSION:1.9-->1.20
- FROM TBG02_SLAB_COMM T1
- ,TBG02_SLAB_COMM_D T2
- WHERE T1.SLAB_NO = T2.SLAB_NO
- AND T1.ORD_FL = T2.ORD_FL
- AND T1.ORD_NO = T2.ORD_NO
- AND T1.ORD_SEQ = T2.ORD_SEQ
- AND T1.ORD_FL = '1'
- ---------------------------------------------------------------------------------------------------------------
- AND T1.SLAB_STAT = '2'
- AND T1.SLAB_RT IN ('H','C')
- AND T1.CUR_LOAD_LOC LIKE 'Z%'
-
- AND T1.SLAB_RT LIKE ? || '%'
- AND T1.STL_GRD LIKE ? || '%'
- AND (T1.SLAB_LEN BETWEEN 4500 AND 11000)
- AND T1.CUR_PROG_CD IN ('RBA','RBB','RAB')
- AND T1.CUR_PROG_CD LIKE ? || '%'
- UNION ALL
- SELECT T1.ORD_FL
- ,CASE WHEN 4500 <= T1.SLAB_LEN AND T1.SLAB_LEN <= 5300 THEN '5300'
- WHEN 5300 < T1.SLAB_LEN AND T1.SLAB_LEN < 9600 THEN '9600'
- WHEN 9600 <= T1.SLAB_LEN AND T1.SLAB_LEN <= 11000 THEN '11000' END SLAB_LEN
- ,CASE WHEN 0 <= T1.SLAB_WTH AND T1.SLAB_WTH <= 750 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT750
- ,CASE WHEN 750 < T1.SLAB_WTH AND T1.SLAB_WTH <= 800 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT800
- ,CASE WHEN 800 < T1.SLAB_WTH AND T1.SLAB_WTH <= 850 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT850
- ,CASE WHEN 850 < T1.SLAB_WTH AND T1.SLAB_WTH <= 900 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT900
- ,CASE WHEN 900 < T1.SLAB_WTH AND T1.SLAB_WTH <= 950 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT950
- ,CASE WHEN 950 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1000 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1000
- ,CASE WHEN 1000 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1050 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1050
- ,CASE WHEN 1050 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1100 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1100
- ,CASE WHEN 1100 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1150 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1150
- ,CASE WHEN 1150 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1200 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1200
- ,CASE WHEN 1200 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1250 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1250
- ,CASE WHEN 1250 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1300 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1300
- ,CASE WHEN 1300 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1350 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1350
- ,CASE WHEN 1350 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1400 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1400
- ,CASE WHEN 1400 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1450 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT1450
- ,CASE WHEN T1.SLAB_WTH > 1450 THEN TO_CHAR(T1.SLAB_WGT) END SLAB_CNT_BIG1450
- FROM TBG02_SLAB_COMM T1
- WHERE T1.ORD_FL = '2'
- AND T1.SLAB_STAT = '2'
- AND T1.SLAB_RT IN ('H','C')
- AND T1.CUR_LOAD_LOC LIKE 'Z%'
- AND T1.SLAB_RT LIKE ? || '%'
- AND T1.STL_GRD LIKE ? || '%'
- AND (T1.SLAB_LEN BETWEEN 4500 AND 11000)
- AND T1.CUR_PROG_CD IN ('RRC','RAB')
- AND T1.CUR_PROG_CD LIKE ? || '%'
- UNION ALL
- SELECT '1' ORD_FL
- ,'5300' SLAB_LEN
- ,'' SLAB_CNT750
- ,'' SLAB_CNT800
- ,'' SLAB_CNT850
- ,'' SLAB_CNT900
- ,'' SLAB_CNT950
- ,'' SLAB_CNT1000
- ,'' SLAB_CNT1050
- ,'' SLAB_CNT1100
- ,'' SLAB_CNT1150
- ,'' SLAB_CNT1200
- ,'' SLAB_CNT1250
- ,'' SLAB_CNT1300
- ,'' SLAB_CNT1350
- ,'' SLAB_CNT1400
- ,'' SLAB_CNT1450
- ,'' SLAB_CNT_BIG1450
- FROM dual
- UNION ALL
- SELECT '1' ORD_FL
- ,'11000' SLAB_LEN
- ,'' SLAB_CNT750
- ,'' SLAB_CNT800
- ,'' SLAB_CNT850
- ,'' SLAB_CNT900
- ,'' SLAB_CNT950
- ,'' SLAB_CNT1000
- ,'' SLAB_CNT1050
- ,'' SLAB_CNT1100
- ,'' SLAB_CNT1150
- ,'' SLAB_CNT1200
- ,'' SLAB_CNT1250
- ,'' SLAB_CNT1300
- ,'' SLAB_CNT1350
- ,'' SLAB_CNT1400
- ,'' SLAB_CNT1450
- ,'' SLAB_CNT_BIG1450
- FROM dual
- UNION ALL
- SELECT '1' ORD_FL
- ,'9600' SLAB_LEN
- ,'' SLAB_CNT750
- ,'' SLAB_CNT800
- ,'' SLAB_CNT850
- ,'' SLAB_CNT900
- ,'' SLAB_CNT950
- ,'' SLAB_CNT1000
- ,'' SLAB_CNT1050
- ,'' SLAB_CNT1100
- ,'' SLAB_CNT1150
- ,'' SLAB_CNT1200
- ,'' SLAB_CNT1250
- ,'' SLAB_CNT1300
- ,'' SLAB_CNT1350
- ,'' SLAB_CNT1400
- ,'' SLAB_CNT1450
- ,'' SLAB_CNT_BIG1450
- FROM dual
- UNION ALL
- SELECT '2' ORD_FL
- ,'5300' SLAB_LEN
- ,'' SLAB_CNT750
- ,'' SLAB_CNT800
- ,'' SLAB_CNT850
- ,'' SLAB_CNT900
- ,'' SLAB_CNT950
- ,'' SLAB_CNT1000
- ,'' SLAB_CNT1050
- ,'' SLAB_CNT1100
- ,'' SLAB_CNT1150
- ,'' SLAB_CNT1200
- ,'' SLAB_CNT1250
- ,'' SLAB_CNT1300
- ,'' SLAB_CNT1350
- ,'' SLAB_CNT1400
- ,'' SLAB_CNT1450
- ,'' SLAB_CNT_BIG1450
- FROM dual
- UNION ALL
- SELECT '2' ORD_FL
- ,'11000' SLAB_LEN
- ,'' SLAB_CNT750
- ,'' SLAB_CNT800
- ,'' SLAB_CNT850
- ,'' SLAB_CNT900
- ,'' SLAB_CNT950
- ,'' SLAB_CNT1000
- ,'' SLAB_CNT1050
- ,'' SLAB_CNT1100
- ,'' SLAB_CNT1150
- ,'' SLAB_CNT1200
- ,'' SLAB_CNT1250
- ,'' SLAB_CNT1300
- ,'' SLAB_CNT1350
- ,'' SLAB_CNT1400
- ,'' SLAB_CNT1450
- ,'' SLAB_CNT_BIG1450
- FROM dual
- UNION ALL
- SELECT '2' ORD_FL
- ,'9600' SLAB_LEN
- ,'' SLAB_CNT750
- ,'' SLAB_CNT800
- ,'' SLAB_CNT850
- ,'' SLAB_CNT900
- ,'' SLAB_CNT950
- ,'' SLAB_CNT1000
- ,'' SLAB_CNT1050
- ,'' SLAB_CNT1100
- ,'' SLAB_CNT1150
- ,'' SLAB_CNT1200
- ,'' SLAB_CNT1250
- ,'' SLAB_CNT1300
- ,'' SLAB_CNT1350
- ,'' SLAB_CNT1400
- ,'' SLAB_CNT1450
- ,'' SLAB_CNT_BIG1450
- FROM dual
- ) M
-
- GROUP BY ORD_FL
- ,SLAB_LEN)
-
- GROUP BY CUBE(ORD_FL,SLAB_LEN)
- HAVING SLAB_LEN IS NOT NULL
- )
- ORDER BY ORD_FL,SLAB_LEN
- )
-
- /*****************************UIF022020.countSlabWthLenByW************************************************/
-
- ]]>
- </query>
- <query id="UIF022020.findSlabinfo" desc="UIF022020.findSlabinfo" fetchSize="10">
- <![CDATA[
- /**************************************UIF022020.findSlabinfo****************************/
- SELECT A.SLAB_NO -- 板坯号
- ,A.SLAB_THK --板坯厚度
- ,A.SLAB_WTH --板坯宽度
- ,A.SLAB_LEN --板坯长度
- ,A.SLAB_WGT --板坯重量
- ---------------------------------------------------------------------
- --modify by sjw 090829
- --desc: 根据吴宏部长要求而加
- --VERSION:1.9-->1.20
- ,E.ORD_WTH --订单宽度
- ,E.ORD_THK --订单厚度
- ---------------------------------------------------------------------
- ,DECODE(A.ORD_FL,'1',C.HOT_THK_AIM,'') HOT_THK_AIM --热厚度AIM
- ,DECODE(A.ORD_FL,'1',C.HOT_THK_MIN,'') HOT_THK_MIN --热厚度MIN
- ,DECODE(A.ORD_FL,'1',C.HOT_THK_MAX,'') HOT_THK_MAX --热厚度MAX
- ,DECODE(A.ORD_FL,'1',C.WTH_AIM,'') WTH_AIM --宽度AlM
- ,DECODE(A.ORD_FL,'1',C.WTH_MIN,'') WTH_MIN --宽度MIN
- ,DECODE(A.ORD_FL,'1',C.WTH_MAX,'') WTH_MAX --宽度MAX
- ,DECODE(ORD_FL,'2','','1',B.ORD_LEN) MILL_LEN --轧制长度
- ,DECODE(ORD_FL,'2','','1',B.ORD_WGT) MILL_WGT --轧制重量
- ,T1.SM_CFNM CUR_PROG_CD -- 当前进程
- ,A.STL_GRD -- 钢码
- ,A.CUR_LOAD_LOC -- 堆置位置
- ,T3.SM_CFNM HCR_CLF -- 热送代号
- ,DECODE(ORD_FL,'2','','1',A.ORD_NO) ORD_NO -- 合同号
- ,DECODE(ORD_FL,'2','','1',A.ORD_SEQ) ORD_SEQ -- 订单号
- ,A.SPEC_ABBSYM -- 标准号
- ,T4.SM_CFNM ORD_USEAGE_CD -- 订单用途代码
- ,E.DEL_TO_DATE -- 交货期
- ,T5.SM_CFNM SLAB_RT -- 去向
- ,NVL(E.EMGMTR_NMG_CLF,'N') EMGMTR_NMG_CLF
- FROM TBG02_SLAB_COMM A,
- (SELECT SLAB_NO,
- COUNT(SLAB_CUT_SEQ) COIL_CUT_CNT,
- SUM(ORD_LEN) ORD_LEN, SUM(ORD_WGT) ORD_WGT
- FROM TBG02_SLAB_COMM_D GROUP BY SLAB_NO) B,
- TBB01_ORD_PROD C,
- ( SELECT SLAB_NO
- ,CASE WHEN 4500 <= T1.SLAB_LEN AND T1.SLAB_LEN <= 5300 THEN '4500~5300'
- WHEN 5300 < T1.SLAB_LEN AND T1.SLAB_LEN < 9600 THEN '5300~9600'
- WHEN 9600 <= T1.SLAB_LEN AND T1.SLAB_LEN <= 11000 THEN '9600~11000' END SLAB_LEN
- ,CASE WHEN 0 <= T1.SLAB_WTH AND T1.SLAB_WTH <= 750 THEN '≤750'
- WHEN 750 < T1.SLAB_WTH AND T1.SLAB_WTH <= 800 THEN '≤800'
- WHEN 800 < T1.SLAB_WTH AND T1.SLAB_WTH <= 850 THEN '≤850'
- WHEN 850 < T1.SLAB_WTH AND T1.SLAB_WTH <= 900 THEN '≤900'
- WHEN 900 < T1.SLAB_WTH AND T1.SLAB_WTH <= 950 THEN '≤950'
- WHEN 950 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1000 THEN '≤1000'
- WHEN 1000 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1050 THEN '≤1050'
- WHEN 1050 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1100 THEN '≤1100'
- WHEN 1100 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1150 THEN '≤1150'
- WHEN 1150 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1200 THEN '≤1200'
- WHEN 1200 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1250 THEN '≤1250'
- WHEN 1250 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1300 THEN '≤1300'
- WHEN 1300 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1350 THEN '≤1350'
- WHEN 1350 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1400 THEN '≤1400'
- WHEN 1400 < T1.SLAB_WTH AND T1.SLAB_WTH <= 1450 THEN '≤1450'
- WHEN T1.SLAB_WTH > 1450 THEN '>1450' END SLAB_WTH
- FROM TBG02_SLAB_COMM T1 ) D,
- TBE02_ORD_PRC E
- ,(SELECT T.SM_CD, T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD = 'E01002') T1
- ,(SELECT T.SM_CD, T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD = 'E01012') T3
- ,(SELECT T.SM_CD, T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD = 'A01007') T4
- ,(SELECT T.SM_CD, T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD = 'B01004') T5
- WHERE A.ORD_NO = E.ORD_NO(+)
- AND A.ORD_SEQ = E.ORD_SEQ (+)
- AND A.ORD_NO = C.ORD_NO(+)
- AND A.ORD_SEQ = C.ORD_SEQ (+)
- AND A.CUR_PROG_CD = T1.SM_CD (+)
- AND A.HCR_CLF = T3.SM_CD (+)
- AND E.ORD_USEAGE_CD = T4.SM_CD (+)
- AND A.SLAB_RT = T5.SM_CD (+)
- AND A.SLAB_NO = B.SLAB_NO(+)
- AND A.SLAB_NO = D.SLAB_NO
- AND A.SLAB_RT IN ('H','C')
- AND A.CUR_PROG_CD IN ('RRC','RBA','RBB','RAB')
- AND A.SLAB_STAT = '2'
- AND A.SLAB_LEN BETWEEN 4500 AND 11000
- AND A.CUR_LOAD_LOC LIKE 'Z%'
- AND A.STL_GRD LIKE ? || '%'
- AND A.SLAB_RT LIKE ? || '%'
- AND D.SLAB_LEN = ?
- AND D.SLAB_WTH LIKE DECODE(?,'合计','',?) || '%'
- AND A.ORD_FL LIKE DECODE(?,'余材','2','合计','','1') || '%'
- AND A.CUR_PROG_CD LIKE ? || '%'
- -- AND A.CUR_PROG_CD LIKE DECODE(?,'余材','RRC','合计','',A.CUR_PROG_CD)|| '%'
- /*******************************UIF022020.findSlabinfo***********************************/
- ]]>
- </query>
- </queryMap>
|
