/* Formatted on 2018/09/10 14:15 (Formatter Plus v4.8.5) */
SELECT activity_made,
(CASE
WHEN activity_made = '0000/00/00 00:00:00'
THEN NULL
ELSE activity_made
END
) AS actmode
FROM (SELECT (CASE
WHEN (SELECT COUNT (*)
FROM sfa_activity sa
WHERE sa.companyid = opp.companyid
AND sa.opptyid = opp.opptyid
AND sa.TYPE = 'NOTE') > 0
THEN (SELECT NVL
(MAX
(TO_CHAR
(CASE
WHEN TRUNC (sa.updat) =
TRUNC (SYSDATE)
THEN TO_CHAR
(sa.updat,
'hh12:mi PM'
)
WHEN TRUNC (sa.updat)
BETWEEN TRUNC
(SYSDATE,
'yy'
)
AND TRUNC
( SYSDATE
- 1
)
THEN TO_CHAR (sa.updat,
'Mon dd'
)
ELSE TO_CHAR (sa.updat,
'DD/MM/YYYY'
)
END
)
),
'0000/00/00 00:00:00'
)
FROM sfa_activity sa
WHERE sa.companyid = opp.companyid
AND sa.opptyid = opp.opptyid
AND sa.TYPE = 'NOTE')
ELSE (SELECT NVL
(MAX
(TO_CHAR
(CASE
WHEN TRUNC (sa.updat) =
TRUNC (SYSDATE)
THEN TO_CHAR (sa.updat,
'hh12:mi PM'
)
WHEN TRUNC (sa.updat)
BETWEEN TRUNC (SYSDATE,
'yy'
)
AND TRUNC ( SYSDATE
- 1
)
THEN TO_CHAR (sa.updat,
'Mon dd'
)
ELSE TO_CHAR (sa.updat,
'DD/MM/YYYY'
)
END
)
),
'0000/00/00 00:00:00'
)
/*ELSE (SELECT (MAX(TO_CHAR(sa.updat,'YYYY/MM/DD HH24:MI:SS')))*/
FROM sfa_activity sa
WHERE sa.companyid = opp.companyid
AND sa.opptyid = opp.opptyid
AND sa.TYPE <> 'NOTE')
END
) AS activity_made
FROM sfa_opportunities opp
WHERE companyid = 1192)