SELECT LOAD_PLAN_NAME,
I_LP_INST,
NB_RUN,
START_DATE,
END_DATE,
STATUS,
LAST_SUCCESSFUL_RUN,
THE_CHOICE,
THE_RANK
FROM ( SELECT LOAD_PLAN_NAME,
I_LP_INST,
NB_RUN,
START_DATE,
END_DATE,
STATUS,
LSR AS "LAST_SUCCESSFUL_RUN",
THE_CHOICE,
RANK () OVER (ORDER BY THE_CHOICE DESC) AS "THE_RANK"
FROM (SELECT /*+ USE_NL(A,B,C,D) */
NVL (B.LOAD_PLAN_NAME, 'NO DATA AVAILABLE')
AS "LOAD_PLAN_NAME",
NVL (C.I_LP_INST, 0) AS "I_LP_INST",
NVL (C.NB_RUN, 0) AS "NB_RUN",
NVL (C.START_DATE,
TO_DATE ('19900101', 'YYYYMMDD'))
AS "START_DATE",
NVL (C.END_DATE, TO_DATE ('19900101', 'YYYYMMDD'))
AS "END_DATE",
NVL (C.STATUS, 'D') AS "STATUS",
NVL (LSR, TO_DATE ('19900101', 'YYYYMMDD'))
AS "LSR",
C.NB_RUN AS "THE_CHOICE"
FROM (SELECT MAX (I_LP_INST) AS "I_LP_INST"
FROM SNP_LP_INST
WHERE LOAD_PLAN_NAME = 'Enter Load Plan Name Here') A,
SNP_LP_INST B,
SNP_LPI_RUN C,
(SELECT MAX (C.END_DATE) AS LSR
FROM SNP_LP_INST B, SNP_LPI_RUN C
WHERE 1 = 1
AND B.I_LP_INST = C.I_LP_INST
AND B.LOAD_PLAN_NAME = 'Enter Load Plan Name Here'
AND C.STATUS = 'D') D
WHERE 1 = 1
AND A.I_LP_INST = B.I_LP_INST
AND B.I_LP_INST = C.I_LP_INST
AND B.LOAD_PLAN_NAME = 'Enter Load Plan Name Here'
UNION ALL
SELECT 'NO DATA AVAILABLE' AS "LOAD_PLAN_NAME",
0 AS "I_LP_INST",
0 AS "NB_RUN",
TO_DATE ('19900101', 'YYYYMMDD') AS "START_DATE",
TO_DATE ('19900101', 'YYYYMMDD') AS "END_DATE",
'D' AS "STATUS",
TO_DATE ('19900101', 'YYYYMMDD') AS "LSR",
-1 AS "THE_CHOICE"
FROM DUAL)
ORDER BY 9)
WHERE THE_RANK = 1;
