SQL SHOW系列（一）
SELECT ROWNUM, PP.* FROM (SELECT TT.*FROM (SELECT T.ASSESSCODE,
T.ASSESSNAME,
T.PLANCOMPDATE,
DECODE(T.ASSESSCODE,
'GCJSWZSQ',
WZCODE.ENDDATE,
'GCZTB',
DECODE(LEAST(NVL(BZTBCODE.ENDDATE,
TO_DATE('9999-12-31',
'YYYY-MM-DD')),
NVL(XZTBCODE.ENDDATE,
TO_DATE('9999-12-31',
'YYYY-MM-DD')),
NVL(LSZTBCODE.ENDDATE,
TO_DATE('9999-12-31',
'YYYY-MM-DD'))),
TO_DATE('9999-12-31', 'YYYY-MM-DD'),
NULL,
LEAST(NVL(BZTBCODE.ENDDATE,
TO_DATE('9999-12-31',
'YYYY-MM-DD')),
NVL(XZTBCODE.ENDDATE,
TO_DATE('9999-12-31', 'YYYY-MM-DD')),
NVL(LSZTBCODE.ENDDATE,
TO_DATE('9999-12-31', 'YYYY-MM-DD')))),
T.FACTCOMPDATE) FACTCOMPDATE,
T.NEEDCOMPDATE,
T.PHASENAME,
T.ISEDITPLANDATE,
T.SERIALNUM,
T.ISEMPTY
FROM BO_TASK_ITEM1 T,
BO_ENG_PLAN M,
BO_ENGINEERING V,
(SELECT TRUNC(MIN(A.ENDDATE), 'DD') ENDDATE,
'GCJSWZSQ' CODE
FROM BPM_WORKLIST A
WHERE A.INSTID IN
(SELECT V.INSTID
FROM BPM_PROCVAR V
WHERE V.KEYNAME = 'APPID'
AND V.VALUESTRING IN
(SELECT TT.BO_MATERIALAPLLY_ID
FROM BO_ENG_MATERIALAPLLY TT
WHERE TT.BO_ENGINEERING_ID =
'7cb1cce7-48d5-4e37-aae7-e07dcaa8107c'))) WZCODE,
(SELECT TRUNC(MIN(A.ENDDATE), 'DD') ENDDATE,
'GCZTB' CODE
FROM BPM_WORKLIST A
WHERE A.INSTID IN
(SELECT V.INSTID
FROM BPM_PROCVAR V
WHERE V.KEYNAME = 'APPID'
AND V.VALUESTRING IN
(SELECT TT.BO_SUPPLYCONFIRM_ID
FROM BO_SUPPLYCONFIRM TT
WHERE TT.BO_ENGINEERING_ID =
'7cb1cce7-48d5-4e37-aae7-e07dcaa8107c'))) BZTBCODE,
(SELECT TRUNC(MIN(A.ENDDATE), 'DD') ENDDATE,
'GCZTB' CODE
FROM BPM_WORKLIST A
WHERE A.INSTID IN
(SELECT V.INSTID
FROM BPM_PROCVAR V
WHERE V.KEYNAME = 'APPID'
AND V.VALUESTRING IN
(SELECT TT.BO_BIDAPPLY_ID
FROM BO_BIDAPPLY TT
WHERE TT.BO_ENGINEERING_ID =
'7cb1cce7-48d5-4e37-aae7-e07dcaa8107c'))) XZTBCODE,
(SELECT TRUNC(MIN(A.ENDDATE), 'DD') ENDDATE,
'GCZTB' CODE
FROM BPM_WORKLIST A
WHERE A.INSTID IN
(SELECT V.INSTID
FROM BPM_PROCVAR V
WHERE V.KEYNAME = 'APPID'
AND V.VALUESTRING IN
(SELECT TT.BO_TEMPORARYAPPLY_ID
FROM BO_TEMPORARYAPPLY TT
WHERE TT.BO_ENGINEERING_ID =
'7cb1cce7-48d5-4e37-aae7-e07dcaa8107c'))) LSZTBCODE
WHERE T.ASSESSCODE != 'ZZJHJD'
AND M.BO_ENGINEERING_ID =
'7cb1cce7-48d5-4e37-aae7-e07dcaa8107c'
AND T.BO_ENG_PLAN_ID = M.BO_ENG_PLAN_ID
AND M.BO_ENGINEERING_ID = V.BO_ENGINEERING_ID
AND T.ASSESSCODE = WZCODE.CODE(+)
AND T.ASSESSCODE = BZTBCODE.CODE(+)
AND T.ASSESSCODE = XZTBCODE.CODE(+)
AND T.ASSESSCODE = LSZTBCODE.CODE(+)
AND (T.ISEDITPLANDATE = 1 OR
(T.ASSESSCODE = 'JSJHRWS' OR T.ASSESSCODE = 'MQXMFZR'))
UNION
SELECT 'SJPF' ASSESSCODE,
'设计批复' ASSESSNAME,
NULL PLANCOMPDATE,
(SELECT DV.CREATEDATE
FROM BO_DESIGNAPPROVE_VER DV, V_BO_ENGINEERING VE
WHERE DV.BO_PROJECT_ID = VE.BO_PROJECT_ID(+)
AND VE.BO_ENGINEERING_ID =
'7cb1cce7-48d5-4e37-aae7-e07dcaa8107c'
AND DV.VERIONNUMBER = '1.0') FACTCOMPDATE,
(SELECT T1.NEEDCOMPDATE
FROM BO_TASK_ITEM1 T1,
BO_ENG_PLAN M1,
V_BO_ENGINEERING V1
WHERE M1.BO_ENGINEERING_ID =
'7cb1cce7-48d5-4e37-aae7-e07dcaa8107c'
AND T1.BO_ENG_PLAN_ID = M1.BO_ENG_PLAN_ID
AND M1.BO_ENGINEERING_ID = V1.BO_ENGINEERING_ID
AND (T1.ASSESSCODE = 'SJPF')) NEEDCOMPDATE,
'工程前期' PHASENAME,
'2' ISEDITPLANDATE,
12 SERIALNUM,
'' ISEMPTY
FROM DUAL) TT
ORDER BY TT.SERIALNUM) PP
页:
[1]