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************************************************/
]]>
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************************************************/
]]>
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***********************************/
]]>