Notes

SELECT AA.*,
DECODE(AA.SUM_STPG,0,0,ROUND(AA.SUM_VAN / (AA.SUM_STPG/60), 2)) AVG_GBP,
DECODE(AA.SUM_PRDTOT,0,0,ROUND(AA.SUM_VAN / (AA.SUM_PRDTOT/60), 2)) AVG_NBP,
DECODE(AA.SUM_WKTOT,0,0,ROUND(AA.SUM_VAN / (AA.SUM_WKTOT/60), 2)) AVG_GQCR,
DECODE(AA.SUM_WKNET,0,0,ROUND(AA.SUM_VAN / (AA.SUM_WKNET/60), 2)) AVG_NQCR,
(SELECT CDV_VSL_LENGTH FROM CDV_VESSEL WHERE CDV_VSL_CODE = VSL
) LOA
FROM
(SELECT A.VSB_VOY_OUTSERVICE AS LANE,
A.VSB_VOY_VESSEL AS VSL,
A.VSB_VOY_IVOYAGEIN AS VOY,
Z.TD_F_20,
Z.TD_F_40,
Z.TD_M_20,
Z.TD_M_40,
Z.TL_F_20,
Z.TL_F_40,
Z.TL_M_20,
Z.TL_M_40,
Z.II_F_20,
Z.II_F_40,
Z.II_M_20,
Z.II_M_40,
Z.XX_F_20,
Z.XX_F_40,
Z.XX_M_20,
Z.XX_M_40,
Z.S2_F_20,
Z.S2_F_40,
Z.S2_M_20,
Z.S2_M_40,
Z.S1_F_20,
Z.S1_F_40,
Z.S1_M_20,
Z.S1_M_40,
B.VSS_STT_VAN AS TVAN,
B.VSS_STT_TEU AS TTEU,
ROUND(B.VSS_STT_GROSSTIME /60, 2) AS WKTOT,
ROUND(B.VSS_STT_NETTIME /60, 2) AS WKNET,
ROUND(B.VSS_STT_ABERTHTIME/60, 2) AS PRDTOT,
ROUND(B.VSS_STT_COMMENTIME/60, 2) AS PRDNET,
X.CC AS STPG,
B.VSS_STT_GQCR AS GQCR,
B.VSS_STT_NQCR AS NQCR,
B.VSS_STT_GBP AS GBP,
B.VSS_STT_NBP AS NBP,
B.VSS_STT_WORKQC AS GC,
Z.GG AS FRT,
Z.HH AS ERT,
TO_CHAR (TO_DATE (DECODE (A.VSB_VOY_ACTBER_DATE, NULL, A.VSB_VOY_ACTARR_DATE
|| A.VSB_VOY_ACTARR_TIME, A.VSB_VOY_ACTBER_DATE
|| A.VSB_VOY_ACTBER_TIME ), 'YYYYMMDDHH24MISS' ), 'YYYY-MM-DD HH24:MI' ) AS DT,
MOVES,
SING_MOVES,
TWIN_MOVES,
TOT_MOVES,
SUM(B.VSS_STT_VAN) OVER(PARTITION BY '') SUM_VAN,
SUM(B.VSS_STT_GROSSTIME) OVER(PARTITION BY '') SUM_WKTOT,
SUM(B.VSS_STT_NETTIME) OVER(PARTITION BY '') SUM_WKNET,
SUM(B.VSS_STT_ABERTHTIME) OVER(PARTITION BY '') SUM_STPG,
SUM(B.VSS_STT_COMMENTIME) OVER(PARTITION BY '') SUM_PRDTOT
FROM VSB_VOYAGE A,
VSS_STATISTICS B,
(SELECT A.VSB_VOY_VESSEL AA,
A.VSB_VOY_VOYAGE BB,
SUM(DECODE(B.VSP_SHP_CLASS, 'TL', DECODE (B.VSP_SHP_DISLOAD, 'D', DECODE (B.VSP_SHP_STATUS, 'M', 0, DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 1, 0 ) ), 0) , 0)) TD_F_20,
SUM(DECODE(B.VSP_SHP_CLASS, 'TL', DECODE (B.VSP_SHP_DISLOAD, 'D', DECODE (B.VSP_SHP_STATUS, 'M', 0, DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 0, 1 ) ), 0) , 0)) TD_F_40,
SUM(DECODE(B.VSP_SHP_CLASS, 'TL', DECODE (B.VSP_SHP_DISLOAD, 'D', DECODE (B.VSP_SHP_STATUS, 'M', DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 1, 0 ), 0 ), 0) , 0)) TD_M_20,
SUM(DECODE(B.VSP_SHP_CLASS, 'TL', DECODE (B.VSP_SHP_DISLOAD, 'D', DECODE (B.VSP_SHP_STATUS, 'M', DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 0, 1 ), 0 ), 0) , 0)) TD_M_40,
SUM(DECODE(B.VSP_SHP_CLASS, 'TL', DECODE (B.VSP_SHP_DISLOAD, 'L', DECODE (B.VSP_SHP_STATUS, 'M', 0, DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 1, 0 )), 0), 0)) TL_F_20,
SUM(DECODE(B.VSP_SHP_CLASS, 'TL', DECODE (B.VSP_SHP_DISLOAD, 'L', DECODE (B.VSP_SHP_STATUS, 'M', 0, DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 0, 1 )), 0), 0)) TL_F_40,
SUM(DECODE(B.VSP_SHP_CLASS, 'TL', DECODE (B.VSP_SHP_DISLOAD, 'L', DECODE (B.VSP_SHP_STATUS, 'M', DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 1, 0 ), 0 ), 0), 0)) TL_M_20,
SUM(DECODE(B.VSP_SHP_CLASS, 'TL', DECODE (B.VSP_SHP_DISLOAD, 'L', DECODE (B.VSP_SHP_STATUS, 'M', DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 0, 1 ), 0 ), 0), 0)) TL_M_40,
SUM(DECODE(B.VSP_SHP_CLASS, 'II', DECODE (B.VSP_SHP_STATUS, 'M', 0, DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 1, 0 ) ), 0)) II_F_20,
SUM(DECODE(B.VSP_SHP_CLASS, 'II', DECODE (B.VSP_SHP_STATUS, 'M', 0, DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 0, 1 ) ), 0)) II_F_40,
SUM(DECODE(B.VSP_SHP_CLASS, 'II', DECODE (B.VSP_SHP_STATUS, 'M', DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 1, 0 ), 0 ), 0)) II_M_20,
SUM(DECODE(B.VSP_SHP_CLASS, 'II', DECODE (B.VSP_SHP_STATUS, 'M', DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 0, 1 ), 0 ), 0)) II_M_40,
SUM(DECODE(B.VSP_SHP_CLASS, 'XX', DECODE (B.VSP_SHP_STATUS, 'M', 0, DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 1, 0 ) ), 0)) XX_F_20,
SUM(DECODE(B.VSP_SHP_CLASS, 'XX', DECODE (B.VSP_SHP_STATUS, 'M', 0, DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 0, 1 ) ), 0)) XX_F_40,
SUM(DECODE(B.VSP_SHP_CLASS, 'XX', DECODE (B.VSP_SHP_STATUS, 'M', DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 1, 0 ), 0 ), 0)) XX_M_20,
SUM(DECODE(B.VSP_SHP_CLASS, 'XX', DECODE (B.VSP_SHP_STATUS, 'M', DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 0, 1 ), 0 ), 0)) XX_M_40,
SUM(DECODE(B.VSP_SHP_CLASS, 'S2', DECODE (B.VSP_SHP_STATUS, 'M', 0, DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 1, 0 ) ), 0)) S2_F_20,
SUM(DECODE(B.VSP_SHP_CLASS, 'S2', DECODE (B.VSP_SHP_STATUS, 'M', 0, DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 0, 1 ) ), 0)) S2_F_40,
SUM(DECODE(B.VSP_SHP_CLASS, 'S2', DECODE (B.VSP_SHP_STATUS, 'M', DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 1, 0 ), 0 ), 0)) S2_M_20,
SUM(DECODE(B.VSP_SHP_CLASS, 'S2', DECODE (B.VSP_SHP_STATUS, 'M', DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 0, 1 ), 0 ), 0)) S2_M_40,
SUM(DECODE(B.VSP_SHP_CLASS, 'S1', DECODE (B.VSP_SHP_STATUS, 'M', 0, DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 1, 0 ) ), 0)) S1_F_20,
SUM(DECODE(B.VSP_SHP_CLASS, 'S1', DECODE (B.VSP_SHP_STATUS, 'M', 0, DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 0, 1 ) ), 0)) S1_F_40,
SUM(DECODE(B.VSP_SHP_CLASS, 'S1', DECODE (B.VSP_SHP_STATUS, 'M', DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 1, 0 ), 0 ), 0)) S1_M_20,
SUM(DECODE(B.VSP_SHP_CLASS, 'S1', DECODE (B.VSP_SHP_STATUS, 'M', DECODE (SUBSTR (B.VSP_SHP_ISO, 1, 1), '2', 0, 1 ), 0 ), 0)) S1_M_40,
TO_CHAR (DECODE (COUNT (B.VSP_SHP_CONTNO), 0, 0, ROUND ( ( SUM (DECODE (B.VSP_SHP_STATUS, 'M', 0, 1 ) ) / COUNT (B.VSP_SHP_CONTNO) ) * 100, 2 ) ), '990.00' ) GG,
TO_CHAR (DECODE (COUNT (B.VSP_SHP_CONTNO), 0, 0, ROUND ( ( SUM (DECODE (B.VSP_SHP_STATUS, 'M', 1, 0 ) ) / COUNT (B.VSP_SHP_CONTNO) ) * 100, 2 ) ), '990.00' ) HH,
ROUND(SUM(DECODE(VSP_SHP_TWINTANDEM, 'W', 0.5, 'M', 0.25, 1))) MOVES,
ROUND(SUM(DECODE(VSP_SHP_TWINTANDEM, NULL, 1, 0))) SING_MOVES,
ROUND(SUM(DECODE(VSP_SHP_TWINTANDEM, 'W', 0.5, 'M', 0, 0))) TWIN_MOVES,
ROUND(SUM(DECODE(VSP_SHP_TWINTANDEM, 'W', 0.5, NULL, 1, 0))) TOT_MOVES
FROM VSB_VOYAGE A,

VSS_VSP_SHIP B
WHERE A.VSB_VOY_STATUS = 'D'
AND A.VSB_VOY_VESSEL = B.VSP_SHP_VESSEL(+)
AND A.VSB_VOY_VOYAGE = B.VSP_SHP_VOYAGE(+)
AND B.VSP_SHP_CLASS <> 'OT'
AND B.VSP_SHP_PLANST = 'C'
GROUP BY A.VSB_VOY_VESSEL,
A.VSB_VOY_VOYAGE
) Z,
(SELECT A.VSB_VOY_VESSEL AA,
A.VSB_VOY_VOYAGE BB,
SUM (DECODE (B.VSS_STGC_OUTAGE, NULL, NULL, B.VSS_STGC_OUTAGE ) ) CC
FROM VSB_VOYAGE A,
VSS_STATISTICS_GC B
WHERE A.VSB_VOY_STATUS = 'D'
AND B.VSS_STGC_VESSEL = A.VSB_VOY_VESSEL(+)
AND B.VSS_STGC_VOYAGE = A.VSB_VOY_VOYAGE(+)
GROUP BY A.VSB_VOY_VESSEL,
A.VSB_VOY_VOYAGE
) X
WHERE A.VSB_VOY_STATUS = 'D'
AND A.VSB_VOY_VESSEL = B.VSS_STT_VESSEL(+)
AND A.VSB_VOY_VOYAGE = B.VSS_STT_VOYAGE(+)
AND A.VSB_VOY_VESSEL = Z.AA(+)
AND A.VSB_VOY_VOYAGE = Z.BB(+)
AND A.VSB_VOY_VESSEL = X.AA(+)
AND A.VSB_VOY_VOYAGE = X.BB(+)
ORDER BY TO_CHAR(TO_DATE(DECODE(A.VSB_VOY_ACTBER_DATE, NULL, A.VSB_VOY_ACTARR_DATE
|| A.VSB_VOY_ACTARR_TIME, A.VSB_VOY_ACTBER_DATE
|| A.VSB_VOY_ACTBER_TIME), 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI')
) AA;