|
WITH temp001 AS (
SELECT
main.PL_PROJECT_MAIN_ID,
info.PL_PLAN_INFO_ID,
info.TASK_NAME,
info.ORDER_NO,
detail.BEGIN_TIME,
detail.OVER_TIME
FROM
PL_PROJECT_MAIN main
LEFT JOIN PL_PLAN_INFO info ON main.PL_PROJECT_MAIN_ID = info.PL_PROJECT_MAIN_ID
AND info.PARENT_ID = '0'
LEFT JOIN PL_PLAN_DETAIL detail ON info.PL_PLAN_INFO_ID = detail.PL_PLAN_INFO_ID
WHERE
main.PROJECT_PHASE NOT IN ( '1', '2', '3' )
AND info.ORDER_NO = '1'
),
temp002 AS (
SELECT
main.PL_PROJECT_MAIN_ID,
info.PL_PLAN_INFO_ID,
info.TASK_NAME,
info.ORDER_NO,
detail.BEGIN_TIME,
detail.OVER_TIME
FROM
PL_PROJECT_MAIN main
LEFT JOIN PL_PLAN_INFO info ON main.PL_PROJECT_MAIN_ID = info.PL_PROJECT_MAIN_ID
AND info.PARENT_ID = '0'
LEFT JOIN PL_PLAN_DETAIL detail ON info.PL_PLAN_INFO_ID = detail.PL_PLAN_INFO_ID
WHERE
main.PROJECT_PHASE NOT IN ( '1', '2', '3' )
AND info.ORDER_NO = '2'
),
temp003 AS (
SELECT
main.PL_PROJECT_MAIN_ID,
info.PL_PLAN_INFO_ID,
info.TASK_NAME,
info.ORDER_NO,
detail.BEGIN_TIME,
detail.OVER_TIME
FROM
PL_PROJECT_MAIN main
LEFT JOIN PL_PLAN_INFO info ON main.PL_PROJECT_MAIN_ID = info.PL_PROJECT_MAIN_ID
AND info.PARENT_ID = '0'
LEFT JOIN PL_PLAN_DETAIL detail ON info.PL_PLAN_INFO_ID = detail.PL_PLAN_INFO_ID
WHERE
main.PROJECT_PHASE NOT IN ( '1', '2', '3' )
AND info.ORDER_NO = '3'
),
temp004 AS (
SELECT
main.PL_PROJECT_MAIN_ID,
info.PL_PLAN_INFO_ID,
info.TASK_NAME,
info.ORDER_NO,
detail.BEGIN_TIME,
detail.OVER_TIME
FROM
PL_PROJECT_MAIN main
LEFT JOIN PL_PLAN_INFO info ON main.PL_PROJECT_MAIN_ID = info.PL_PROJECT_MAIN_ID
AND info.PARENT_ID = '0'
LEFT JOIN PL_PLAN_DETAIL detail ON info.PL_PLAN_INFO_ID = detail.PL_PLAN_INFO_ID
WHERE
main.PROJECT_PHASE NOT IN ( '1', '2', '3' )
AND info.ORDER_NO = '4'
),
temp005 AS (
SELECT
main.PL_PROJECT_MAIN_ID,
info.PL_PLAN_INFO_ID,
info.TASK_NAME,
info.ORDER_NO,
detail.BEGIN_TIME,
detail.OVER_TIME
FROM
PL_PROJECT_MAIN main
LEFT JOIN PL_PLAN_INFO info ON main.PL_PROJECT_MAIN_ID = info.PL_PROJECT_MAIN_ID
AND info.PARENT_ID = '0'
LEFT JOIN PL_PLAN_DETAIL detail ON info.PL_PLAN_INFO_ID = detail.PL_PLAN_INFO_ID
WHERE
main.PROJECT_PHASE NOT IN ( '1', '2', '3' )
AND info.ORDER_NO = '5'
)
SELECT DISTINCT
(
CASE
WHEN to_char(BEGIN_TIME1,'yyyyMMdd') <= to_char(sysdate,'yyyyMMdd') AND to_char(OVER_TIME1,'yyyyMMdd') >= to_char(sysdate,'yyyyMMdd') THEN TASK_NAME1
WHEN to_char(BEGIN_TIME2,'yyyyMMdd') <= to_char(sysdate,'yyyyMMdd') AND to_char(OVER_TIME2,'yyyyMMdd') >= to_char(sysdate,'yyyyMMdd') THEN TASK_NAME2
WHEN to_char(BEGIN_TIME3,'yyyyMMdd') <= to_char(sysdate,'yyyyMMdd') AND to_char(OVER_TIME3,'yyyyMMdd') >= to_char(sysdate,'yyyyMMdd') THEN TASK_NAME3
WHEN to_char(BEGIN_TIME4,'yyyyMMdd') <= to_char(sysdate,'yyyyMMdd') AND to_char(OVER_TIME4,'yyyyMMdd') >= to_char(sysdate,'yyyyMMdd') THEN TASK_NAME4
WHEN to_char(BEGIN_TIME5,'yyyyMMdd') <= to_char(sysdate,'yyyyMMdd') AND to_char(OVER_TIME5,'yyyyMMdd') >= to_char(sysdate,'yyyyMMdd') THEN TASK_NAME5
END
) AS taskName,
a.PL_PROJECT_MAIN_ID,
a.PL_PROJECT_NAME,
a.PL_PROJECT_NO,
(
CASE
WHEN a.PL_PROJECT_ATTRIBUTE = '1' THEN
b.BUSI_INFO_ID
WHEN a.PL_PROJECT_ATTRIBUTE = '2' THEN
c.BUSI_INFO_ID
WHEN a.PL_PROJECT_ATTRIBUTE = '7' THEN
d.BUSI_INFO_ID
WHEN a.PL_PROJECT_ATTRIBUTE = '6' THEN
e.BUSI_INFO_ID
WHEN a.PL_PROJECT_ATTRIBUTE = '3' THEN
f.BUSI_INFO_ID
WHEN a.PL_PROJECT_ATTRIBUTE = '4' THEN
g.BUSI_INFO_ID
WHEN a.PL_PROJECT_ATTRIBUTE = '8' THEN
h.BUSI_INFO_ID
WHEN a.PL_PROJECT_ATTRIBUTE = '5' THEN
i.BUSI_INFO_ID
END
) AS busiInfoId,
(
CASE
WHEN ( a.PROJECT_PHASE NOT IN ( '1', '2', '3', '8' ) AND a.CHANGE_STATUS NOT IN ( '5', '6' ) ) THEN
'0'
WHEN ( a.PROJECT_PHASE != '8' AND a.CHANGE_STATUS = '5' ) THEN
'1'
WHEN ( a.PROJECT_PHASE != '8' AND a.CHANGE_STATUS = '6' ) THEN
'2'
WHEN PROJECT_PHASE = '8' THEN
'3'
END
) AS plProjectStatus,
j.PRO_MEMBER_ORG_ID AS sysOrgId,
j.PRO_MEMBER_ORG_NAME AS sysOrgName,
j.PRO_MEMBER_NAME,
j.PRO_MEMBER_ID,
k.CREATION_DATE,
a.PL_PROJECT_REAL_OVER_TIME AS proOverTime,
NVL(
n.CALCULATE_TYPE,
( CASE WHEN j.PRO_MEMBER_ORG_NAME = '测控中心' THEN 'B' WHEN j.PRO_MEMBER_ORG_NAME = '保障设备中心' THEN 'B' ELSE 'A' END )) AS calculateType,
n.DELIVERY_LIMIT,
n.CONTRACT_END,
n.BUSI_CONTRACT_OUT_INFO_ID,
n.ADJUST_SUM
FROM
PL_PROJECT_MAIN a
LEFT JOIN PL_PRO_INFO_TECH b ON a.PL_PROJECT_MAIN_ID = b.PL_PROJECT_MAIN_ID
LEFT JOIN PL_PRO_INFO_REPAIR c ON a.PL_PROJECT_MAIN_ID = c.PL_PROJECT_MAIN_ID
LEFT JOIN PL_PRO_INFO_PLANEM d ON a.PL_PROJECT_MAIN_ID = d.PL_PROJECT_MAIN_ID
LEFT JOIN PL_PRO_INFO_MEASURE e ON a.PL_PROJECT_MAIN_ID = e.PL_PROJECT_MAIN_ID
LEFT JOIN PL_PRO_INFO_GOODS f ON a.PL_PROJECT_MAIN_ID = f.PL_PROJECT_MAIN_ID
LEFT JOIN PL_PRO_INFO_APP g ON a.PL_PROJECT_MAIN_ID = g.PL_PROJECT_MAIN_ID
LEFT JOIN PL_PRO_INFO_AIRREPAIR h ON a.PL_PROJECT_MAIN_ID = h.PL_PROJECT_MAIN_ID
LEFT JOIN PL_PRO_INFO_AIRBORNE i ON a.PL_PROJECT_MAIN_ID = i.PL_PROJECT_MAIN_ID
LEFT JOIN PL_PRO_MEMBER j ON j.PL_PROJECT_MAIN_ID = a.PL_PROJECT_MAIN_ID
AND j.PRO_ROLE = 0
LEFT JOIN WF_FLOW_CLIENT_RUN k ON j.PL_PROJECT_MAIN_ID = k.BUSINESS_KEY_
LEFT JOIN PL_PLAN_INFO l ON l.PARENT_ID = '0'
AND l.PL_PROJECT_MAIN_ID = a.PL_PROJECT_MAIN_ID
LEFT JOIN PL_PLAN_DETAIL m ON l.PL_PLAN_INFO_ID = m.PL_PLAN_INFO_ID
LEFT JOIN PL_PRO_PAY_INFO n ON n.PL_PROJECT_MAIN_ID = a.PL_PROJECT_MAIN_ID
AND n.PAY_STATUS = 1
LEFT JOIN (
SELECT
temp001.PL_PROJECT_MAIN_ID,
temp001.TASK_NAME TASK_NAME1,temp001.ORDER_NO ORDER_NO1,temp001.BEGIN_TIME BEGIN_TIME1,temp001.OVER_TIME OVER_TIME1,
temp002.TASK_NAME TASK_NAME2,temp002.ORDER_NO ORDER_NO2,temp002.BEGIN_TIME BEGIN_TIME2,temp002.OVER_TIME OVER_TIME2,
temp003.TASK_NAME TASK_NAME3,temp003.ORDER_NO ORDER_NO3,temp003.BEGIN_TIME BEGIN_TIME3,temp003.OVER_TIME OVER_TIME3,
temp004.TASK_NAME TASK_NAME4,temp004.ORDER_NO ORDER_NO4,temp004.BEGIN_TIME BEGIN_TIME4,temp004.OVER_TIME OVER_TIME4,
temp005.TASK_NAME TASK_NAME5,temp005.ORDER_NO ORDER_NO5,temp005.BEGIN_TIME BEGIN_TIME5,temp005.OVER_TIME OVER_TIME5
FROM
temp001
LEFT JOIN temp002 ON temp001.PL_PROJECT_MAIN_ID = temp002.PL_PROJECT_MAIN_ID
LEFT JOIN temp003 ON temp001.PL_PROJECT_MAIN_ID = temp003.PL_PROJECT_MAIN_ID
LEFT JOIN temp004 ON temp001.PL_PROJECT_MAIN_ID = temp004.PL_PROJECT_MAIN_ID
LEFT JOIN temp005 ON temp001.PL_PROJECT_MAIN_ID = temp005.PL_PROJECT_MAIN_ID
) temp ON a.PL_PROJECT_MAIN_ID = temp.PL_PROJECT_MAIN_ID
WHERE
a.PROJECT_PHASE NOT IN ( '1', '2', '3' )
ORDER BY
nlssort( a.PL_PROJECT_NAME, 'NLS_SORT = SCHINESE_PINYIN_M' ),
a.PL_PROJECT_NO
|
